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?

IIUC maintaining the conflict log table is the user's responsibility.
Therefore, we don't need to overengineer use cases where a user
concurrently backs up and deletes the table. Furthermore, since only
the subscription owner can delete data from it, it is ultimately their
responsibility to ensure they do not delete rows they intend to copy.

--
Regards,
Dilip Kumar
Google