Re: Proposal: Conflict log history table for Logical Replication

On Thu, Aug 6, 2026 at 4:38 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> 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?

I completely agree with this analysis, an audit history table should
track actions taken, not process stoppages. So we can define these
rules and document them clearly:

1) When an ERROR occurs, replication halts and the transaction aborts.
Because no data change is committed, no resolution action is actually
taken. Storing this in an audit table doesn't fit the definition of a
"history log" because no completed state exists to audit.
2) It only makes sense to log conflicts in the "conflict history
table" when we automatically resolve the conflict. If the system can
automatically resolve a conflict (e.g., applying a rule and
continuing), logging that specific action is crucial for DBAs to
understand why and how data changed.

So +1 to your analysis.

--
Regards,
Dilip Kumar
Google