[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  (Amit Kapila <amit.kapila16@gmail.com>)
Список 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 по дате отправления:

Предыдущее
От: Peter Smith
Дата:
Сообщение: Re: row filtering for logical replication
Следующее
От: Greg Nancarrow
Дата:
Сообщение: Re: On login trigger: take three