Re: Race conditions in logical decoding

Zhijie Hou (Fujitsu) <houzj(dot)fnst(at)fujitsu(dot)com> wrote:

> On Wednesday, September 9, 2026 6:20 PM Álvaro Herrera <alvherre(at)kurilemu(dot)de> wrote:
> > On 2026-Aug-22, Zhijie Hou (Fujitsu) wrote:
> > >
> > > Besides, just to confirm one note: IIUC, for exported snapshots by
> > > logicalrep, a transaction could be treated as committed while still in
> > > PGPROC, while concurrent MVCC snapshots still see it as in progress which
> > looks inconsistent.
> > > I understand that waiting for ProcArray removal in the general case
> > > could deadlock against synchronous replication, so it's probably
> > > acceptable to leave it unchanged for internal usage in active replication processes.
> >
> > OK. TBH I'm somewhat unease about this inconsistency; I wondered about
> > doing the CLOG-based test only in sync replication and using XidIsInProgress
> > otherwise, but didn't really try (which is to say: I'm not even sure if it's
> > _possible_ at all.)
>
> I experimented with this a bit and confirmed that the inconsistency exists,
> though it doesn't affect REPACK (CONCURRENTLY), the command takes an exclusive
> lock on the table when switching the old and new heap, which forces any
> concurrent transactions on that table to finish first. However, the
> inconsistency can be observed if a user directly uses the exported snapshot, as
> shown in the attachment (generated with AI assistance).

...

> diff --git a/src/test/recovery/t/058_exported_snapshot_pgproc_window.pl b/src/test/recovery/t/058_exported_snapshot_pgproc_window.pl
> new file mode 100644
> index 00000000000..4b6d8dd8353
> --- /dev/null
> +++ b/src/test/recovery/t/058_exported_snapshot_pgproc_window.pl
> @@ -0,0 +1,293 @@
> +# Copyright (c) 2026, PostgreSQL Global Development Group
> +#
> +# Test that a snapshot exported by CREATE_REPLICATION_SLOT ... (SNAPSHOT
> +# 'export') can treat a transaction as committed while that transaction is
> +# still in the procarray, so that concurrent MVCC snapshots taken by other
> +# backends still see it as in progress.

As far as I understand, what you demonstrate here is that different backends
can have a different view of the database. Isn't that pretty common situation?

What I'd consider a problem would be a single backend (and single transaction)
seeing inconsistent data.

--
Antonin Houska
Web: https://www.cybertec-postgresql.com