| From: | Shlok Kyal <shlok(dot)kyal(dot)oss(at)gmail(dot)com> |
|---|---|
| To: | Michael Malis <malis(at)pgrust(dot)com> |
| Cc: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Subject: | Re: Logical replication commits duplicates in DEFERRABLE unique indexes |
| Date: | 2026-11-18 10:46:12 |
| Message-ID: | CANhcyEXq1Ax5rQDeAv18CYAp6G3CKSte1crmPuCuCFw6CmOprA@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
On Tue, 17 Nov 2026 at 03:36, Michael Malis <malis(at)pgrust(dot)com> wrote:
>
> Reproduced on PostgreSQL 18.7 (REL_18_7, commit f5c41f6681c9), and verified
> in the source on REL_18_STABLE (commit 2f613719fa4e) and master (commit
> 7fc2f0423320), where the deferred constraint trigger creation and
> TriggerEnabled() checks are unchanged. Logical replication apply and
> tablesync workers do not fire the internal unique_key_recheck() trigger for
> DEFERRABLE PRIMARY KEY, UNIQUE, and EXCLUDE constraints (both INITIALLY
> IMMEDIATE and INITIALLY DEFERRED).
> Replicated INSERT, UPDATE, and initial COPY changes that conflict with an
> existing subscriber row commit duplicate keys directly into the unique
> index (or conflicting entries into the exclusion index) without error.
> Afterward, queries return duplicate primary keys, amcheck's
> bt_index_check() with checkunique enabled reports an index uniqueness
> violation, and REINDEX TABLE fails.
>
> In doc/src/sgml/logical-replication.sgml (Section "Conflicts"), logical
> replication states that incoming data violating any constraints stops
> replication (while scoping extended conflict logging such as insert_exists
> and update_exists to NOT DEFERRABLE unique constraints). Meanwhile,
> doc/src/sgml/config.sgml warns under session_replication_role that replica
> mode disables foreign key checks because foreign keys are implemented as
> triggers, and doc/src/sgml/ref/alter_table.sgml notes under DISABLE/ENABLE
> TRIGGER that deferrable uniqueness and exclusion constraints also use
> internally generated constraint triggers (which ENABLE TRIGGER USER skips
> and non-superusers cannot alter). Unlike a skipped foreign key check,
> skipping unique_key_recheck() after index insertion has already taken the
> UNIQUE_CHECK_PARTIAL path leaves duplicate keys committed inside a
> pg_index.indisunique = true index.
>
> 1. Logical replication reproduction (two clusters initialized with the
> same default superuser, publisher on port 55430 with wal_level = logical,
> subscriber on port 55431, no manual session_replication_role changes):
>
> -- On publisher (port 55430):
> CREATE TABLE ledger (
> id int PRIMARY KEY DEFERRABLE,
> tag text UNIQUE DEFERRABLE INITIALLY DEFERRED
> );
> ALTER TABLE ledger REPLICA IDENTITY FULL;
> INSERT INTO ledger VALUES (1, 'pub_copy'), (10, 'ten');
> CREATE PUBLICATION ledger_pub FOR TABLE ledger;
>
> -- On subscriber (port 55431):
> CREATE EXTENSION amcheck;
> CREATE TABLE ledger (
> id int PRIMARY KEY DEFERRABLE,
> tag text UNIQUE DEFERRABLE INITIALLY DEFERRED
> );
> INSERT INTO ledger VALUES (1, 'local_1'), (2, 'local_2'), (3, 'dup_tag');
> CREATE SUBSCRIPTION ledger_sub
> CONNECTION 'host=127.0.0.1 port=55430 dbname=postgres'
> PUBLICATION ledger_pub;
>
> -- On publisher, after initial table sync reaches state 'r':
> INSERT INTO ledger VALUES (2, 'pub_ins');
> UPDATE ledger SET tag = 'dup_tag' WHERE id = 10;
>
> -- On subscriber, after replay:
> SELECT id, tag FROM ledger ORDER BY id, tag;
> -- id | tag
> -- ----+----------
> -- 1 | local_1
> -- 1 | pub_copy
> -- 2 | local_2
> -- 2 | pub_ins
> -- 3 | dup_tag
> -- 10 | dup_tag
> -- (6 rows)
>
> SELECT bt_index_check('ledger_pkey',
> heapallindexed => true,
> checkunique => true);
> -- ERROR: index uniqueness is violated for index "ledger_pkey"
> -- DETAIL: Index tid=(1,1) and tid=(1,2) (point to heap tid=(0,1) and tid=(0,4)) page lsn=0/17F4818.
> -- (page lsn in DETAIL varies across runs)
>
> SELECT bt_index_check('ledger_tag_key',
> heapallindexed => true,
> checkunique => true);
> -- ERROR: index uniqueness is violated for index "ledger_tag_key"
> -- DETAIL: Index tid=(1,1) and tid=(1,2) (point to heap tid=(0,3) and tid=(0,7)) page lsn=0/17F4858.
>
> REINDEX TABLE ledger;
> -- ERROR: could not create unique index "ledger_pkey"
> -- DETAIL: Key (id)=(1) is duplicated.
>
> 2. Single-node reproduction (psql -X on a freshly initialized cluster):
>
> Because logical replication workers run under session_replication_role =
> replica, the same path reproduces in one session across PRIMARY KEY,
> UNIQUE, and EXCLUDE constraints:
>
> CREATE EXTENSION amcheck;
>
> -- Control: NOT DEFERRABLE primary key still rejects duplicates
> CREATE TABLE ctl (id int PRIMARY KEY);
> INSERT INTO ctl VALUES (1);
> SET session_replication_role = replica;
> INSERT INTO ctl VALUES (1);
> -- ERROR: duplicate key value violates unique constraint "ctl_pkey"
> -- DETAIL: Key (id)=(1) already exists.
> RESET session_replication_role;
>
> -- DEFERRABLE PRIMARY KEY (INITIALLY IMMEDIATE) and DEFERRABLE INITIALLY
> -- DEFERRED UNIQUE constraint both commit duplicate keys into their indexes
> CREATE TABLE ledger (
> id int PRIMARY KEY DEFERRABLE,
> tag text UNIQUE DEFERRABLE INITIALLY DEFERRED
> );
> INSERT INTO ledger VALUES (1, 'a'), (2, 'b');
>
> SET session_replication_role = replica;
> INSERT INTO ledger VALUES (1, 'c');
> UPDATE ledger SET tag = 'b' WHERE id = 1 AND tag = 'c';
> RESET session_replication_role;
>
> SELECT id, tag FROM ledger ORDER BY id, tag;
> -- id | tag
> -- ----+-----
> -- 1 | a
> -- 1 | b
> -- 2 | b
> -- (3 rows)
>
> SELECT bt_index_check('ledger_pkey',
> heapallindexed => true,
> checkunique => true);
> -- ERROR: index uniqueness is violated for index "ledger_pkey"
> -- DETAIL: Index tid=(1,1) and tid=(1,3) (point to heap tid=(0,1) and tid=(0,4)) page lsn=0/17FBCB0.
> -- (page lsn in DETAIL varies across runs)
>
> REINDEX TABLE ledger;
> -- ERROR: could not create unique index "ledger_pkey"
> -- DETAIL: Key (id)=(1) is duplicated.
>
> -- DEFERRABLE EXCLUDE constraint likewise commits conflicting entries
> CREATE TABLE slots (
> span int4range,
> EXCLUDE USING gist (span WITH &&) DEFERRABLE
> );
> INSERT INTO slots VALUES ('[1,5)');
>
> SET session_replication_role = replica;
> INSERT INTO slots VALUES ('[3,8)');
> RESET session_replication_role;
>
> REINDEX TABLE slots;
> -- ERROR: could not create exclusion constraint "slots_span_excl"
> -- DETAIL: Key (span)=([1,5)) conflicts with key (span)=([3,8)).
>
> Observations from the source:
>
> In src/backend/catalog/index.c, index_constraint_create() marks the
> backing pg_index row with indimmediate = false whenever deferrable is
> true (covering both INITIALLY IMMEDIATE and INITIALLY DEFERRED) and calls
> CreateTrigger() with base name "PK_ConstraintTrigger" or
> "Unique_ConstraintTrigger" invoking unique_key_recheck()
> (F_UNIQUE_KEY_RECHECK in src/backend/commands/constraint.c).
> CreateTrigger() in src/backend/commands/trigger.c calls
> CreateTriggerFiringOn(..., TRIGGER_FIRES_ON_ORIGIN), creating the internal
> PK_ConstraintTrigger_<oid> or Unique_ConstraintTrigger_<oid> row in
> pg_trigger with tgenabled = 'O'.
>
> During INSERT, UPDATE, or COPY, ExecInsertIndexTuples() in
> src/backend/executor/execIndexing.c sees !indimmediate, passes
> UNIQUE_CHECK_PARTIAL to index_insert() (or violationOK = true to
> check_exclusion_or_unique_constraint()), inserts the conflicting entry
> into the index without raising an error, and appends the index OID to
> recheckIndexes so the AFTER ROW trigger will recheck it.
>
> Every logical replication apply and tablesync worker calls
> InitializeLogRepWorker() in src/backend/replication/logical/worker.c,
> which sets session_replication_role = replica. InitConflictIndexes() in
> src/backend/replication/logical/conflict.c skips !indimmediate indexes
> when building ri_onConflictArbiterIndexes for conflict reporting, so
> ExecSimpleRelationInsert() and ExecSimpleRelationUpdate() in
> src/backend/executor/execReplication.c rely on ExecARInsertTriggers() and
> ExecARUpdateTriggers() to queue unique_key_recheck(). Inside
> AfterTriggerSaveEvent() in src/backend/commands/trigger.c,
> TriggerEnabled() returns false for TRIGGER_FIRES_ON_ORIGIN when
> SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA, so
> F_UNIQUE_KEY_RECHECK is never queued and the duplicate index entries
> commit.
>
> Two details stand out if changing this behavior:
>
> 1. Changing only index_constraint_create() to use TRIGGER_FIRES_ALWAYS
> would leave existing deferrable constraints in minor-release upgrades with
> tgenabled = 'O' in pg_trigger, unless TriggerEnabled() also allows
> F_UNIQUE_KEY_RECHECK to fire in SESSION_REPLICATION_ROLE_REPLICA when
> tgenabled != TRIGGER_DISABLED.
>
> 2. In src/backend/replication/logical/worker.c, create_edata_for_relation()
> and finish_edata() bracket each replicated row change with
> AfterTriggerBeginQuery() and AfterTriggerEndQuery(). On a DEFERRABLE
> INITIALLY IMMEDIATE constraint, a publisher statement such as UPDATE ...
> SET k = k + 1 (or a transaction using SET CONSTRAINTS ... DEFERRED, which
> pgoutput does not replicate) can transiently duplicate keys across row
> steps while remaining valid at commit. Deferring F_UNIQUE_KEY_RECHECK to
> transaction end during replication apply would validate the final
> transaction state without rejecting valid multi-row updates mid-apply.
Hi Michael,
Thanks for the report.I tested the above cases and I am able to
reproduce the issues on the HEAD.
When the 'session_replication_role = replica', trigger check
corresponding to DEFERRABLE constraints is disabled. Hence the
'unique_key_recheck' call is skipped and the subscriber can have
duplicate values.
I noticed the behaviour for 'session_replication_role = replica' is
documented as:
logical-replication.sgml
```
The apply process on the subscriber database always runs with
<link linkend="guc-session-replication-role"><varname>session_replication_role</varname></link>
set to <literal>replica</literal>. This means that, by default,
triggers and rules will not fire on a subscriber. Users can optionally choose to
enable triggers and rules on a table using the<link
linkend="sql-altertable"><command>ALTER TABLE</command></link> command
and the <literal>ENABLE TRIGGER</literal> and <literal>ENABLE RULE</literal>
clauses.
```
config.sgml
```
Since foreign keys are implemented as triggers, setting this parameter
to <literal>replica</literal> also disables all foreign key checks,
which can leave data in an inconsistent state if improperly used.
```
Since we have already documented the checks for the foreign keys and
also the user has the flexibility to enable the DEFERRABLE related
triggers using ENABLE ALWAYS/REPLICA TRIGGER. Does it make sense to
only document the case related to DEFERRABLE unique indexes?
Thanks,
Shlok Kyal
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Document-skipped-deferrable-constraint-rechecks-i.patch | application/octet-stream | 1.9 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | miracee | 2026-11-18 10:56:24 | Inconsistent time zone handling in timetz |
| Previous Message | Dilip Kumar | 2026-11-18 09:57:45 | Re: BUG #19858: pg_dump --clean --if-exists fails on NOT VALID domain constraint |