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
v1-0001-Disallow-UPDATE-DELETE-on-table-with-generated-co.patch
Description: Binary data
