Re: Question about behavior of deletes with REPLICA IDENTITY NOTHING

On Thu, Feb 8, 2024 at 4:47 AM Ashutosh Bapat
<ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
>
> On Thu, Feb 8, 2024 at 9:57 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
> >
> > On Thu, 2024-02-08 at 13:40 +1100, Peter Smith wrote:
> > > - how to set the replica identity. If a table without a replica identity is
> > > + how to set the replica identity. If a table without a replica identity
> > > + (or with replica identity behavior the same as <literal>NOTHING</literal>) is
> > > added to a publication that replicates <command>UPDATE</command>
> > > or <command>DELETE</command> operations then
> > > subsequent <command>UPDATE</command> or <command>DELETE</command>
> >
> > I had the impression that the root of the confusion was the perceived difference
> > between "REPLICA IDENTITY NOTHING" and "no replica identity", and that change
> > doesn't improve that.
> >
> > How about:
> >
> > If a table without a replica identity (explicitly set to <literal>NOTHING</literal>,
> > or set to a primary key or index that doesn't exist) is added ...
>
> Another possibility is just to improve the documentation of various
> options as follows.
>
> DEFAULT
>
> If there is a primary key, record the old values of the columns of the
> primary key. Otherwise it acts as NOTHING. This is the default for
> non-system tables.
>
> USING INDEX index_name
>
> Records the old values of the columns covered by the named index, that
> must be unique, not partial, not deferrable, and include only columns
> marked NOT NULL. If this index is dropped, the behavior is the same as
> NOTHING.
>
> FULL
>
> Records the old values of all columns in the row.
>
> NOTHING
>
> Records no information about the old row. This is equivalent to having
> no replica identity. This is the default for system tables.

This is the simplest change, and it does solve the confusion, so I'd
be happy with it also. The other proposals have the benefit of having
all the information necessary on the publications page rather than
requiring the user to refer to the ALTER TABLE REPLICA IDENTITY page
to understand what's meant.

Regards,
James Coleman