Re: Proposal: Conflict log history table for Logical Replication

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.

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.

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.

Given these challenges, I don't think we should introduce complex
error-handling machinery, risk autovacuum overhead, or generate
duplicate tuples.

Let me know what you think.

--
Regards,
Dilip Kumar
Google