Logical replication commits duplicates in DEFERRABLE unique indexes

Поиск
Список
Период
Сортировка
Искать

Logical replication commits duplicates in DEFERRABLE unique indexes

От:
Michael Malis <malis@pgrust.com>
Дата:
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

Re: Logical replication commits duplicates in DEFERRABLE unique indexes

От:
Shlok Kyal <shlok.kyal.oss@gmail.com>
Дата:
Вложения:
On Tue, 17 Nov 2026 at 03:36, Michael Malis  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_ or Unique_ConstraintTrigger_ 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
session_replication_role
set to replica. 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 theALTER TABLE command
and the ENABLE TRIGGER and ENABLE RULE
clauses.
```
config.sgml
```
Since foreign keys are implemented as triggers, setting this parameter
to replica 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
FAQ