Re: on_error table, saving error info to a table

On Mon, May 26, 2025 at 2:30 PM Nishant Sharma
<nishant.sharma@enterprisedb.com> wrote:
>>
>> 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
>
> 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);
>
but where "error_saving_table_type" TYPE/TABLE will come from?
"error_saving_table_type" either comes from built-in or makes it user defined.
Preserving it as a built-in type would require broader consensus,
which is likely difficult to achieve.

user-defined: for COPY command, we can not use SPI to create another table.
also if there is already a user-defined table/type, we still need to check
if it meets the condition or not.
so overall manually check error saving table meet the criteria or not.

rebased patch attached.