Re: Fix replica identity checks for MERGE command on published table.

On Fri, Apr 11, 2025 at 10:54 AM Zhijie Hou (Fujitsu)
<houzj.fnst@fujitsu.com> wrote:
>
> Hi,
>
> While testing publication DDLs, I noticed an unexpected behavior where the
> MERGE command can be executed on tables lacking replica identity keys,
> regardless of whether they are part of a publication that publishes updates and
> deletes.
>
> Replication and application of the updates and deletes generated by MERGE
> command require replica identity keys in the WAL record, which are essential
> for the apply worker on the subscriber to find local tuples for updating or
> deletion. Furthermore, publications require specific columns to be part of the
> replica identity key if the table specifies a publication row filter or column
> list.
>
> We already have restrictions on executing UPDATE and DELETE commands for tables
> without replica identity keys under similar conditions. So, I think the same
> restriction should apply to the MERGE command as well.
>

+-- fail - missing REPLICA IDENTITY
+MERGE INTO testpub_merge_no_ri USING testpub_merge_pk s ON s.a >= 1
+ WHEN MATCHED THEN UPDATE SET b = s.b;
+ERROR:  cannot update table "testpub_merge_no_ri" because it does not
have a replica identity and publishes updates
+HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
+-- fail - missing REPLICA IDENTITY
+MERGE INTO testpub_merge_no_ri USING testpub_merge_pk s ON s.a >= 1
+ WHEN MATCHED THEN DELETE;
+ERROR:  cannot delete from table "testpub_merge_no_ri" because it
does not have a replica identity and publishes deletes
+HINT:  To enable deleting from the table, set REPLICA IDENTITY using
ALTER TABLE.

I was wondering whether we should mention MERGE somewhere in the error
message like "cannot merge into table ...". But the error message is
reported depending upon the actual operation being performed and
whether it's being published by the publication, so mentioning
specific operations is better than mentioning just MERGE. So I think
the current error message is ok; and it will help point out the
operations that caused it.

But that opens up another question: some merge operations (on the same
table) will go through and some won't if the publication publishes
only some of the operations. I am wondering, albeit quite late after
the feature has sailed, whether MERGE should be considered a separate
operation as far as publication is concerned. This topic may have been
discussed either when MERGE was implemented or when operation filters
were implemented. Sorry for the noise in that case.

This isn't a detailed review.

--
Best Wishes,
Ashutosh Bapat