COPY ON_CONFLICT TABLE; save duplicated record to another table.

First seen: 2026-04-25 04:12:50+00:00 · Messages: 10 · Participants: 3

Latest Update

2026-06-01 · claude-opus-4-6

COPY ON_CONFLICT TABLE — May 2026 Monthly Summary

Overview

This thread proposes extending PostgreSQL's COPY FROM with the ability to redirect rows that violate unique/primary key constraints into a separate "conflict table" rather than aborting the entire load. The feature is the bulk-load analog of INSERT ... ON CONFLICT DO NOTHING, targeting ETL workloads that ingest semi-clean external data.

The month saw rapid iteration from v1 (labeled v20 in the commitfest) through v3, driven by two external reviewers who uncovered crashes, data loss, and design gaps. A major architectural pivot occurred mid-month when the author abandoned hand-rolled conflict detection in favor of delegating to the executor's existing ExecInsert function.

Proposed Syntax

COPY target FROM ... (ON_CONFLICT TABLE, CONFLICT_TABLE conflict_tbl)

The conflict table must have exactly four columns: OID (target table), TEXT (source filename), INT8 (line number), TEXT (raw input line). Validation is by type, not column name.

Key Architectural Evolution

v1/v20: Hand-rolled conflict detection in copyfrom.c

v2: Delegation to ExecInsert

v3: Bug fixes and refinements

Critical Bugs Found and Status

Bug Severity Found By Status in v3
Crash on partitioned targets Server crash Zsolt Parragi Likely fixed by ExecInsert delegation (not explicitly confirmed)
Crash under REPEATABLE READ Server crash Zsolt Parragi Likely fixed by ExecInsert delegation (not explicitly confirmed)
Silent data loss (no indexes + trigger) Data corruption Zsolt Parragi Likely fixed by ExecInsert delegation
COPY TO accepts conflict options Wrong behavior Jim Jones, Zsolt Parragi Fixed
Binary mode crash Server crash Jim Jones, Zsolt Parragi Fixed in v3
Use-after-close of conflict relation Crash/corruption Jim Jones, Zsolt Parragi Open — needs move to EndCopyFrom()
Unconditional ExecOpenIndices(..., true) Performance/correctness Jim Jones Fixed in v2 architecture

Surprise Finding: Exclusion Constraints Work

Despite documentation stating otherwise, Zsolt confirmed that EXCLUDE USING gist(...) constraints are correctly handled by the v3 patch — the ExecInsert delegation picks up this capability for free. Only the error message text is wrong (says "unique constraint" for exclusion violations). This validates the architectural pivot.

Unresolved Issues

  1. Unification with ON_ERROR table (CF 4817): Both features need a user-named sink table with similar validation and dispatch. Pursuing them independently risks divergent syntax. Not discussed this month.
  2. No committer engagement: The design has not been validated by any committer.
  3. BEFORE triggers on conflict table silently dropping rows: Standard PostgreSQL behavior but operationally surprising for a "save my rejected rows" feature. Documentation concern shared with ON_ERROR work.
  4. Performance under heavy conflicts: Per-row routing to conflict table defeats multi-insert buffering. No batching strategy proposed.
  5. Syntax aesthetics: ON_CONFLICT TABLE, CONFLICT_TABLE x is verbose; cleaner alternatives suggested but not adopted.

Month-End State

The patch is at v3 with one known open bug (use-after-close) and several documentation corrections needed. The ExecInsert delegation architecture is a clear improvement over v1 and handles more cases than the author initially expected. The patch remains in early review with no committer signoff.

History (1 prior analysis)
2026-06-01 · claude-opus-4-6

Round 5: Author Ships v4 — Exclusion Constraint Confirmation, Performance Regression Fix, Trigger Corrections

The author (Jian Universality) responds with a v4 patch addressing the accumulated feedback from Zsolt Parragi and Jim Jones across rounds 3-4.

Key Changes in v4

1. Exclusion Constraint Support Confirmed and Documented

The author has verified through code inspection (ExecCheckIndexConstraints, ExecInsertIndexTuples) and added regression tests confirming that INSERT ON CONFLICT DO NOTHING — and by extension this COPY path via ExecInsert delegation — works correctly with exclusion constraints. This resolves the long-standing open question flagged since the initial patch submission. The documentation has been corrected to remove the previous incorrect statement that exclusion constraints are unsupported. This is architecturally significant: it confirms the ExecInsert delegation approach provides broader constraint coverage than the author originally believed.

2. Performance Regression Fixed for Tables Without Unique/Exclusion Constraints

The author identifies and fixes a performance regression in v3: when ON_CONFLICT TABLE was specified but the target table had no unique or exclusion constraints, there was measurable overhead. This connects directly to the earlier concern about unconditional speculative-insertion setup — if there are no relevant constraints, the conflict-detection path should short-circuit early. The author claims v4 reduces this regression to "very very minimal" and provides a SQL test script demonstrating the improvement. The exact mechanism of the fix isn't described in the message, but likely involves gating the speculative-insertion / conflict-detection code path on the presence of relevant indexes.

3. Trigger Semantics Corrected

v4 now fires both row-level and statement-level triggers for every insertion to the conflict table, addressing Zsolt's concern from the prior round about conditional/late firing of BEFORE STATEMENT triggers. This aligns conflict-table trigger behavior with standard INSERT semantics.

4. Jim Jones's Comments Addressed

The author states Jim's remaining comments (including the use-after-close bug where conflictRel was closed in BeginCopyFrom instead of EndCopyFrom) have been addressed. No specifics given beyond "fixed."

What Remains Unresolved

  • BEFORE triggers returning NULL on conflict table: Zsolt's concern about silent row dropping is not explicitly addressed in this message. It may be considered acceptable behavior (standard trigger semantics) or may need documentation.
  • Binary mode: Presumably still rejected per v3's fix, but not re-discussed.
  • Unification with ON_ERROR table (CF 4817): Still not discussed.
  • No committer engagement: Still no committer has weighed in on the design or syntax.