Re: Revisiting {CREATE INDEX, REINDEX} CONCURRENTLY improvements

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.

I can't think of an ordering that applies all changes correctly
without either filtering which transactions to include in LR apply
steps, or LR's sync scan snapshots being different from normal
snapshots on the primary.

Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)

[0] https://jepsen.io/analyses/amazon-rds-for-postgresql-17.4