Re: Question about behavior of deletes with REPLICA IDENTITY NOTHING

On Thu, Feb 8, 2024 at 9:57 AM Laurenz Albe <laurenz.albe@cybertec.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.


--
Best Wishes,
Ashutosh Bapat