Re: Proposal: Conflict log history table for Logical Replication

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)>

Regards,
Saurabh