Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xRYdx-000000014up-18cB for pgsql-bugs@arkaria.postgresql.org; Thu, 12 Nov 2026 17:31:25 +0000 Received: from localhost ([127.0.0.1]:47730 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xRYds-0000000451F-1W8l for pgsql-bugs@arkaria.postgresql.org; Thu, 12 Nov 2026 17:31:20 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:58324) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xRGfp-0000000DGBF-03M3 for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 22:20:09 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xRGfm-00000000XcD-29yz for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 22:20:09 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=jIo7MJGft92svmjBw8IIWM/n+GoYglkC8lAqp2Etz9o=; b=VnoOdCspfpEYF2PgKyFqjvokko M/J05KmE1tyggZhX4qqvW49zoc7l8MdjovfIVq5cANPVm+K/GypmYm9zlQQgJgjUE0EqTWrD5e0Ok fk64wOGdGvhtjGEFr4EjvYuvKo17BfKxr9UspJW7I3X9iVpnPMyXpHbLD/0exlikAwe+WcqZ27Tss sXa2dFrOXI/LiXmsFspuRZdZB7w2irTen4tOxdvdBbhG9+24EcwQHK/b73rlj4wmk65169yrslKr9 KoPMbelOlmdoXk0Bw5/WM4X7RNf0visztkCQMixwo9fu2lejed53nO7eW6NGllPMm0BYwXz9HxoBm wsY/WSjQ==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xRGfk-00000001DXk-3m9z for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 22:20:05 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xRGfj-0000000366P-3PSj for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 22:20:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: kehan5800@gmail.com Reply-To: kehan5800@gmail.com, pgsql-bugs@lists.postgresql.org Date: Wed, 11 Nov 2026 22:19:12 +0000 Message-ID: <19892-351e4f794818a1e7@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk The following bug has been logged on the website: Bug reference: 19892 Logged by: Ke Email address: kehan5800@gmail.com PostgreSQL version: 18.6 Operating system: Ubuntu 22.04 x86_64 (Linux 5.15) Description: =20 When a published table has REPLICA IDENTITY FULL and the subscriber's table has no primary key or replica identity index, the apply worker looks up the old row through the first suitable index of the subscriber's table, and since PostgreSQL 17 that can be a hash index. If the indexed column is NULL in the old row, the lookup finds nothing: the UPDATE or DELETE is not applied, and the worker only logs an update_missing or delete_missing conflict (on 17, nothing at the default log level). The subscription keeps running, and the subscriber keeps the old version of the updated row and the deleted row. With a btree index on the same column, or with no index (sequential scan), the same changes are applied correctly. The documentation lists hash indexes as candidates without a restriction on NULL values: "Candidate indexes must be btree or hash, non-partial, and the leftmost index field must be a column (not an expression) that references the published table column." (Logical Replication, Replica Identity). The comment above IsIndexUsableForReplicaIdentityFull() also says that, unlike for primary keys, nullable columns are supported for these indexes. Script (two new clusters on ports 5446 and 5447; the second argument selects the subscriber's index: hash, btree or none): ---- repro.sh: PGBIN=3D/usr/local/pgsql/bin sh repro.sh /tmp/lrhash hash --= -- #!/bin/sh # usage: PGBIN=3D/usr/local/pgsql/bin sh repro.sh /tmp/lrhash [hash|btree|none] W=3D${1:?work directory}; AM=3D${2:-hash}; PUB=3D5446; SUB=3D5447 rm -rf "$W"; mkdir -p "$W" for n in pub sub; do "$PGBIN/initdb" -D "$W/$n" -U postgres -A trust >/dev/null printf "listen_addresses =3D ''\nunix_socket_directories =3D '%s'\nwal_le= vel =3D logical\n" "$W" >> "$W/$n/postgresql.conf" done "$PGBIN/pg_ctl" -D "$W/pub" -o "-p $PUB" -l "$W/pub.log" -w start >/dev/null "$PGBIN/pg_ctl" -D "$W/sub" -o "-p $SUB" -l "$W/sub.log" -w start >/dev/null pub() { "$PGBIN/psql" -X -q -At -h "$W" -p $PUB -U postgres -c "$1"; } sub() { "$PGBIN/psql" -X -q -At -h "$W" -p $SUB -U postgres -c "$1"; } pub "CREATE TABLE t (a int, b text)" pub "ALTER TABLE t REPLICA IDENTITY FULL" pub "CREATE PUBLICATION p FOR TABLE t" sub "CREATE TABLE t (a int, b text)" [ "$AM" =3D none ] || sub "CREATE INDEX t_a_idx ON t USING $AM (a)" sub "CREATE SUBSCRIPTION s CONNECTION 'host=3D$W port=3D$PUB user=3Dpostgres dbname=3Dpostgres' PUBLICATION p" 2>/dev/null until [ "$(sub "SELECT count(*) FROM pg_subscription_rel WHERE srsubstate <> 'r'")" =3D 0 ]; do sleep 0.2; done pub "INSERT INTO t VALUES (1, 'one'), (NULL, 'null-1'), (NULL, 'null-2'), (2, 'two')" pub "UPDATE t SET b =3D 'null-1 updated' WHERE b =3D 'null-1'" pub "DELETE FROM t WHERE b =3D 'null-2'" pub "UPDATE t SET b =3D 'two updated' WHERE a =3D 2" pub "DELETE FROM t WHERE a =3D 1" pub "INSERT INTO t VALUES (99, 'marker')" # wait until the subscriber has applied everything until [ "$(sub "SELECT count(*) FROM t WHERE a =3D 99")" =3D 1 ]; do sleep = 0.2; done echo "=3D=3D publisher"; pub "SELECT a, b FROM t ORDER BY b" echo "=3D=3D subscriber (index: $AM)"; sub "SELECT a, b FROM t ORDER BY b" echo "=3D=3D subscriber log" grep -E 'conflict detected|Could not find' "$W/sub.log" | sed 's/^[^[]*\[[0-9]*\] //' "$PGBIN/pg_ctl" -D "$W/sub" -w stop >/dev/null "$PGBIN/pg_ctl" -D "$W/pub" -w stop >/dev/null ---- end ---- Output on master (d0a4f66217, and bb8a88bbd1 before it); 19rc1 prints the same: =3D=3D publisher 99|marker |null-1 updated 2|two updated =3D=3D subscriber (index: hash) 99|marker |null-1 |null-2 2|two updated =3D=3D subscriber log LOG: conflict detected on relation "public.t": conflict=3Dupdate_missing DETAIL: Could not find the row to be updated: remote row (null, null-1 updated), replica identity full (null, null-1). LOG: conflict detected on relation "public.t": conflict=3Ddelete_missing DETAIL: Could not find the row to be deleted: replica identity full (null, null-2). Expected: the subscriber has the same rows as the publisher. That is what happens with "btree" or "none" as the second argument, and then no conflict is logged: =3D=3D subscriber (index: btree) 99|marker |null-1 updated 2|two updated The rows with a non-NULL key are replicated correctly in every case. Later changes of an affected row are skipped as well, so the subscriber does not converge again: after appending UPDATE t SET b =3D 'null-1 again' WHERE b = =3D 'null-1 updated' and UPDATE t SET a =3D 5 WHERE b =3D 'null-1 again' to the script, the publisher has (5, 'null-1 again') and the subscriber still has (NULL, 'null-1'), with two more update_missing conflicts. Versions tested (SELECT version()): PostgreSQL 20devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.3) 11.4.0, 64-bit (master d0a4f66217, 2026-11-08, and bb8a88bbd1, 2026-11-06; ./configure --enable-debug --without-icu) PostgreSQL 19rc1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.3) 11.4.0, 64-bit PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit PostgreSQL 17.11 (Debian 17.11-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit 18.6 and 17.11 (PGDG packages in Debian 13 containers) give the same rows with the same statements; 17 logs nothing at the default log level, since it reports missing rows at DEBUG1. Hash indexes have been accepted for these lookups since commit edca342434 (17); 16 only uses btree indexes here. Cause, as far as I can tell: FindUsableIndexForReplicaIdentityFull() in src/backend/replication/logical/relation.c returns the first valid index for which IsIndexUsableForReplicaIdentityFull() is true. That function checks for a non-partial index, an equality strategy for every key column, a leftmost column that is a published column, and amgettuple, but not amsearchnulls, so the hash index is chosen. build_replindex_scan_key() in src/backend/executor/execReplication.c turns a NULL value of the search tuple into a scan key with SK_ISNULL | SK_SEARCHNULL. That works for btree, but hash indexes do not store NULLs (amsearchnulls is false), and _hash_first() returns no match for an SK_ISNULL key ("If the constant in the index qual is NULL, assume it cannot match any items in the index"). RelationFindReplTupleByIndex() therefore finds no tuple, and apply_handle_update_internal() and apply_handle_delete_internal() in src/backend/replication/logical/worker.c report update_missing / delete_missing and skip the change. Possible fix: either reject index AMs without amsearchnulls in IsIndexUsableForReplicaIdentityFull() (simple, but then hash indexes are never used for these lookups), or fall back to a sequential scan when a key column of the search tuple is NULL and the index cannot search for NULLs. The patch below does the latter in RelationFindReplTupleByIndex(). With it, the script above on master gives the same rows on both sides and logs no conflict, and the hash index is still used for the non-NULL keys. The subscription TAP tests pass with it (make -C src/test/subscription check: 39 files, 610 tests, on master d0a4f66217). RelationFindDeletedTupleInfoByIndex() (update_deleted detection with retain_dead_tuples) builds the same scan key and would need the same check. --- a/src/backend/executor/execReplication.c +++ b/src/backend/executor/execReplication.c @@ -175,6 +175,30 @@ } =20 /* + * Return true if a key column of 'idxrel' is NULL in 'searchslot' and the + * index access method cannot search for NULLs. An index scan with such a + * scan key returns nothing (hash indexes do not store NULLs). + */ +static bool +replindex_key_unsearchable(Relation idxrel, TupleTableSlot *searchslot) +{ + int2vector *indkey =3D &idxrel->rd_index->indkey; + + if (idxrel->rd_indam->amsearchnulls) + return false; + + for (int i =3D 0; i < IndexRelationGetNumberOfKeyAttributes(idxrel); i++) + { + AttrNumber attno =3D indkey->values[i]; + + if (AttributeNumberIsValid(attno) && searchslot->tts_isnull[attno - 1]) + return true; + } + + return false; +} + +/* * Search the relation 'rel' for tuple using the index. * * If a matching tuple is found, lock it with lockmode, fill the slot with its @@ -206,6 +230,18 @@ /* Open the index. */ idxrel =3D index_open(idxoid, RowExclusiveLock); =20 + /* + * If the index cannot search for a NULL key value, find the tuple with a + * sequential scan instead. Only an index chosen for REPLICA IDENTITY + * FULL can have nullable key columns, and then 'searchslot' holds the + * whole row. + */ + if (!skipduplicates && replindex_key_unsearchable(idxrel, searchslot)) + { + index_close(idxrel, NoLock); + return RelationFindReplTupleSeq(rel, lockmode, searchslot, outslot); + } + InitDirtySnapshot(snap); =20 /* Build scan key. */ Workaround: use a btree index instead of the hash index on the subscriber. The recent fixes in this code path (ea9c9432c8 for invalid indexes, ad36e3608c for concurrent index DDL, 3cde90ecc5 for lossy GiST identity indexes) do not change this case; master still behaves as shown above.