Horton

Disabled logical replication origin session causes primary key errors

Shawn McCoy <shawn.the.mccoy@gmail.com>

View thread
From: Shawn McCoy <shawn.the.mccoy@gmail.com>
To: pgsql-bugs@lists.postgresql.org
Cc: swada.mshk@gmail.com, drewwcallahan@gmail.com, "scott@meads.us" <scott@meads.us>
Date: 2025-04-18T21:22:00Z
Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix an oversight in 3f28b2fcac.

  2. Don't advance origin during apply failure.

Attachments

Hello,

We have discovered a recent regression in the Origin handling of logical
replication apply workers. We have found the cause of the issue was due to
the worker resetting its local origin session information during the
processing of an error that is
silently handled allowing the worker to continue. We suspect this is caused
by the recent change made in the following thread,
https://www.postgresql.org/message-id/TYAPR01MB5692FAC23BE40C69DA8ED4AFF5B92@TYAPR01MB5692.jpnprd01.prod.outlook.com
.

The logical replication apply worker will originally setup the origin
correctly. However, on the first insert will call into the trigger which
will raise an exception. This exception will execute the error callback
that resets the origin session state. The exception will then be silently
handled, returning execution back to the apply worker.  In the second
reproduction, a function based index is used with the same result.

At this point, the apply worker can continue to commit these changes, but
has cleared all local origin session state. As a result, we will not update
our remote to local LSN mapping of the origin. Allowing for duplicate data
to be applied.

This was tested and observed in at least these versions:

PostgreSQL 16.8
PostgreSQL 17.4

We provide a simple reproduction of the issue below in 2 separate use-cases.

Regards,
Shawn McCoy, Drew Callahan, Scott Mead