on_error table, saving error info to a table

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

Responses

Browse pgsql-hackers by date

  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