Remote UPDATE/DELETE silently skipped as update_delete on rows that exist and are lockable (self-compiled 2.1.8)

[gzuco]

Summary

On a two-node group, some remote UPDATEs are consistently rejected on the receiving node with

CONFLICT: remote UPDATE: could not find existing row. Resolution: skip_change; PKEY:

The row exists, is reachable by primary key, is lockable and is updatable locally. INSERTs always replicate; UPDATE/DELETE are skipped. The result is permanent, silent data divergence: the channel stays healthy (slot active, reserved, ~0 lag), nothing is logged as an error, and only pgactive.pgactive_conflict_history records the loss.

It is reproducible on demand: every UPDATE on the affected row produces a new conflict row.

Update (important): the behaviour is symmetric. It was initially observed only in the
direction RDS → self-compiled node, which suggested a problem with that node. It is not.
Updating the same row from the node towards RDS produces the identical conflict
on RDS (update_delete → skip_change, local_tuple null) — the first conflict ever
recorded on that instance. So the affected row cannot be located by the apply worker on
either side, while being perfectly reachable by SQL on both. This rules out node-local
state (restore, re-join leftovers, build) as the explanation and points at something about
the row itself, or about how its key is transmitted.

Reproduction (production data, two nodes)

-- on the writer node
UPDATE product_feature SET role='addon'
 WHERE product_code='travelsafe_vpn_monthly' AND feature_key='voice.text_chat';
-- UPDATE 1

On the peer, ~20 ms later:

conflict_type       | update_delete
conflict_resolution | skip_change
local_tuple         | (null)
remote_tuple        | {"product_code":"travelsafe_vpn_monthly","feature_key":"voice.text_chat",
                       "display_order":10,"config":null,"role":"addon"}

Meanwhile, on that same peer, the very same row:

SELECT count(*) FROM product_feature
 WHERE product_code='travelsafe_vpn_monthly' AND feature_key='voice.text_chat';         -- 1

BEGIN;
SELECT ctid FROM product_feature
 WHERE product_code='travelsafe_vpn_monthly' AND feature_key='voice.text_chat'
   FOR UPDATE;                                                                          -- (0,35), locked fine
UPDATE product_feature SET role='addon'
 WHERE product_code='travelsafe_vpn_monthly' AND feature_key='voice.text_chat';         -- UPDATE 1
ROLLBACK;

Running the same UPDATE in the opposite direction (on the node, towards RDS) yields the same conflict on RDS, with the same empty local_tuple.

An UPDATE issued in the same transaction as a batch is applied partially: in the original incident 5 rows were updated on the writer and 4 arrived, one was skipped. In a controlled test with 7 freshly inserted rows, 2 replicated and 5 were skipped, with no discernible pattern in the key (upper/lower case, hyphen, leading underscore, leading digit all present on both sides). The UPDATE of a row inserted by replication seconds earlier is also skipped, and its DELETE too (delete_delete → skip_change).

Ruled out by testing

Hypothesis	Verification	Result
Missing PK / replica identity	relreplident='d', PK (product_code, feature_key) present on both	ruled out
Column layout mismatch	pg_attribute incl. dropped placeholders — identical fingerprints	ruled out
Invalid / corrupt PK index	EXPLAIN ANALYZE shows real Index Scan; index-only scan returns the row; heap count = index count	ruled out
Collation / glibc divergence	glibc 2.26 (RDS) vs 2.36 (node), but comparisons and orderings identical on both, incl. 1027 rows with Cyrillic/Korean names	ruled out
Key bytes differing	hex of both key columns identical; searching one node with the bytes taken from the other returns the row	ruled out
Visibility / locking	txid_status = committed on both; FOR UPDATE succeeds; local UPDATE = 1 row	ruled out
Stale relcache in apply worker	worker terminated and restarted → identical behaviour	ruled out
Replication set	{default,all} for the table on both nodes	ruled out
Leftover replication origin	orphan origin from an earlier join dropped → no change	ruled out
Node restart	full Postgres restart of the node → no change	ruled out
Node-local state / build	same conflict occurs on RDS in the reverse direction	ruled out

With tracing enabled

pgactive.debug_trace_replay=on, pgactive.log_conflicts_to_logfile=on, pgactive.log_min_messages=debug2:

TRACE: apply BEGIN  ... xid 447331 ... from node aws-milano
TRACE: apply UPDATE from remote relation public.product_feature ... (action #2)
CONFLICT: remote UPDATE: could not find existing row. Resolution: skip_change; PKEY:
TRACE: apply COMMIT ... (action #3)

A different row of the same table, updated 10 seconds later, applies cleanly with no conflict. (The empty PKEY: is expected — pgactive_conflict_logging.c builds that string from the local tuple, which is null by definition here.)

Reading process_remote_update() in src/pgactive_apply.c, the lookup path is RelationGetReplicaIndex() → build_index_scan_key() on the remote tuple → find_pkey_tuple(). Everything that feeds that path checks out from the outside, so the failure appears to be inside the lookup itself.

Environment

	Node A	Node B
pgactive	2.1.8, built from source	2.1.8, Amazon RDS binary
PostgreSQL	17.10 (now 17.11)	17.10
OS / build	Debian bookworm, postgres:17.x-bookworm image, ./configure && make	Amazon RDS
Settings	skip_ddl_replication=on, log_conflicts_to_table=on	same

The self-compiled node was re-joined on 2026-08-09 (pgactive_join_group with bypass_collation_check := true, after several failed attempts), and every conflict on that node dates from then. However, since the same failure now reproduces on RDS for the same row, the re-join no longer looks like the cause. A second table, identity.breach_catalog, produces ~970 update_delete → skip_change conflicts every night when a batch job updates it.

Possibly related

We reported #324 (backend crash on DDL statements over ~1100 bytes) on the same self-compiled node. That one is clearly build-specific; this one, given the symmetry, appears not to be.

Impact

Silent, permanent divergence between nodes on an otherwise healthy channel. In our case a single skipped row broke product purchases for roughly half of the users for 24 hours, discovered only because a tester reported it. There is no error and no lag: the only trace is a row in pgactive_conflict_history, which nothing surfaces by default.

Notes

Happy to run further tests — it reproduces reliably on demand, in both directions — or to provide the full trace output.

[gzuco]

Update — the failure is symmetric, which rules out node-local causes.

I originally reported this as "the self-compiled node cannot find the row". That framing was wrong, and the issue body has been updated accordingly.

Running the same UPDATE on the affected row from the node towards RDS produces an identical conflict on RDS:

conflict_id         | 1                      <- first conflict ever recorded on this instance
object_name         | product_feature
conflict_type       | update_delete
conflict_resolution | skip_change
local_tuple         | (null)
remote_tuple        | {"product_code":"travelsafe_vpn_monthly","feature_key":"voice.text_chat",
                       "display_order":10,"config":null,"role":"addon"}

So neither node can locate that row when the UPDATE arrives through replication, while both find it instantly with plain SQL (SELECT, SELECT ... FOR UPDATE, local UPDATE — all return 1 row).

This eliminates everything node-specific that I had suspected: the restore/re-join of 2026-08-09, leftover replication origins, the self-compiled build, and PostgreSQL 17.11 on that side. RDS is an untouched managed instance and behaves exactly the same way.

What remains is that certain rows are not locatable by find_pkey_tuple() during apply, on any node, while being perfectly locatable through the normal executor path with byte-identical key values. Neighbouring rows in the same table, with the same structure and key shape, replicate without any problem.

Happy to run any targeted test that would help — the case reproduces on demand in both directions.

[gzuco]

Further narrowing — the emitted WAL is correct, and the behaviour is deterministic per row.

Three more results.

1. It is not the physical tuple version. The affected row was rewritten locally on the node (new ctid, new xmin, new commit timestamp — it was previously the tuple created by the join restore). A subsequent remote UPDATE is still rejected with the same update_delete → skip_change.

2. It is strictly deterministic per row. Three consecutive remote UPDATEs on a neighbouring row of the same table (VIAGGIO_BASE, same feature_key, same structure) were all applied, zero conflicts. The affected row fails every time, the healthy row succeeds every time. No randomness, no batch effect: single-row UPDATEs behave the same way as multi-row ones.

3. The emitted WAL is identical for both rows. Decoded through a temporary test_decoding slot on the emitting side:

table public.product_feature: UPDATE: product_code[text]:'travelsafe_vpn_monthly'
  feature_key[text]:'voice.text_chat' display_order[integer]:10 config[jsonb]:null
  role[character varying]:'addon'

table public.product_feature: UPDATE: product_code[text]:'VIAGGIO_BASE'
  feature_key[text]:'voice.text_chat' display_order[integer]:10 config[jsonb]:null
  role[character varying]:'addon'

Same shape, all columns present, key values present and correct in both. The first one is skipped on apply, the second one is applied.

So the decoding/emitting side produces the right message, and the receiving side cannot locate a row that plain SQL finds instantly with byte-identical key values — on both nodes, for the same row, every time.

Also worth noting: the affected product has two rows in this table (secure_net.tunnel and voice.text_chat) and both fail, while rows of other products in the same table replicate normally. In a controlled test with 7 newly inserted rows whose keys varied by case, hyphen, leading underscore and leading digit, 2 replicated and 5 were skipped — with no correlation to the shape of the key (e.g. zztest_minuscolo succeeded while ZZTEST_MAIUSCOLO failed, but VIAGGIO_BASE succeeds and travelsafe_vpn_monthly fails, both lowercase/uppercase-with-underscore respectively).

At this point everything observable from outside the extension checks out: schema, index (verified via real query plans and index-only scans), collation, key bytes, visibility, locking, replica identity, replication sets, and now the emitted WAL. Happy to run anything else that would help — including builds with debugging enabled if that is useful.

[bdrouvotAWS]

Hi,

Thanks for the detailed report!
Could you please run the first two queries on both nodes, and the last one on either node? (connected on the database where pgactive is installed).

Those queries are read-only and do not return any production row values or connection information.

  SELECT datlocprovider, datcollate, datlocale, datcollversion,
         pg_collation_actual_version('default'::regcollation)
  FROM pg_database
  WHERE datname = current_database();

  SELECT i.indexrelid::regclass, i.indnkeyatts, i.indnatts,
         k.n + 1 AS key_position, a.attname,
         i.indcollation[k.n]::regcollation AS index_collation
  FROM pg_index i
  CROSS JOIN LATERAL
       generate_series(0, i.indnkeyatts - 1) AS k(n)
  JOIN pg_attribute a
    ON a.attrelid = i.indrelid
   AND a.attnum = i.indkey[k.n]
  WHERE i.indrelid = 'public.product_feature'::regclass
    AND (i.indisprimary OR i.indisreplident)
  ORDER BY i.indexrelid, k.n;

  SELECT EXISTS (
      SELECT 1
      FROM (SELECT DISTINCT product_code
            FROM public.product_feature) p
      WHERE (product_code < 'travelsafe_vpn_monthly')
            IS DISTINCT FROM
            ((product_code COLLATE "C") <
             ('travelsafe_vpn_monthly'::text COLLATE "C"))
  ) AS ordering_differs;

Regards,
Bertrand

[gzuco]

Quantified over a large batch — and a second, distinct failure mode: partial application of multi-row transactions.

A nightly job updates all 1027 rows of identity.breach_catalog in one go. Measuring the two sides the morning after:

writer (RDS):   1027 rows, all with updated_at = today   (1027/1027 touched)
peer  (node):   1027 rows, only  567  with updated_at = today
                            → 460 rows never received the update

460 is exactly the number of update_delete → skip_change conflicts recorded that night. So ~45% of the rows in a single large transaction are silently dropped, while ~55% apply normally.

Grouping the dropped rows by the date of their last successful update:

2026-08-14:  69    <- these applied successfully the previous night, and were dropped this night
2026-08-13: 116
2026-08-12:   8
2026-08-11:   7
2026-08-10:   6
2026-08-09:  18
2026-08-08: 236    <- never updated since the node was re-joined

So membership in the "dropped" set changes from night to night for a large part of the rows.

However, single-row updates are strictly deterministic. Ten consecutive single-row UPDATEs on each of two rows in the same table, same structure, same key shape:

travelsafe_vpn_monthly / voice.text_chat :  X X X X X X X X X X   0/10 applied
VIAGGIO_BASE           / voice.text_chat :  . . . . . . . . . .  10/10 applied

Which suggests two effects at once:

1. some rows are permanently unreachable by find_pkey_tuple() (deterministic, reproducible, survives everything we tried);
2. inside a large transaction, a variable subset of rows is additionally dropped — in our data 4/5, 2/7 and 567/1027 applied in three separate cases.

Additional statistics on the 1027-row batch, in case they help:

	rows	starts with digit	starts with lowercase
applied	567	14	0
dropped	460	0	25

Every key starting with a digit was applied; every key starting with a lowercase letter was dropped. Uppercase keys (the vast majority) split across both groups, so this is a correlation rather than the rule. Ordering-based explanations are ruled out: the two sets alternate 166 times along key order and 235 times along physical (ctid) order, and every heap page contains both applied and dropped rows in similar proportions — no contiguous blocks, no "bad page".

All rows involved carry the same primary key definition, identical column layout, and byte-identical keys on both nodes.

[gzuco]

Root cause found: the apply lookup does not honour the column collation

Controlled experiment, two twin tables created on both nodes, differing only in the collation of the key column:

CREATE TABLE public.zz_diag_c       (k text COLLATE "C" PRIMARY KEY, v text);
CREATE TABLE public.zz_diag_default (k text              PRIMARY KEY, v text);  -- en_US.utf8

Same 10 keys inserted in both (all replicated fine), then one single-row UPDATE at a time on each table, from the writer:

key	COLLATE "C"	default collation
1cifra	applied	skipped
aminuscola	applied	skipped
mminuscola	applied	skipped
zminuscola	applied	applied
_underscore	applied	skipped
-trattino	applied	skipped
.punto	applied	skipped
AMAIUSCOLA	applied	skipped
ZMAIUSCOLA	applied	skipped
MixedCase	applied	skipped

COLLATE "C"        : 10/10 applied
default collation  :  1/10 applied

The only key that survives under the default collation is zminuscola, which happens to sort last under both orderings — i.e. the one key whose position is identical in both. That is exactly what the hypothesis predicts.

Consistent with the production data

On a 1027-row table (name text primary key, default collation), correlating the positional shift of each key between ORDER BY name COLLATE "en_US.utf8" and ORDER BY name COLLATE "C" against whether the nightly update was actually applied:

positional shift	rows	applied
0 (identical position)	20	100%
1 – 47	981	57.8%
51 – 91	25	0%
253	1	0%
330 – 342	2	0%
410 – 891	18	0%

Every key displaced by more than ~50 positions fails 100% of the time; keys at identical positions succeed 100% of the time.

It also explains every earlier observation:

• keys starting with a digit always replicated (digits sort first under both orderings)
• keys starting with a lowercase letter never replicated (lowercase sorts last in C, interleaved in en_US)
• keys starting with punctuation never replicated (ignored by en_US, ordered by byte in C)
• uppercase keys were mixed — sometimes the positions coincide, sometimes not
• the failure is symmetric: both nodes build their index with the column collation and both apply workers do the lookup the same way
• it is deterministic per row (0/10 vs 10/10 on two neighbouring rows), yet appears partial on large transactions simply because a batch contains a mix of displaced and non-displaced keys
• it survives node restart, worker restart, re-join, reindexing, and is independent of the build — the index is correct, the lookup is what disagrees with it

So the index is fine and the key bytes are fine: find_pkey_tuple() / build_index_scan_key() appear to perform the index scan without applying the collation declared on the key column, so the B-tree descent goes to the wrong subtree and the tuple is reported as missing → update_delete → skip_change → silent divergence.

Practical impact

Any table whose text primary key uses a non-C collation — which is the default for anyone who did not explicitly declare one — will silently lose UPDATEs and DELETEs for a large share of its rows, with a perfectly healthy replication channel and no error anywhere. In our case ~45% of the rows of one table every night.

Workaround for other users

Declaring key columns as COLLATE "C" makes the lookup and the index agree; in our test it took the result from 1/10 to 10/10. This requires an ALTER TABLE ... ALTER COLUMN ... TYPE text COLLATE "C" (rebuilding the index) applied on every node, since DDL is not replicated.

[bdrouvotAWS]

@gzuco Thanks! That confirms my suspicion. The fix proposed in #328 should address it.

[gzuco]

Thanks @bdrouvotAWS — #328 matches exactly what I arrived at independently from the source, so I'll skip the analysis and only add what the patch does not cover, plus one question.

I had traced it to the same place: build_index_scan_key() uses ScanKeyInit(), which hardcodes entry->sk_collation = C_COLLATION_OID ("We set collation to C always" — correct for hardwired catalog lookups, not for user tables), while core corrects it on the next line in build_replindex_scan_key(). sk_collation had zero matches across this repository.

One adjacent issue that #328 does not fix

The same function iterates over RelationGetNumberOfAttributes(idxrel), whereas core uses IndexRelationGetNumberOfKeyAttributes():

for (attoff = 0; attoff < RelationGetNumberOfAttributes(idxrel); attoff++)

With a covering unique index (INCLUDE), scan keys would also be built for the non-key columns. We have no such index, so I cannot provide a reproducer — flagging it because it is two lines away from the patch.

Also worth noting for the release notes: build_index_scan_keys() builds keys for every unique index, so this bug affects INSERT conflict detection on unique constraints as well, not only PK-based UPDATE/DELETE lookups. In our database, 270 text columns across 154 tables sit in a unique index with a locale-dependent collation — the blast radius is wider than the tables where we happened to notice it.

Question: RDS timeline

Our peer node is self-compiled, so we can pick up #328 as soon as it lands. The writer is RDS, where we cannot patch the extension — so until the fix ships there, the peer → writer direction stays exposed and we have to work around it by forcing binary collations on key columns (198 unique indexes). Is there an expected timeline for the fix reaching the RDS build? The answer decides whether we do that schema work or just wait.

Offer

We are in a good position to validate the fix: a deterministic per-row reproducer, a nightly job that reliably loses ~460 of 1027 rows in one table, and a self-compiled peer we can rebuild at will. Happy to run #328 on the peer and report back both the conflict counts and a full-row md5 comparison of the two nodes, if that is useful to you before merging.

Unrelated, but from the same session — #324 is not DDL-only

While investigating I hit #324 (statements over ~1100 bytes killing the backend on the self-compiled node) with read-only statements: a pg_dump --schema-only catalog query, and a ~2.5 KB catalog query of my own — the latter with TCP keepalives enabled, so it was not a network timeout. pg_postmaster_start_time() was unchanged afterwards and the replication slot stayed active/reserved, so only the backend dies. Practical consequence: pg_dump cannot be run against that node at all. I'll add this to #324 as well.