Re: Disallow UPDATE/DELETE on table with unpublished generated column as REPLICA IDENTITY - Mailing list pgsql-hackers

On Sat, Nov 9, 2024 at 8:46 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Fri, Nov 8, 2024 at 5:17 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> >
> > On 2024-Nov-07, Amit Kapila wrote:
> >
> > > 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?
> >
> > I think a blanket restriction of this sort is not a good idea (at least
> > in back branches), because there might be people using replica
> > identities with stacks other than pgoutput.
> >
>
> Do you mean to say that people using plugins other than pgoutput may
> already be sending generated columns, so defining replica identity
> should be okay for them?
>

If we don't want to add a restriction to not create replica identity
on generated columns then I think the solution similar to HEAD should
be okay which is to restrict UPDATE/DELETE in such cases.

Also, another point against restricting defining REPLICA IDENTITY on
generated columns is that we do allow generated columns to be PRIMARY
KEY which is a DEFAULT for REPLICA IDENTITY, so that also needs to be
restricted. That won't be a good idea.

--
With Regards,
Amit Kapila.