>
> On Tue, Dec 17, 2024 at 12:31 PM Kirill Reshke <reshkekir...@gmail.com>
> wrote:
> >
> > On Mon, 16 Dec 2024 at 16:50, Nishant Sharma
> > <nishant.sha...@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
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
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 |
raw_field_value | err_message |
err_detail | errorcode
--------+----------+----------+--------+----------------------------+---------+-----------------
+--------------------------+-------
----------------------------------------+-----------------
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)
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.
