On Wed, 26 Aug 2026 at 16:15, shveta malik <[email protected]> wrote:
>
> How the updates on tab_nothing and tab_nopk will go through if there
> are no RIs on these on pub? Do you mean we need to have RI on pub
> while no RI on sub?
Yes, the replica identity is required on the publisher side, but the
subscriber does not need to have a replica identity configured.
>Can you give exact steps if so?
I have attached a shell script that reproduces the issue.
Regards,
Vignesh
#!/usr/bin/env bash
mkdir publisher
./initdb -D publisher
cat << EOF >> publisher/postgresql.conf
wal_level = logical
wal_sender_timeout = 0
EOF
cat << EOF >> publisher/pg_hba.conf
host all all 0.0.0.0/0 trust
EOF
./pg_ctl start -D publisher -l publisher.log
./psql -d postgres -c "
CREATE TABLE tab_full (a int, b text);
ALTER TABLE tab_full REPLICA IDENTITY FULL;
-- Publisher-side replica identity is FULL for all three tables below.
-- The subscriber-side replica identity is intentionally changed later to
-- NOTHING for tab_nothing and DEFAULT-without-PK for tab_nopk.
CREATE TABLE tab_nothing (a int, b text);
ALTER TABLE tab_nothing REPLICA IDENTITY FULL;
CREATE TABLE tab_nopk (a int, b text);
ALTER TABLE tab_nopk REPLICA IDENTITY FULL;
CREATE TABLE tab_pk (a int PRIMARY KEY, b text);
CREATE PUBLICATION pub
FOR TABLE tab_full, tab_nothing, tab_nopk, tab_pk;
INSERT INTO tab_full VALUES (1, 'from publisher');
INSERT INTO tab_nothing VALUES (1, 'from publisher');
INSERT INTO tab_nopk VALUES (1, 'from publisher');
INSERT INTO tab_pk VALUES (1, 'from publisher');
"
#!/usr/bin/env bash
cluster_name=subscriber
./initdb -D $cluster_name
cat << EOF >> $cluster_name/postgresql.conf
port = 5433
track_commit_timestamp = on
EOF
cat << EOF >> $cluster_name/pg_hba.conf
host all all 0.0.0.0/0 trust
EOF
./pg_ctl start -D $cluster_name -l $cluster_name.log
./psql -d postgres -p 5433 -c "
CREATE TABLE tab_full (a int, b text);
ALTER TABLE tab_full REPLICA IDENTITY FULL;
-- Subscriber-side replica identity is NOTHING.
-- GetRelationIdentityOrPK() returns InvalidOid for this case, but this
-- does NOT mean REPLICA IDENTITY FULL.
CREATE TABLE tab_nothing (a int, b text);
ALTER TABLE tab_nothing REPLICA IDENTITY NOTHING;
-- Subscriber-side replica identity is DEFAULT with no primary key.
-- GetRelationIdentityOrPK() also returns InvalidOid for this case.
-- This is another case that must not be reported as REPLICA IDENTITY FULL.
CREATE TABLE tab_nopk (a int, b text);
-- Subscriber-side DEFAULT with a primary key has a valid replica identity
-- index, so GetRelationIdentityOrPK() returns the primary-key index OID.
CREATE TABLE tab_pk (a int PRIMARY KEY, b text);"
./psql -d postgres -p 5433 -c "CREATE SUBSCRIPTION sub1 CONNECTION 'host=localhost port=5432 dbname=postgres' PUBLICATION pub with (conflict_log_destination = 'table')"
sleep 1
./psql -d postgres -p 5433 -c " UPDATE tab_full SET b = b;
UPDATE tab_nothing SET b = b;
UPDATE tab_nopk SET b = b;
UPDATE tab_pk SET b = b;
"
./psql -d postgres -c "UPDATE tab_full SET b = 'from subscriber conflict';
UPDATE tab_nothing SET b = 'from subscriber conflict';
UPDATE tab_nopk SET b = 'from subscriber conflict';
UPDATE tab_pk SET b = 'from subscriber conflict';"
sleep 3
## Subscriber's actual replica identity configuration:
## tab_full : 'f' = REPLICA IDENTITY FULL
## tab_nothing : 'n' = REPLICA IDENTITY NOTHING
## tab_nopk : 'd' = REPLICA IDENTITY DEFAULT, with no primary key
## tab_pk : 'd' = REPLICA IDENTITY DEFAULT, with a primary key
./psql -d postgres -p 5433 -c "SELECT relname, relreplident FROM pg_class WHERE relname IN
('tab_full', 'tab_nothing', 'tab_nopk', 'tab_pk');"
conflict_log_relid=$(./psql -d postgres -p 5433 -Atc \
"SELECT oid FROM pg_subscription WHERE subname = 'sub1';")
## Conflict log result:
## tab_full : t -> correct; the relation uses REPLICA IDENTITY FULL.
## tab_nothing : t -> incorrect; the relation uses REPLICA IDENTITY NOTHING.
## tab_nopk : t -> incorrect; the relation uses REPLICA IDENTITY DEFAULT
## and has no primary key.
## tab_pk : f -> correct; the primary-key index is used as the
## replica identity.
./psql -d postgres -p 5433 -c \
"SELECT relname, replica_identity_full
FROM pg_conflict.pg_conflict_log_${conflict_log_relid};"
