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

On Wed, Nov 6, 2024 at 5:48 PM Aleksander Alekseev
<aleksander(at)timescale(dot)com> wrote:
>
> > So, I think this behavior would be acceptable. Thoughts?
>
> That's a fair point, thanks for sharing. Personally I find this
> behavior somewhat suboptimal but since we already have it in certain
> cases I guess what you propose might be acceptable.
>

This is not a suboptimal behavior but a must to have, otherwise, there
is no way we can identify the row to update on the subscriber side.
Also, this is not in certain cases but in all cases for UPDATE/DELETE,
we need REPLICA IDENTITY to be set. See more about REPLICA IDENTITY in
Alter Table docs [1]. The problem reported by Shlok is that even
though we have a REPLICA IDENTITY defined on a generated column but
still won't send the required column value (as generated columns are
skipped by default) to the subscriber which will lead to ERROR as
mentioned below. Now, one can argue that this is not expected from the
user or why the user would have such a setup but I think we should fix
the problem if it leads to unexpected behavior on the subscriber.

> I'm still not entirely happy about breaking the existing behavior in
> the discussed case. Not sure what the lesser evil would be - breaking
> it or keeping it as is.
>

The current behavior is not acceptable because it would generate an
ERROR as follows on the subscriber:

2024-11-07 10:50:31.381 IST [16260] ERROR: publisher did not send
replica identity column expected by the logical replication target
relation "public.testpub_gencol"
2024-11-07 10:50:31.381 IST [16260] CONTEXT: processing remote data
for replication origin "pg_16389" during message type "UPDATE" for
replication target relation "public.testpub_gencol" in transaction
748, finished at 0/176D5D8
2024-11-07 10:50:31.398 IST [6216] LOG: background worker "logical
replication apply worker" (PID 16260) exited with exit code 1

> Some input from other people on the mailing list would be appreciated.
>

We should fix this in the HEAD and back branches.

[1] - https://www.postgresql.org/docs/devel/sql-altertable.html

--
With Regards,
Amit Kapila.