Re: Question about behavior of deletes with REPLICA IDENTITY NOTHING

On Mon, Jan 13, 2025 at 10:22 AM Robert Treat <rob(at)xzilla(dot)net> wrote:
>
> On Sun, Jan 12, 2025 at 11:00 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >
> > On Sat, Jan 11, 2025 at 7:28 PM Robert Treat <rob(at)xzilla(dot)net> wrote:
> > >
> > > Definitely couldn't hurt; Updated patch cleans that up a bit and
> > > tweaks the link to alter table replica status.
> > >
> >
> > IIUC, we have changed following to clarify the REPLICA IDENTITY usage:
> > If a table without a replica identity is
> > - added to a publication that replicates <command>UPDATE</command>
> > - or <command>DELETE</command> operations then
> > - subsequent <command>UPDATE</command> or <command>DELETE</command>
> > - operations will cause an error on the publisher. <command>INSERT</command>
> > - operations can proceed regardless of any replica identity.
> >
> > + If a table with replica identity set to <literal>NOTHING</literal>
> > + (or set <command>DEFAULT</command> but with no primary key, or set
> > + <command>USING INDEX</command> but the index has been dropped) is
> > + added to a publication that replicates <command>UPDATE</command>
> > + or <command>DELETE</command> operations,
> > + subsequent <command>UPDATE</command> or <command>DELETE</command>
> > + operations will cause an error on the publisher.
> >
> > In the above change, we missed the existing "a table without a replica
> > identity" part. A slightly different way to write the above change
> > could be: "Tables lacking a replica identity or with an insufficiently
> > defined replica identity (e.g., set to NOTHING, set to DEFAULT but
> > with no primary key, or set USING INDEX but the index has been
> > dropped) cannot be updated or deleted when added to a publication that
> > replicates these operations. Attempting to do so will result in an
> > error on the publisher."
> >
>
> We didn't miss it, we removed it. It is a misnomer to say a table
> doesn't have a replica identity, because all tables do and always must
> have one, hence pg_class.relreplident is NOT NULL. In most cases it is
> set DEFAULT and people don't think about it, but it isn't due to a
> lack of or insufficient replica identity, and I think that language is
> part of what confuses people.
>

Okay, I got it.

> Aside from that, your above language is a little more compact with the
> trade-off of being less explicit in talking about publication
> properties; I didn't change that part because it didn't seem like an
> issue, but we could update that second part if you feel strongly about
> it. LMK.
>

One of the reasons I tried to rephrase the sentence was it appears to
be long. I agree that the way you proposed is more explicit but the
way I phrased also conveys the information in a bit succinct form. I
think you can once propose with the wording on those lines then let us
what Peter or others think about it.

--
With Regards,
Amit Kapila.