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

On Mon, 14 Apr 2025 at 05:40, Ashutosh Bapat
<ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
>
> 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.

Yes, agreed. The error messages seem clear enough to me.

> 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.

I don't know if it was discussed at the time, but to me the way it has
been done makes sense, because replication is happening at a lower
level -- individual row INSERTs, UPDATEs and DELETEs using rows
identified by the replica identity key. If you tried to do it at the
level of MERGE, I think it would be difficult to guarantee the same
results on the replica.

This makes me wonder though, does INSERT ... ON CONFLICT DO UPDATE
have the same problem as MERGE?

If I create a table and publication like this:

CREATE TABLE foo (a int PRIMARY KEY, b text, c int);
INSERT INTO foo VALUES (1, 'xxx', 10);

CREATE PUBLICATION pub1 FOR TABLE foo WHERE (c > 0);

plain UPDATEs are rejected:

UPDATE foo SET b = 'yyy' WHERE a = 1;

ERROR: cannot update table "foo"
DETAIL: Column used in the publication WHERE expression is not part
of the replica identity.

However, INSERT ... ON CONFLICT DO UPDATE can be used to achieve the same thing:

INSERT INTO foo VALUES (1)
ON CONFLICT (a) DO UPDATE SET b = 'yyy';

This isn't rejected, and it replicates the change to the subscriber
correctly, which makes me wonder, why do we need this restriction on
filter columns? If there is some other situation where it's needed,
should INSERT ... ON CONFLICT DO UPDATE also check it?

Regards,
Dean