Updating Primary Keys - beware of consequences

Updating Primary Keys using logical replication may cause conflicts. This is a limitation of row change based logical replication. While some SQL operations are valid when done on a set of rows at once, they are not valid when broken down into separate change (which is what row based logical replication does)

So there are legal UPDATEs that cannot be converted to single-rows.

Example:

SQL>update SCOTT.EMP set EMPNO = 3 - EMPNO where EMPNO in (1,2);

This will be replicated as 2 single row-by-row operations:

1. UPDATE SCOTT.EMP set EMPNO = 1 where EMPNO = 2;
2. UPDATE SCOTT.EMP set EMPNO = 2 where EMPNO = 1;

 This is a valid statement when done as a set of rows on the source, but running the SQL as a single row operation will always violate PK and cause conflicts.

 There are several solutions:

1. Do not update the primary keys. Updating PK in this way violates relational design best practices.
2. Change the PK constraint to deferred.
3. When this happens (which should be very seldom), disable the PK constraint on the target to let the transaction pass and then enable it the PK constraint back again.

For more information please see How to update primary keys (PK) when using logical replication