Re: Question about behavior of deletes with REPLICA IDENTITY NOTHING

On Thu, Feb 8, 2024 at 7:24 PM James Coleman <jtc331@gmail.com> wrote:
>
> On Thu, Feb 8, 2024 at 4:47 AM Ashutosh Bapat
> <ashutosh.bapat.oss@gmail.com> wrote:
> >
> > 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.
>
> 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.
>

There is no harm in having it at both places (publications page and
ALTER TABLE REPLICA IDENTITY page). Would someone be interested in
preparing a patch with the changes agreed upon?

--
With Regards,
Amit Kapila.