Re: Proposal: Conflict log history table for Logical Replication

From: Nisha Moond <nisha(dot)moond412(at)gmail(dot)com>
To: Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
Cc: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>, shveta malik <shveta(dot)malik(at)gmail(dot)com>, shihao zhong <zhong950419(at)gmail(dot)com>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>, vignesh C <vignesh21(at)gmail(dot)com>, saurabh singh <saurabh(dot)singh214(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Peter Smith <smithpb2250(at)gmail(dot)com>, Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, shveta malik <shvetamalik(at)gmail(dot)com>
Subject: Re: Proposal: Conflict log history table for Logical Replication
Date: 2026-11-18 07:18:43
Message-ID: CABdArM5=0t3PvvfpU7C7rXXemwQPZxZac2TdvjbdvMFaiXo=xg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Tue, Nov 10, 2026 at 8:27 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Mon, Nov 9, 2026 at 11:00 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >
> > On Mon, Nov 9, 2026 at 10:17 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> > >
> > > On Tue, Nov 3, 2026 at 2:31 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > > >
> > > >
> > > > Yes, I also thought about it previously and felt the same. To achieve
> > > > this we need to scan the lock table for holders on the relation and
> > > > match them to the subscription's prepared transactions (their dummy
> > > > PGPROCs). See attached top-up atop v88 generated by AI. I can
> > > > review/test it more if we decide to go with this strategy. I still
> > > > feel we shouldn't add this because such scenarios will be rare in
> > > > practice. It won't be difficult to address this if we really see such
> > > > field reports, so I would like to leave this code as is but if you
> > > > still think otherwise then we can take care with finding whether
> > > > prepared xact has locked CLT.
> > >
> > > I'm not sure it's that rare. Tuples copied by the initial table sync
> > > always cause a conflict (update_origin_differs or
> > > delete_origin_differs) when the apply worker modifies them for the
> > > first time, if track_commit_timestamp is enabled.
>
> As discussed in thread [1] that is a caveat we should fix. When/If we
> fix those cases then this shouldn't be a cause of worry and I think we
> need to do something about it before we think of resolutions.
>
> > > Also, in systems
> > > using two-phase commit, it's common that the subscription has prepared
> > > transactions. But I might be worrying too much.
> > >
>
> I am also not very clear how much to worry about this case.
>
> > > TBH I'm still not sure why we really need the special behavior of
> > > erroring out only for the CLT. I agree that it's an internal behavior
> > > and users might not be aware of it, but I think users who don't know
> > > it would be equally confused when the apply worker errors out because
> > > of it.
> > >
> >
> > I see your point. So, what should we do instead? We can remove this
> > extra handling for prepared xact from apply_worker and document it.
> > Prepare Transaction already notes as follows: "PREPARE TRANSACTION is
> > not intended for use in applications or interactive sessions. Its
> > purpose is to allow an external transaction manager to perform atomic
> > global transactions across multiple databases or other transactional
> > resources. Unless you're writing a transaction manager, you probably
> > shouldn't be using PREPARE TRANSACTION.". Then there is a long Caution
> > Note [1], shall we document the additional hazards with respect to
> > apply worker both for normal and CLT tables?
> >
>
> We have discussed two options (a) document it as mentioned above (b)
> detect and error out apply worker as currently proposed by patch. Now,
> we can argue to implement a larger solution to detect such cases and
> consider them as deadlock but I don't think that is a good idea
> especially for CLT.
>

Option (a), documenting this behavior, was discussed in [1], and the
doc change has now been pushed.

Attached are v94 patches.

v94-0001: Rebased on top of that doc commit, with two changes:
1. Removed the extra prepared-transaction handling from the apply worker.
2. Updated new two_phase doc saying the same stall can happen with the CLT.

v94-0002: No changes.

v94-0003: A top-up patch on 002 with changes for my review comments
below. Please review it and merge it into 002 if it looks fine.

v94-0004: No changes.

My review comments for 002:
1) pg_dump.sgml says that pg_dump does not dump the conflict log
table. But nothing says that pg_upgrade keeps it, along with its rows
and privileges. Should we document that too?
2) Comments and security labels on the conflict log table are lost
across pg_upgrade. COMMENT ON TABLE/COLUMN is allowed on it, but in
binary-upgrade mode selectDumpableTable() keeps only
DUMP_COMPONENT_DEFINITION | DUMP_COMPONENT_ACL. Should we add
DUMP_COMPONENT_COMMENT | DUMP_COMPONENT_SECLABEL as well?
3) In 002_pg_dump.pl, the test 'binary upgrade: conflict log table of
sub3' matches any "CREATE TABLE pg_conflict.pg_conflict_log_\d+". So
it would also pass with only sub4's table, and it doesn't really check
sub3's table. Should the regex check that the table name uses the OID
kept for sub3?

[1] https://www.postgresql.org/message-id/72c9b956-96c3-40a5-99bb-d9b7338c0bab@gmail.com

--
Thanks,
Nisha

Attachment Content-Type Size
v94-0001-Record-logical-replication-conflicts-in-the-conf.patch application/octet-stream 133.4 KB
v94-0002-Preserve-conflict-log-destination-and-subscripti.patch application/octet-stream 23.9 KB
v94-0003-002-comments-top-up-patch-for-review.patch application/octet-stream 4.7 KB
v94-0004-Add-conflict-log-table-information-to-describe-s.patch application/octet-stream 80.3 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Langote 2026-11-18 07:41:17 Re: Two more RI fast-path issues
Previous Message Fujii Masao 2026-11-18 07:13:50 Re: Assertion failure with temporary relations in single-user mode