Re: Proposal: Conflict log history table for Logical Replication

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.

Regards,
Vignesh