Re: create_index test fails when synchronous_commit = off @ master

From: Andres Freund <andres(at)anarazel(dot)de>
To: Aleksander Alekseev <aleksander(at)timescale(dot)com>, David Rowley <dgrowleyml(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: create_index test fails when synchronous_commit = off @ master
Date: 2022-02-24 15:46:48
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


On 2022-02-24 07:33:39 -0800, Andres Freund wrote:
> I added the SELECT relpages, reltuples, relallvisible FROM pg_class WHERE oid = 'tenk1'::regclass;
> just after the
> synchronous_commit=on
> + relpages | reltuples | relallvisible
> +----------+-----------+---------------
> + 345 | 10000 | 345
> +(1 row)
> synchronous_commit=off
> + relpages | reltuples | relallvisible
> +----------+-----------+---------------
> + 345 | 10000 | 0
> +(1 row)
> So it clearly is the explanation for the issue.
> Obviously we can locally work around it by adding a
> SET LOCAL synchronous_commit = local;
> to the COPY. But I'd like to fully understand what's going on.

It is the hint bit sets delayed by asynchronous commit. I traced execution and
we do end up not setting all visible due to reaching the
!HeapTupleHeaderXminCommitted() path in lazy_scan_prune()

* Is the tuple definitely visible to all transactions?
* NB: Like with per-tuple hint bits, we can't set the
* PD_ALL_VISIBLE flag if the inserter committed
* asynchronously. See SetHintBits for more info. Check that
* the tuple is hinted xmin-committed because of that.
if (prunestate->all_visible)
TransactionId xmin;

if (!HeapTupleHeaderXminCommitted(tuple.t_data))

So it might be reasonable to use synchronous_commit=on in test_setup.sql?

It's not super satisfying, but i don't immediately see what else could prevent
all-visible to be set in this case? There's no dead rows, so concurrent
snapshots shouldn't prevent cleanup.

I guess we could alternatively try doing something like flushing pending async
commits at the start of vacuum. But that probably isn't warranted.


Andres Freund

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Masahiko Sawada 2022-02-24 15:49:37 Re: Design of pg_stat_subscription_workers vs pgstats
Previous Message Alvaro Herrera 2022-02-24 15:46:28 Re: Add id's to various elements in protocol.sgml