Re: Proposal: Conflict log history table for Logical Replication

On Fri, Jun 26, 2026 at 3:13 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> 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?
>

I agree.

thanks
Shveta