[BUG]Invalidate relcache when setting REPLICA IDENTITY
От | tanghy.fnst@fujitsu.com |
---|---|
Тема | [BUG]Invalidate relcache when setting REPLICA IDENTITY |
Дата | |
Msg-id | OS0PR01MB61133CA11630DAE45BC6AD95FB939@OS0PR01MB6113.jpnprd01.prod.outlook.com обсуждение исходный текст |
Ответы |
Re: [BUG]Invalidate relcache when setting REPLICA IDENTITY
|
Список | pgsql-hackers |
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 ofhim) 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
Вложения
В списке pgsql-hackers по дате отправления: