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