Logical replication commits duplicates in DEFERRABLE unique indexes

From: Michael Malis <malis(at)pgrust(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Logical replication commits duplicates in DEFERRABLE unique indexes
Date: 2026-11-16 20:28:11
Message-ID: CANVD+MD+B3vbSN+7appXg1h=8R=EMyc4vnsxnsYqtZWR+15X7w@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

Cheers,
Michael

Browse pgsql-bugs by date

  From Date Subject
Next Message Michael Malis 2026-11-16 20:28:12 GiST: SERIALIZABLE misses conflict when scan prunes at internal page
Previous Message Michael Malis 2026-11-16 20:28:10 dbase_redo: XLOG_DBASE_CREATE_FILE_COPY replay empties live database