On Mon, 7 Jul 2025 at 18:17, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>
> This makes me wonder though, does INSERT ... ON CONFLICT DO UPDATE
> have the same problem as MERGE?
>
Answering my own question, INSERT ... ON CONFLICT DO UPDATE does have
the same problem as MERGE. To reproduce the error, all you need to do
is create the unique index it needs *after* creating the publication,
for example:
CREATE TABLE foo (a int, b text);
INSERT INTO foo VALUES (1, 'xxx');
CREATE PUBLICATION pub1 FOR TABLE foo;
CREATE UNIQUE INDEX foo_a_idx ON foo(a);
Then a plain UPDATE is blocked:
UPDATE foo SET b = 'yyy' WHERE a = 1;
ERROR: cannot update table "foo" because it does not have a replica
identity and publishes updates
HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
but the equivalent with INSERT ... ON CONFLICT DO UPDATE is allowed on
the publisher:
INSERT INTO foo VALUES (1)
ON CONFLICT (a) DO UPDATE SET b = 'yyy';
but fails on the subscriber:
ERROR: logical replication target relation "public.foo" has neither
REPLICA IDENTITY index nor PRIMARY KEY and published relation does not
have REPLICA IDENTITY FULL
CONTEXT: processing remote data for replication origin "pg_16535"
during message type "UPDATE" for replication target relation
"public.foo" in transaction 872, finished at 0/01E65718
So INSERT ... ON CONFLICT DO UPDATE needs to check that the result
relation is valid for UPDATEs.
Regards,
Dean
