relcache not invalidated when ADD PRIMARY KEY USING INDEX

From: "houzj(dot)fnst(at)fujitsu(dot)com" <houzj(dot)fnst(at)fujitsu(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: relcache not invalidated when ADD PRIMARY KEY USING INDEX
Date: 2021-12-20 06:45:33
Message-ID: OS0PR01MB5716EBE01F112C62F8F9B786947B9@OS0PR01MB5716.jpnprd01.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

When reviewing some replica identity related patches, I found that when adding
primary key using an existing unique index on not null columns, the
target table's relcache won't be invalidated.

This would cause an error When REPLICA IDENTITY is default and we are
UPDATE/DELETE a published table , because we will refer to
RelationData::rd_pkindex to check if the UPDATE or DELETE can be safety
executed in this case.

---reproduction steps
CREATE TABLE test(a int not null, b int);
CREATE UNIQUE INDEX a ON test(a);
CREATE PUBLICATION PUB for TABLE test;
UPDATE test SET a = 2;
ERROR: cannot update table "test" because it does not have a replica identity and publishes updates
HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.

alter table test add primary key using index a;
UPDATE test SET a = 2;
ERROR: cannot update table "test" because it does not have a replica identity and publishes updates
HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
---

I think the bug exists in HEAD ~ PG11 after the commit(f66e8bf) which remove
relhaspkey from pg_class. In PG10, when adding a primary key, it will always
update the relhaspkey in pg_class which will invalidate the relcache, so it
was OK.

I tried to write a patch to fix this by invalidating the relcache after marking
primary key in index_constraint_create().

Best regards,
Hou zj

Attachment Content-Type Size
0001-Invalid-relcache-when-ADD-PRIMARY-KEY-USING-INDEX.patch application/octet-stream 22.1 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Langote 2021-12-20 07:13:04 Re: ExecRTCheckPerms() and many prunable partitions
Previous Message Kyotaro Horiguchi 2021-12-20 06:28:23 Re: In-placre persistance change of a relation