Re: Proposal: Conflict log history table for Logical Replication

On Mon, Jun 15, 2026 at 10:46 AM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> On Sat, 13 Jun 2026 at 15:46, Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> >
> > On Thu, Jun 11, 2026 at 5:53 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> > >
> > > On Thu, 11 Jun 2026 at 10:44, Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> > > >
> > > > Please find the rebased patch
> > > > 1. It includes the new 0005 patch for reporting errors for DDLs on clt.
> > > >
> > > > Open comments:
> > > > 1. Recent comments from Nisha and Shveta after v47 are still open
> > > > 2. Vignesh's patch for "describe related" changes needs a rebase. Can
> > > > you do that, Vignesh? Meanwhile, I will close all the open comments
> > > > and try to share a new version by EOD today.
> > >
> > > Here is the rebased version of the patch attached.
> >
> > Please find attached the latest patch. I have reordered the series,
> > moving 0006 to 0002, and updated the lock restrictions. We now allow
> > ACCESS SHARE mode exclusively to ensure pg_dump can acquire its
> > necessary locks, while blocking higher-level locks to prevent
> > interference with insertions into the conflict log tables.
>
> I noticed that declaring a cursor with 'FOR UPDATE' on a conflict log
> table currently fails:
> postgres=*# DECLARE cur1 CURSOR FOR
> SELECT *
> FROM pg_conflict.pg_conflict_log_16404
> WHERE relid = 16402
> FOR UPDATE;
> ERROR: cannot lock rows in the conflict log table "pg_conflict_log_16404"
>
> I'm not sure whether this restriction is intentional.
>
> One benefit of supporting 'FOR UPDATE' cursors is that they provide
> protection against concurrent modifications. For example:
> **Session 1**
> BEGIN;
> DECLARE cur1 CURSOR FOR SELECT * FROM t3 WHERE c1 < 100 FOR UPDATE;
> FETCH NEXT FROM cur1;
>
> **Session 2**
> DELETE FROM t3 WHERE c1 < 100;
>
> In this case, the 'DELETE' in Session 2 will wait until Session 1
> releases the row locks, preventing concurrent modification of the rows
> being processed.
>
> This can be useful for workflows that need to archive rows before
> deleting them. For example:
> DO $$
> DECLARE
> r t3%ROWTYPE;
> cur1 CURSOR FOR SELECT * FROM t3 WHERE c1 < 100 FOR UPDATE;
> BEGIN
> OPEN cur1
> LOOP
> FETCH cur1 INTO r;
> EXIT WHEN NOT FOUND;
>
> INSERT INTO t3_archive VALUES (r.c1);
>
> DELETE FROM t3 WHERE CURRENT OF cur1;
> END LOOP;
>
> CLOSE cur1;
> END $$;
>
> Given these use cases, should 'FOR UPDATE' be allowed on conflict log
> tables, or is the current behavior intentional for some reason that
> I'm overlooking?
>

As told in the previous email, we are planning to allow only a minimal
set of commands initially that are necessary for observability and
maintainability of CLT tables. I think such use cases could be
achieved by LOCK command or the user needs to be careful not to
remove/truncate data from these tables till the same is required.

--
With Regards,
Amit Kapila.