Technical Analysis: ON_ERROR table — Saving COPY FROM Errors to a Table

Background and Motivation

PostgreSQL 17 introduced the ON_ERROR ignore option for COPY FROM, allowing rows that fail input conversion to be silently discarded (with an optional LOG_VERBOSITY verbose to emit NOTICE messages). This was a significant usability win for bulk-loading dirty data, but it has a glaring deficiency: the rejected rows and the reasons for rejection are lost. A data engineer running a 10M-row COPY has no structured way to audit which rows failed and why.

This thread, spanning from Feb 2024 through mid-2026, proposes filling that gap by persisting error metadata to a user-specified table. The original seed was a suggestion from Tom Lane (postgr.es/m/752672.1699474336@sss.pgh.pa.us) during the original ON_ERROR ignore review.

The Core Technical Problem

Capturing COPY errors to a table requires solving several distinct architectural problems:

1. Where does the schema of the error table come from? COPY is a utility command; it does not run through the normal planner/executor pipeline. The metadata to record (line number, raw field value, SQLSTATE, error message, etc.) has a fixed shape, but PostgreSQL has no precedent for a utility statement that auto-creates its auxiliary storage.

2. How do we insert into the error table from inside COPY? COPY's input-processing path (copyfromparse.c) does not currently drive an executor node. Inserting into a different relation mid-COPY requires either (a) invoking SPI (heavyweight, recursive, problematic for a utility), (b) hand-rolling heap_insert + index maintenance, or (c) bootstrapping enough of a ModifyTableState to reuse ExecInsert().

3. Syntax / grammar. TABLE is a reserved keyword, which complicates adding it as a COPY option name.

4. Security. The error table may be owned by a different user than the one running COPY; BEFORE INSERT triggers on the error table could be weaponized.

Evolution of the Design

v1 (Feb 2024): String literal option + user-provided schema

The initial syntax was ON_ERROR 'table', TABLE 'error_saving_tbl'. Because TABLE is a keyword, jian he added an explicit production in gram.y:

copy_opt_item: ... | TABLE opt_as Sconst { ... }

The user had to pre-create the error table with exactly the right 10 columns (userid oid, copy_tbl oid, filename text, lineno int8, line text, colname text, raw_field_value text, err_message text, err_detail text, errorcode text). BeginCopyFrom validated the schema column-by-column and checked INSERT privilege on every column via DirectFunctionCall3.

v2–v3: Pushback on the validation approach

Nishant Sharma (EDB) and Kirill Reshke both objected to forcing users to hand-craft a table with an exact schema. Two alternatives surfaced:

• Auto-create the table if it doesn't exist (Kirill's preference).
• Log to a file instead of a table (Nishant's initial suggestion — rejected by jian he because the table form is queryable).

jian he resisted auto-creation, citing the "avoid repeated name lookups" guidance from Noah Misch and Robert Haas, and the absence of any precedent for a utility command that internally invokes CREATE TABLE via SPI.

The pivotal suggestion: Typed tables (Andrew Dunstan, Dec 2024)

Andrew Dunstan (committer) proposed the architecturally cleanest solution:

"Wouldn't it be better to use a typed table? Then all you would need to check is the reloftype to make sure it's the right type, instead of checking all the column names and types."

This is the CREATE TABLE x OF some_composite_type feature. A single reloftype OID comparison replaces dozens of column-level checks. Nishant and Kirill both enthusiastically endorsed this.

The ambiguity was whether the composite type should be built-in or user-defined. jian he initially misread it as "user-defined" and objected that this still required schema validation; Nishant clarified in May 2025 that he meant seeding a built-in type via initdb:

v7 (Oct 2025): Built-in composite type copy_error_saving

Added to system_functions.sql:

CREATE TYPE copy_error_saving AS (
    userid oid, copy_tbl oid,
    filename text COLLATE "C", lineno bigint,
    line text COLLATE "C", colname text COLLATE "C",
    raw_field_value text COLLATE "C",
    err_message text COLLATE "C",
    err_detail text COLLATE "C",
    errorcode text COLLATE "C"
);

The COLLATE "C" annotations are deliberate: these columns store diagnostic strings, not natural language data, and C collation is faster and locale-independent.

The user now writes:

CREATE TABLE my_errs OF copy_error_saving;
COPY t FROM '...' WITH (ON_ERROR TABLE, ERROR_TABLE my_errs);

Note the syntax also evolved: on_error 'table' became on_error TABLE (unquoted), and TABLE x became ERROR_TABLE x — sidestepping the keyword conflict entirely and removing the gram.y hack.

Security concern raised by Kirill (Oct 2025)

Kirill flagged that without a security context switch, a user inserting bad rows into table A (owned by victim) could trigger writes into error table B (owned by attacker), firing attacker-controlled BEFORE INSERT triggers under the victim's privileges — a classic trigger-based privilege escalation vector (similar to the CVE-2018-1058 search_path family).

v8 (Apr 2026): Lock down the error table

jian he's response was to forbid anything that could be exploited, via CopyFromErrorTableCheck():

"It cannot have foreign key constraints; nor can it have column DEFAULT values, triggers, rules, or row-level security policies."

This restriction is pragmatic: it sidesteps the security question AND removes the need for FK validation on every inserted error row (which would be pathological at COPY scale). The tradeoff is that users cannot, e.g., add a received_at timestamptz DEFAULT now() column convenience.

v9 (May 2026): Reuse ExecInsert() via exported executor internals

The final architectural move is the most interesting from an executor-internals standpoint. Rather than hand-roll table_tuple_insert() + ExecInsertIndexTuples() (as v8 did), jian he proposes:

1. Export ExecInsert, ExecSetupTransitionCaptureState, fireBSTriggers, fireASTrigger from nodeModifyTable.c.
2. In CopyFromStateData, add a ModifyTableContext *.
3. During BeginCopyFrom, synthesize a minimal ModifyTableState/EState/ResultRelInfo using the standard helpers: ExecInitRangeTable, ExecInitResultRelation, ExecOpenIndices, CheckValidResultRel.
4. Set cstate->escontext->details_wanted = true so the soft-error machinery captures full ErrorData (detail, SQLSTATE, etc.), not just a message.
5. On each failed row, build a TupleTableSlot and call ExecInsert.

Because the error table is constrained to be a plain table (no partitions, no foreign tables, no RLS, no FKs, no triggers in the v8 sense — though ExecInsert can fire them if allowed), the setup skips ExecSetupPartitionTupleRouting, foreign-table FDW init, withCheckOptionLists, and EvalPlanQual setup. This is a clean factoring — it reuses the executor's battle-tested INSERT path (including index maintenance, constraint checking where applicable, and AFTER trigger queueing if we ever relax the restriction) instead of forking a parallel implementation in COPY.

Key Design Tradeoffs

Open Questions at Time of Last Message

1. Compatibility risk of the built-in type. jian he himself notes that changing copy_error_saving in a future major version would break existing user-created tables derived from it. This is analogous to the constraints on changing pg_class — once baked in, the schema is effectively frozen.
2. Should a starttime timestamptz column be added? Raised but unresolved.
3. Exporting ExecInsert et al. has not yet been reviewed by a committer with executor ownership. This is a non-trivial API surface expansion and will need sign-off from someone like Alvaro Herrera, Andres Freund, or Tom Lane.
4. No committer has signed off after more than two years of iteration. Andrew Dunstan drove the crucial design inflection but has not reviewed the implementation.

Architectural Significance

The interesting meta-lesson here is how the patch matured from a naive "validate each column name" design to leveraging two underused PostgreSQL features — typed tables (CREATE TABLE ... OF type) and soft error reporting (ErrorSaveContext, details_wanted) — to produce a clean, minimal interface. The v9 move to reuse ExecInsert also represents a welcome consolidation: COPY's internal path has historically diverged from the executor's INSERT path (e.g., its own multi-insert batching, its own BEFORE trigger firing), and each divergence is a maintenance liability. Threading the error-insert case through ExecInsert is a small but real step toward unification.