[BUG]Invalidate relcache when setting REPLICA IDENTITY
tanghy.fnst@fujitsu.com <tanghy.fnst@fujitsu.com>
From: "tanghy.fnst@fujitsu.com" <tanghy.fnst@fujitsu.com>
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Cc: "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>
Date: 2021-11-10T07:11:57Z
Lists: pgsql-hackers
Attachments
- 0001-Invalidate-relcache-when-setting-REPLICA-IDENTITY.patch (application/octet-stream) patch 0001
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
Commits
-
Invalidate relcache when changing REPLICA IDENTITY index.
- 2c0443c59570 10.20 landed
- 40fb634b1a7b 11.15 landed
- 9816e2d31826 12.10 landed
- 63c3eeddc2db 13.6 landed
- 232fd72a5eab 14.2 landed
- 354a1f8d220f 15.0 landed