Re: Question about behavior of deletes with REPLICA IDENTITY NOTHING - Mailing list pgsql-hackers

On Sat, Jan 11, 2025 at 7:28 PM Robert Treat <rob@xzilla.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."

--
With Regards,
Amit Kapila.