Re: Proposal: Conflict log history table for Logical Replication

On Wed, Aug 5, 2026 at 4:59 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Wed, Aug 5, 2026 at 7:02 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> >
> >
> > My understanding is that the conflict log table is meant to record the
> > same conflicts we have been writing to the server log. But the two
> > destinations don't fail in the same way. The log message is emitted
> > immediately and is unaffected by the fate of the apply transaction,
> > whereas a LOG-level conflict row is inserted in the apply transaction
> > and is rolled back if that transaction later fails but the same isn't
> > true for ERROR-level conflicts. So with conflict_log_destination =
> > 'both', there are cases where a conflict appears in the server log but
> > not in the table, and never the other way around. That seems
> > surprising for an option whose purpose is to choose where the same
> > information goes. I'm not sure it's okay to discard many LOG-level
> > conflicts due to one ERROR-level conflict.
> >
>
> I think the way to handle this would be to defer LOG-level inserts
> too, accumulating them per-transaction and flushing them together with
> the ERROR tuple in a fresh transaction after abort (or normally at
> commit if no abort occurs). We can accumulate these conflict tuples in
> an in-memory list at apply-transaction level and after a certain
> threshold, we can spill the same way streaming apply already spills
> large transactions' changes to a BufFile/FileSet rather than assuming
> everything fits in memory. There could be some other ideas to handle
> it but this is what occurred to me. What do you think?
>

I've been thinking further about the idea of accumulating and spilling
LOG-level/ERROR-level conflict tuples to handle them in a fresh
transaction post-abort. While accumulating in memory and spilling to a
BufFile/FileSet (similar to streaming apply) is technically feasible,
I want to step back and question whether storing ERROR-level conflicts
in pg_conflict_history is desirable in the first place.

In my view, the primary purpose of pg_conflict_history should be to
maintain an audit trail for resolved conflicts (keep_remote,
keep_local, ignore, etc.) where the transaction successfully commits
and replication continues. In these cases, local data may silently
diverge from the publisher or be overwritten, making a SQL-accessible
audit log essential for DBAs to understand post-hoc why a row changed
or was skipped.

In contrast, ERROR-level conflicts should be excluded from
pg_conflict_history for several reasons:

No Data Divergence: An ERROR aborts the entire replication
transaction. Because no rows are inserted, updated, or deleted on the
subscriber, no data divergence occurs.
Transactional Consistency & Complexity: Recording an ERROR in a table
requires writing outside the main transaction (e.g., via autonomous
sub-transactions or post-abort out-of-band logging) because the main
transaction is rolling back. Introducing this level of infrastructure
in the apply worker adds significant architectural complexity to core
transaction management for marginal gain.
Observability Alignment: Operational stoppages belong in
error-reporting infrastructure rather than data audit tables. Server
logs capture the full trace and LSN, while dynamic views like
pg_stat_subscription_stats track cumulative failure counts. If SQL
query ability for active errors is needed, a better path would be
expanding pg_stat_subscription / pg_stat_subscription_stats to expose
fields like last_error_code, last_error_message, last_error_lsn, and
last_error_time.

This also appears to align with how other replication engines approach
the problem. Based on feedback from an Oracle GoldenGate practitioner
and my understanding of OGG mechanics [1], when process-halting errors
occur (REPERROR set to ABEND), they do not write to the database
Exception table (which is the equivalent of our pg_conflict_history).
Instead, because the database transaction rolls back, GoldenGate
writes the error out-of-band to file-system log files, whereas
Exception tables are used for errors caught and handled in-band where
replication continues.

Given this, keeping pg_conflict_history scoped strictly to committed
transactions (where the conflicts are handled) keeps the semantics
clean, avoids spilling/out-of-band transaction complexity, and cleanly
separates data auditing from operational error handling.

Thoughts?

[1]: https://docs.oracle.com/en/middleware/goldengate/core/21.3/admin/handling-processing-errors.html#GUID-EB044AF7-BE35-419F-A285-193D6091F46D

--
With Regards,
Amit Kapila.