[BUG]Invalidate relcache when setting REPLICA IDENTITY

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

Responses

Browse pgsql-hackers by date

  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