Re: on_error table, saving error info to a table

On Wed, 30 Sept 2026 at 16:05, jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> Hi.
>
> V15 makes some non-fundamental changes compared to v14.
> These are listed below:
>
> 1. Previously COPY FREEZE + ON_ERROR table does not working properly.
> We need go through CopyFrom ``if (cstate->opts.freeze)`` loop first
> then do CopyFromErrorTableInit.
>
> 2. we need following for COPY ON_ERROR table
> ```
> if (XactReadOnly && !cstate->error_rel->rd_islocaltemp)
> PreventCommandIfReadOnly("COPY FROM");
> ```
>
> 3. With COPY(ON_ERROR TABLE) in some case, we need set the
> (CopyInsertMethod insertMethod)
> to CIM_SINGLE.
>
> 4. Yes, (lineno bigint, line text) distinguishes the error rows within one COPY.
> It does not distinguish rows from different COPY FROM operations that
> write to the same
> error table, e.g., loading the same file twice.
> So I added a (starttime timestamptz) column, set to the statement start time.
> Each <command>COPY FROM</command> command records its own start time,
> so this column distinguishes the rows saved by different commands.
>
>
>
> --
> jian
> https://www.enterprisedb.com/

Hi!

I think counting the number of successfully inserted rows can be improved.

CREATE TABLE t1 (a int, b text);
CREATE TABLE err1 OF copy_error_saving;

CREATE FUNCTION drop_row() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN RETURN NULL; END $$;
CREATE TRIGGER err1_drop BEFORE INSERT ON err1
FOR EACH ROW EXECUTE FUNCTION drop_row();

COPY t1 FROM STDIN WITH (delimiter ',', on_error table, error_table err1);
1,a
x,b
y,c
\.

SELECT 'S2_actual_saved' AS tag, count(*) AS v FROM err1;

CREATE TABLE
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
psql:/home/reshke/allfuzz/pgcf-cf62f4-20261101/onerror/s2repro.sql:11:
NOTICE: 2 rows were saved to table "err1" due to data type
incompatibility
COPY 1
tag | v
-----------------+---
S2_actual_saved | 0
(1 row)

The docs do mention the NOTICE number may differ, but ExecInsert
already knows whether the tuple was actually stored.

--
Best regards,
Kirill Reshke