Horton

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

Amit Kapila <amit.kapila16@gmail.com>

View thread View on postgr.es
From: Amit Kapila <amit.kapila16@gmail.com>
To: Shlok Kyal <shlok.kyal.oss@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2024-11-07T06:07:36Z
Lists: pgsql-hackers 
On Tue, Nov 5, 2024 at 12:53 PM Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
>
> 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.
>

+CREATE PUBLICATION pub_gencol FOR TABLE testpub_gencol;
+UPDATE testpub_gencol SET a = 100 WHERE a = 1;
+ERROR:  cannot update table "testpub_gencol"
+DETAIL:  Column list used by the publication does not cover the
replica identity.

This is not a correct ERROR message as the publication doesn't have
any column list associated with it. You have added the code to detect
this in the column list code path which I think is not required. BTW,
you also need to consider the latest commit 7054186c4e for this. I
guess you need to keep another flag in PublicationDesc to detect this
and then give an appropriate ERROR.

-- 
With Regards,
Amit Kapila.

Commits

  1. Improve the error message introduced in commit 87ce27de696.

  2. Ensure stored generated columns must be published when required.

  3. Replicate generated columns when 'publish_generated_columns' is set.

  4. Ignore generated columns during apply of update/delete.

  5. Ignore dropped columns during apply of update/delete.