relcache not invalidated when ADD PRIMARY KEY USING INDEX
Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>
From: "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2021-12-20T06:45:33Z
Lists: pgsql-hackers
Attachments
- 0001-Invalid-relcache-when-ADD-PRIMARY-KEY-USING-INDEX.patch (application/octet-stream) patch 0001
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
Commits
-
Flush table's relcache during ALTER TABLE ADD PRIMARY KEY USING INDEX.
- e2d53c8767da 12.10 landed
- d8fbbb925bdb 15.0 landed
- 3839e29c58b4 14.2 landed
- 31b7b4d26e10 13.6 landed
- 26c841ed1b2d 11.15 landed