Re: Proposal: Conflict log history table for Logical Replication

On Fri, Aug 7, 2026 at 7:11 AM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> On Fri, Aug 7, 2026 at 6:50 PM saurabh singh <saurabh(dot)singh214(at)gmail(dot)com> wrote:
> >
> > On Fri, Aug 7, 2026 at 12:08 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > >
> > > 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.
> > >
> >
> > Please find below the details on Oracle GoldenGate's default conflict
> > handling behavior:
> >
> > Default Behavior:
> >
> > Oracle GoldenGate attempts conflict resolution based on the
> > user-defined conditions provided.
> > If the conflict is successfully resolved, the record details are
> > updated in the exception table for future reference.
> >
> > On Resolution Failure:
> >
> > If the conflict cannot be resolved, the OGG Replicat process abends,
> > and the error message is written to the log file for troubleshooting.
> > OGG does not write to the exception table in this case, as doing so
> > would create duplicate records for the same conflict on every restart.
> > Additionally, since a transaction may involve multiple tables and
> > multiple conflicts, this can introduce further complications.
> >
> > Customization:
> >
> > This is the default behavior. However, Oracle provides certain
> > parameters that allow users to modify this behavior if needed. How to
> > handle errors is ultimately up to the user, based on their specific
> > requirements, since Oracle has made this provision available.
> >
> > Below is an example of both default and customization behaviour
> >
> > 1. Default Behaviour.
> >
> > 1.If an INSERT operation causes a unique constraint violation on the
> > target database, the GoldenGate Replicat process encounters ORA-00001:
> > unique constraint and it gets abended. Same can be viewed in
> > ggserr.log
> >
> > 2026-08-07T03:39:36.566-0700 WARNING OGG-01919 Oracle GoldenGate
> > Delivery for Oracle, M01SR.prm.backup: Missing RESOLVECONFLICT for
> > SQL error 1.
> > 2026-08-07T03:39:36.566-0700 WARNING OGG-01004 Oracle GoldenGate
> > Delivery for Oracle, M01SR.prm.backup: Canceled grouped transaction
> > on table OGG26AI_OWNER.DDL_TEST010422. Database error 1, (OCI Error
> > ORA-00001: unique constraint (OGG26AI_OWNER.UNIQ_ID_UK) violated
> > 2026-08-07T03:39:41.256-0700 ERROR OGG-01668 Oracle GoldenGate
> > Delivery for Oracle, M01SR.prm: PROCESS ABENDING.
> >
> > OGG PROMPT > info M01SR
> >
> > Program Status Group Type Lag at
> > Chkpt Time Since Chkpt
> > REPLICAT ABENDED M01SR PARALLEL INT 00:49:43
> > 00:00:43
> >
> > 2. Replicat neither updates any error message or details in the
> > Exception table. But it writes to ggserr.log Logfile about error
> > details.
> >
> > SQL> select ID,ERRNO,COMMITTIMESTAMP,DBERRMSG from
> > OGGADMIN.DDL_TEST010422_3295583549_E; <<--- Exception Table
> > no rows selected
> >
> > 2. Customization Behaviour.
> >
> > 1. When we added oracle provided customization and added REPERROR (-1,
> > EXCEPTION) in the parameter file.
> > 2. If an INSERT operation causes a unique constraint violation on the
> > target database, the GoldenGate Replicat process encounters ORA-00001:
> > unique constraint (OGG26AI_OWNER.UNIQ_ID_UK) violated, but this time
> > it writes the conflict transaction in exception table and proceed
> > further.
> >
> >
> > SQL> select ID,ERRNO,COMMITTIMESTAMP,DBERRMSG from
> > OGGADMIN.DDL_TEST010422_3295583549_E; <<--- Exception Table
> >
> > ID ERRNO COMMITTIMESTAMP DBERRMSG
> > ---------- ---------- -----------------------------------
> > ------------------------------------------------------------------------------------------------------------------------------------------------------
> > 100 1 07-AUG-26 03.37.13.025896 AM OCI Error ORA-00001: unique
> > constraint (OGG26AI_OWNER.UNIQ_ID_UK) violated
> >
> > Help: https://docs.oracle.com/error-help/db/ora-00001/ (status = 1),
> > SQL <INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO
> > "OGG26AI_OWNER"."DDL_TEST010422" ("
> >
> > ID","KEY","VALUE","STATUS","CREATED","LASTMODIFIED","UNIQ_ID") VALUES
> > (:a0,:a1,:a2,:a3,:a4,:a5,:a6)>
>
> Thanks for the testing this, Saurabh.
>
> As Saurabh demonstrated with this example, when Oracle GoldenGate
> encounters an unresolvable conflict, raising an error halts
> replication and aborts the transaction without logging to an exception
> table. We see this exact same behavior in other replication engines
> like pgactive and pgedge-spock. Additionally, there are two primary
> issues with trying to log a conflict to a database table when a
> process-halting error is raised:
>
> 1) Every time the replication worker restarts and hits the same
> unresolved conflict, it would insert a new record. Even if we attempt
> to update an existing record, the constant insert/update cycles on a
> failing transaction loop will cause severe table bloat and place
> unnecessary overhead on autovacuum. Even if we think such unhandled
> conflict loops might be rare, in extreme cases or under high-frequency
> restart attempts, this behavior could add a significant, load on a
> production database.

This looks like a separate problem to me. Users would have to take
care of CLT sizes anyway. I think that this issue should be resolved
by having a retention policy in the core or users setting a cron job
to clean old CLT logs etc.

>
> 2) Attempting to write a conflict log for an aborted transaction would
> require a complex error-handling mechanism.
>
> In summary, the Conflict Logging Table should focus primarily on cases
> where the system makes an active, automated decision to proceed with
> replication (such as skipping or overwriting). These are the actions
> that can cause silent data divergence. An ERROR resolver, on the other
> hand, results in an aborted transaction where replication halts and no
> data change is applied. Because there is no silent data divergence in
> this state, standard server logs and monitoring are fully sufficient.

There are two possible definitions we could give to CLT:

1. it stores how replication conflicts were resolved.
2. it stores which replication conflicts caused data divergences (and
how they were resolved).

My interpretation had been (1). The option is named
conflict_log_destination, which reads as choosing where the same
information goes, and the server log carries every conflict including
the conflicts resolved by the ERROR resolver. So I expected the table
to have the same coverage.

That said, I can accept (2), and I agree it is what other replication
solutions do. If we go that way, though, I would like the CLT to have
a 'resolution' column, showing which resolution method was applied,
even though the resolvers are hard-wired per conflict type today. With
that column, a user can see both what conflict happened and how it was
handled by looking at the CLT alone.

Users would still have to read the server log for conflicts handled by
the ERROR resolver, and I think it would be good to somehow make that
information queryable some day.

>
> Furthermore, if ALTER SUBSCRIPTION ... SKIP needs to retrieve the LSN
> via a SQL query to skip the transaction, we could instead look into
> expanding pg_stat_subscription or pg_stat_subscription_stats. As Amit
> pointed out, exposing fields like last_error_code, last_error_message,
> last_error_lsn, and last_error_time would be a much cleaner way to
> support programmatic error inspection.

I don't think pg_stat_subscription_stats is the right place to have
such information. pgstats always loses state during crash restarts,
and the state can be reset. We used to implement the same idea but it
was changed (see commit 7a85073290856).

Regards,

[1] https://www.postgresql.org/message-id/20220125063131.4cmvsxbz2tdg6g65@alap3.anarazel.de

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