Re: Question about behavior of deletes with REPLICA IDENTITY NOTHING

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.

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.

Robert Treat
https://xzilla.net