Re: on_error table, saving error info to a table
| От | Nishant Sharma |
|---|---|
| Тема | Re: on_error table, saving error info to a table |
| Дата | |
| Msg-id | CADrsxdYG++K=iKjRm35u03q-Nb0tQPJaqjxnA2mGt5O=Dht7sw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: on_error table, saving error info to a table (jian he <jian.universality@gmail.com>) |
| Список | pgsql-hackers |
On Tue, Dec 17, 2024 at 12:31 PM Kirill Reshke <reshkekirill@gmail.com> wrote:
>
> On Mon, 16 Dec 2024 at 16:50, Nishant Sharma
> <nishant.sharma@enterprisedb.com> wrote:
> > Also, I think Andrew's suggestion can resolve the concern me and Krill
> > had on forcing users to create tables with correct column names and
> > numbers. Also, will make error table checking simpler. No need for the
> > above kind of checks.
>
> +1 on that.
>
Syntax: COPY (on_error table, table error_saving_tbl);
seems not ideal.
but auto-create on_error table if this table does not exist, seems way
more harder.
Since we can not use SPI interface here, maybe we can use DefineRelation
also, to auto-create a table, what if table already exists, then
our operation would be stuck for not COPY related reason.
also auto-create means we need to come up with a magic table name for
all COPY (on_error table)
operations, which seems not ideal IMO.
i realized we should error out case like:
COPY err_tbl FROM STDIN WITH (DELIMITER ',', on_error table, table err_tbl);
also by changing copy_generic_opt_arg, now we can
COPY err_tbl FROM STDIN WITH (DELIMITER ',', on_error table, table x);
previously, we can only do
COPY err_tbl FROM STDIN WITH (DELIMITER ',', on_error 'table', table x);
I am not sure if you understood Andrew's suggestion.
As per my understanding he did not suggest auto-creating the error table,
he suggested using TYPED TABLES for the error saving table. For example:
postgres=# CREATE TYPE error_saving_table_type AS (userid oid, copy_tbl oid,
filename text, lineno bigint, line text, colname text, raw_field_value text,
err_message text, err_detail text, errorcode text);
CREATE TYPE
CREATE TYPE
We can have something similar like above in some initdb script, which will help
in making the above type kind of derived or standard error saving table type in
PG.
And then, user can use above TYPE to create error saving table like below:
postgres=# CREATE TABLE error_saving_table OF error_saving_table_type;
CREATE TABLE
CREATE TABLE
After this, user can use error_saving_table with the COPY command like below:
COPY t_copy_tbl(a,b) FROM STDIN WITH (DELIMITER ',', on_error table,
table error_saving_table);
Here's manual example of insert in that table:
postgres=# INSERT INTO error_saving_table VALUES (1234, 4321, 'abcd', 12,
'This is was getting copied', 'xyz', 'pqr', 'testing type error table',
'inserting into typed table error saving table', 'test error code');
INSERT 0 1
postgres=# SELECT * from error_saving_table;
userid | copy_tbl | filename | lineno | line | colname |
INSERT 0 1
postgres=# SELECT * from error_saving_table;
userid | copy_tbl | filename | lineno | line | colname |
raw_field_value | err_message |
err_detail | errorcode
--------+----------+----------+--------+----------------------------+---------+-----------------
err_detail | errorcode
--------+----------+----------+--------+----------------------------+---------+-----------------
+--------------------------+-------
----------------------------------------+-----------------
1234 | 4321 | abcd | 12 | This is was getting copied | xyz | pqr |
----------------------------------------+-----------------
1234 | 4321 | abcd | 12 | This is was getting copied | xyz | pqr |
testing type error table | insert
ing into typed table error saving table | test error code
(1 row)
ing into typed table error saving table | test error code
(1 row)
With the above we don't need to check all the 12 column's count, their data
types etc. in the patch. "Then all you would need to check is the reloftype to
make sure it's the right type," as quoted by Andrew.
This will make patch simpler and also will remove burden on users to create
error saving tables with correct columns. As its TYPE will be already available
by default for the users to create error saving tables.
Regards,
Nishant.
В списке pgsql-hackers по дате отправления: