From: | jian he <jian(dot)universality(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | on_error table, saving error info to a table |
Date: | 2024-02-03 06:22:34 |
Message-ID: | CACJufxH_OJpVra=0c4ow8fbxHj7heMcVaTNEPa5vAurSeNA-6Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi.
I previously did some work in COPY FROM save error information to a table.
still based on this suggestion:
https://www.postgresql.org/message-id/752672.1699474336%40sss.pgh.pa.us
Now I refactored it.
the syntax:
ON_ERROR 'table', TABLE 'error_saving_tbl'
if ON_ERROR is not specified with 'table', TABLE is specified, then error.
if ON_ERROR is specified with 'table', TABLE is not specified or
error_saving_tbl does not exist, then error.
In BeginCopyFrom, we check the data definition of error_saving_table,
we also check if the user has INSERT privilege to error_saving_table
(all the columns).
We also did a preliminary check of the lock condition of error_saving_table.
if it does not meet these conditions, we quickly error out.
error_saving_table will be the same schema as the copy from table.
Because "table" is a keyword, I have to add the following changes to gram.y.
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3420,6 +3420,10 @@ copy_opt_item:
{
$$ = makeDefElem("null", (Node *) makeString($3), @1);
}
+ | TABLE opt_as Sconst
+ {
+ $$ = makeDefElem("table", (Node *) makeString($3), @1);
+ }
since "table" is already a keyword, so there is no influence on the
parsing speed?
demo:
create table err_tbl(
userid oid, -- the user oid while copy generated this entry
copy_tbl oid, --copy table
filename text,
lineno int8,
line text,
colname text,
raw_field_value text,
err_message text,
err_detail text,
errorcode text
);
create table t_copy_tbl(a int, b int, c int);
COPY t_copy_tbl FROM STDIN WITH (delimiter ',', on_error 'table',
table err_tbl);
1,2,a
\.
table err_tbl \gx
-[ RECORD 1 ]---+-------------------------------------------
userid | 10
copy_tbl | 17920
filename | STDIN
lineno | 1
line | 1,2,a
colname | c
raw_field_value | a
err_message | invalid input syntax for type integer: "a"
err_detail |
errorcode | 22P02
Attachment | Content-Type | Size |
---|---|---|
v1-0001-on_error-table-saving-error-info-to-a-table.patch | application/x-patch | 30.2 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Kapila | 2024-02-03 06:28:24 | Re: src/bin/pg_upgrade/t/004_subscription.pl test comment fix |
Previous Message | jian he | 2024-02-03 06:22:08 | Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row |