Hi I think I found a bug related to logical replication(REPLICA IDENTITY in specific). If I change REPLICA IDENTITY after creating publication, the DELETE/UPDATE operations won't be replicated as expected.
For example: -- publisher CREATE TABLE tbl(a int, b int); ALTER TABLE tbl ALTER COLUMN a SET NOT NULL; CREATE UNIQUE INDEX idx_a ON tbl(a); ALTER TABLE tbl ALTER COLUMN b SET NOT NULL; CREATE UNIQUE INDEX idx_b ON tbl(b); ALTER TABLE tbl REPLICA IDENTITY USING INDEX idx_a; CREATE PUBLICATION pub FOR TABLE tbl; ALTER TABLE tbl REPLICA IDENTITY USING INDEX idx_b; INSERT INTO tbl VALUES (1,1); -- subscriber CREATE TABLE tbl(a int, b int); ALTER TABLE tbl ALTER COLUMN b SET NOT NULL; CREATE UNIQUE INDEX idx_b ON tbl(b); ALTER TABLE tbl REPLICA IDENTITY USING INDEX idx_b; CREATE SUBSCRIPTION sub CONNECTION 'dbname=postgres port=5432' PUBLICATION pub; SELECT * FROM tbl; -- publisher postgres=# UPDATE tbl SET a=-a; UPDATE 1 postgres=# SELECT * FROM tbl; a | b ----+--- -1 | 1 (1 row) -- subscriber postgres=# SELECT * FROM tbl; a | b ---+--- 1 | 1 (1 row) (a in subscriber should be -1) But if I restart a psql client before executing UPDATE operation in publication, it works well. So I think the problem is: when using "ALTER TABLE ... REPLICA IDENTITY USING INDEX ...", relcahe was not invalidated. Attach a patch to fix it, I also added a test case for it.(Hou helped me to write/review this patch, which is very kind of him) FYI: I also tested that same problem could be reproduced on PG10 ~ PG14. (Logical replication is introduced in PG10.) So I think backpatch is needed here. Regards Tang
0001-Invalidate-relcache-when-setting-REPLICA-IDENTITY.patch
Description: 0001-Invalidate-relcache-when-setting-REPLICA-IDENTITY.patch
