Re: Revisiting {CREATE INDEX, REINDEX} CONCURRENTLY improvements

Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com> wrote:

> On Mon, 1 Dec 2025 at 10:09, Antonin Houska <ah(at)cybertec(dot)at> wrote:
> >
> > Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com> wrote:
> >
> > > I'm a bit worried, though, that LR may lose updates due to commit
> > > order differences between WAL and PGPROC. I don't know how that's
> > > handled in logical decoding, and can't find much literature about it
> > > in the repo either.
> >
> > Can you please give me an example of this problem? I understand that two
> > transactions do this
> >
> > T1: RecordTransactionCommit()
> > T2: RecordTransactionCommit()
> > T2: ProcArrayEndTransaction()
> > T1: ProcArrayEndTransaction()
> >
> > but I'm failing to imagine this if both transactions are trying to update the
> > same row.
>
> Correct, it doesn't have anything to do with two transactions updating
> the same row; but instead the same transaction getting applied twice;
> related to issues described in (among others) [0]:
> Logical replication applies transactions in WAL commit order, but
> (normal) snapshots on the primary use the transaction's persistence
> requirements (and procarray lock acquisition) as commit order.
>
> This can cause the snapshot to see T2 as committed before T1, whilst
> logical replication will apply transactions in T1 -> T2 order. This
> can break the exactly-once expectations of commits, because a normal
> snapshot taken between T2 and T1 on the primary (i.e., T2 is
> considered committed, but T1 not) will have T2 already applied. LR
> would have to apply changes of T1, which also implies it'd eventually
> get to T2's commit and apply that too. Alternatively, it'd skip past
> T2 because that's already present in the snapshot, and lose the
> changes that were committed with T1.

ISTM that what you consider a problem is copying the table using PGPROC-based
snapshot and applying logically decoded commits to the result - is that what
you mean?

In fact, LR (and also REPACK) uses snapshots generated by the logical decoding
system. The information on running/committed transactions is based here on
replaying WAL, not on PGPROC. Thus if the snapshot sees T2 already applied, it
means that the T2's COMMIT record was already decoded, and therefore no data
change of that transaction should be passed to the output plugin (and
consequently applied to the new table).

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