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: Aleksander Alekseev <aleksander@timescale.com>, Peter Eisentraut <peter@eisentraut.org>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>, Shlok Kyal <shlok.kyal.oss@gmail.com>
Date: 2024-11-07T10:31:26Z
Lists: pgsql-hackers 
On Thu, Nov 7, 2024 at 11:04 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Wed, Nov 6, 2024 at 5:48 PM Aleksander Alekseev
> <aleksander@timescale.com> wrote:
>
> We should fix this in the HEAD and back branches.
>

BTW, I was thinking as to how to fix it on back branches and it seems
we should restrict to define REPLICA IDENTITY on stored generated
columns in the first place in back branches as those can't be
replicated. So, the following should fail:

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;

Peter, do you have an opinion on this?

[1] - https://www.postgresql.org/docs/devel/ddl-generated-columns.html

-- 
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.