Re: Proposal: Conflict log history table for Logical Replication

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?

Regards,
Vignesh