Re: Proposal: Conflict log history table for Logical Replication

On Thu, Aug 6, 2026 at 4:19 AM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> 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.

Under this proposal the CLT would record a strict subset of the
conflicts that already go to the server log. It does keep more detail
per conflict, since the log truncates column values to 64 bytes and
omits column names, and it is structured and queryable. But I'm not
sure that is enough on its own to justify a system-managed
per-subscription table. We cannot use it to get commit_lsn even for
ALTER SUBSCRIPTION .. SKIP.

Also, I'm not convinced that the distinction between LOG-level and
ERROR-level conflicts is the right line to draw. My understanding is
that we already have a small set of implicit resolution methods, and
that each conflict type is hard-wired to one of them. For instance,
update_missing effectively uses 'discard' or 'ignore', and
update_origin_differs effectively uses 'overwrite'; the documentation
says the update "will simply be skipped" in the former case and "is
always applied" in the latter. insert_exists can likewise be seen as
using a resolution method that raises an error and waits for manual
resolution. Once conflict resolution becomes configurable, I would
expect users to want (for example) update_origin_differs to raise an
ERROR rather than silently overwrite a locally-modified row. Under
this proposal that conflict would then disappear from the CLT, even
though the same conflict is recorded there today. I think users would
expect both what conflict happened and how it was handled to be
recorded, regardless of which resolution method they chose.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com