From: | "tanghy(dot)fnst(at)fujitsu(dot)com" <tanghy(dot)fnst(at)fujitsu(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Cc: | "houzj(dot)fnst(at)fujitsu(dot)com" <houzj(dot)fnst(at)fujitsu(dot)com> |
Subject: | [BUG]Invalidate relcache when setting REPLICA IDENTITY |
Date: | 2021-11-10 07:11:57 |
Message-ID: | OS0PR01MB61133CA11630DAE45BC6AD95FB939@OS0PR01MB6113.jpnprd01.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 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
Attachment | Content-Type | Size |
---|---|---|
0001-Invalidate-relcache-when-setting-REPLICA-IDENTITY.patch | application/octet-stream | 4.8 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Nancarrow | 2021-11-10 07:12:34 | Re: On login trigger: take three |
Previous Message | Peter Smith | 2021-11-10 07:05:52 | Re: row filtering for logical replication |