Re: Proposal: Conflict log history table for Logical Replication

On Fri, Jun 26, 2026 at 2:55 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> On Fri, 26 Jun 2026 at 12:44, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >
> > On Fri, Jun 26, 2026 at 11:25 AM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> > >
> > > On Thu, 25 Jun 2026 at 19:15, Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> > > >
> > > > PFA, updated version of the patch, fixes all the comments of V57
> > >
> > > Currently, I have a use case where I would like to clean up conflict
> > > log records using a 'MERGE' statement.
> > >
> > > Consider a system that has accumulated conflict records over several
> > > days or weeks. Some of those conflicts have already been resolved, and
> > > in the meantime, a number of tables have been removed from the
> > > publication, dropped on the subscriber, or are otherwise no longer
> > > part of logical replication. However, the corresponding conflict log
> > > records remain in the conflict log table.
> > >
> > > One way to clean up such stale entries is to compare the 'relid'
> > > stored in the conflict log table with 'pg_class' and delete rows whose
> > > relations no longer exist. For example:
> > > MERGE INTO pg_conflict.pg_conflict_log_16404 AS cl
> > > USING pg_class AS c
> > > ON (cl.relid = c.oid)
> > > WHEN NOT MATCHED BY SOURCE THEN
> > > DELETE;
> > > However, this fails with:
> > > ERROR: cannot modify or insert data into conflict log table
> > > "pg_conflict_log_16404"
> > > DETAIL: Conflict log tables are system-managed and only support
> > > cleanup using DELETE or TRUNCATE.
> > >
> > > Although this 'MERGE' only performs a 'DELETE', it is still rejected.
> > >
> > > Is this restriction intentional? I can understand if supporting
> > > 'MERGE' is considered out of scope for this patch, but I wanted to
> > > check whether rejecting a delete-only 'MERGE' is an intentional design
> > > decision. If so, perhaps support for this use case could be considered
> > > in a future version.
> > >
> >
> > Yes, in future versions, we may want to consider such cases. BTW,
> > can't we achieve this simply via DELETE?
>
> Yes, this particular example can be achieved using 'DELETE ... WHERE
> NOT EXISTS':
> DELETE FROM pg_conflict.pg_conflict_log_16404 cl
> WHERE NOT EXISTS (
> SELECT 1
> FROM pg_class c
> WHERE c.oid = cl.relid
> );
>
> My question was mainly whether rejecting a delete-only 'MERGE' is an
> intentional design decision. Although users can always rewrite such
> statements using 'DELETE', those who are accustomed to using 'MERGE'
> for cleanup operations would have to fall back to 'DELETE', even when
> the 'MERGE' can only perform a 'DELETE'. I just wanted to understand
> whether this restriction is intentional or simply a consequence of
> rejecting all 'MERGE' operations.

We should focus strictly on core use cases for the Conflict Log Table
(CLT). Users simply need to view conflicts via SELECT, clear old
records using DELETE, and wipe the table using TRUNCATE to prevent
storage bloat. No other table-level features are necessary. At this
point, I don't think we need to enable features for all use cases,
such as MERGE. At this point, we should prioritize a robust table
schema design to ensure long-term stability and compatibility across
future versions. Since these tables will not be heavily utilized in
the initial release, we do not need to support exhaustive
functionality immediately; we can easily expose additional operations
later as demand arises. Thoughts?

--
Regards,
Dilip Kumar
Google