on_error table, saving error info to a table - Mailing list pgsql-hackers

From jian he
Subject on_error table, saving error info to a table
Date
Msg-id CACJufxH_OJpVra=0c4ow8fbxHj7heMcVaTNEPa5vAurSeNA-6Q@mail.gmail.com
Whole thread Raw
Responses Re: on_error table, saving error info to a table
List 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

pgsql-hackers by date:

Previous
From: jian he
Date:
Subject: Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
Next
From: Amit Kapila
Date:
Subject: Re: src/bin/pg_upgrade/t/004_subscription.pl test comment fix