Disallow UPDATE/DELETE on table with unpublished generated column as REPLICA IDENTITY

From: Shlok Kyal <shlok(dot)kyal(dot)oss(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Disallow UPDATE/DELETE on table with unpublished generated column as REPLICA IDENTITY
Date: 2024-11-05 07:22:45
Message-ID: CANhcyEVw4V2Awe2AB6i0E5AJLNdASShGfdBLbUd1XtWDboymCA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

There was an issue reported recently by Sawada-san on a different thread [1].
I have created this thread to discuss the issue separately.

Currently, generated columns can be published only when we explicitly
specify it in the Publication column list.
An issue was found that UPDATE and DELETE are allowed on the table
even if its replica identity is set to generated columns that are not
published.
For example:
CREATE TABLE testpub_gencol (a INT, b INT GENERATED ALWAYS AS (a + 1)
STORED NOT NULL);
CREATE UNIQUE INDEX testpub_gencol_idx ON testpub_gencol (b);
ALTER TABLE testpub_gencol REPLICA IDENTITY USING index testpub_gencol_idx;
CREATE PUBLICATION pub_gencol FOR TABLE testpub_gencol;
UPDATE testpub_gencol SET a = 100 WHERE a = 1;

Here the generated column 'b' is set as REPLICA IDENTITY for table
'testpub_gencol'. When we create publication 'pub_gencol' we do not
specify any column list, so column 'b' will not be published.
So, the update message generated by the last UPDATE would have NULL
for column 'b'.

To avoid the issue, we can disallow UPDATE/DELETE on table with
unpublished generated column as REPLICA IDENTITY. I have attached a
patch for the same.

[1]: https://www.postgresql.org/message-id/CAD21AoA_RBkMa-6nUpBSoEP9s%3D46r3oq15vQkunVRCsYKXKMnA%40mail.gmail.com

Thanks and regards,
Shlok Kyal

Attachment Content-Type Size
v1-0001-Disallow-UPDATE-DELETE-on-table-with-generated-co.patch application/octet-stream 4.7 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Langote 2024-11-05 07:55:25 Re: Wrong result when enable_partitionwise_join is on if collation of PartitionKey and Column is different.
Previous Message jian he 2024-11-05 07:12:50 Re: general purpose array_sort