Horton

Re: BUG: Postgres 14 + vacuum_defer_cleanup_age + FOR UPDATE + UPDATE

Andres Freund <andres@anarazel.de>

View thread View on postgr.es
From: Andres Freund <andres@anarazel.de>
To: Michail Nikolaev <michail.nikolaev@gmail.com>
Cc: Peter Geoghegan <pg@bowt.ie>, Matthias van de Meent <boekewurm+postgres@gmail.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2023-01-08T00:34:36Z
Lists: pgsql-hackers 
Hi,

On 2023-01-07 21:06:06 +0300, Michail Nikolaev wrote:
> 2) It is not an issue at table creation time. Issue is reproducible if
> vacuum_defer_cleanup_age set after table preparation.
> 
> 3) To reproduce the issue, vacuum_defer_cleanup_age should flip xid
> over zero (be >= txid_current()).
> And it is stable.... So, for example - unable to reproduce with 733
> value, but 734 gives error each time.
> Just a single additional txid_current() (after data is filled) fixes a
> crash... It looks like the first SELECT FOR UPDATE + UPDATE silently
> poisons everything somehow.
> You could use such PSQL script:

FWIW, the concrete value for vacuum_defer_cleanup_age is not crucial to
encounter the problem. It needs to be a value that, when compared to the xid
that did the "INSERT INTO something_is_wrong_here", results in value <= 0.

Setting vacuum_defer_cleanup_age than the xid to a much larger value allows
the crash to be encountered repeatedly.

Greetings,

Andres Freund

Commits

  1. Fix incorrect TAP test ordering

  2. pg_amcheck: Minor test speedups

  3. amcheck: Fix FullTransactionIdFromXidAndCtx() for xids before epoch 0

  4. amcheck: Fix ordering bug in update_cached_xid_range()

  5. Fix corruption due to vacuum_defer_cleanup_age underflowing 64bit xids

  6. Add hardening to catch invalid TIDs in indexes.

  7. Use full 64-bit XID for checking if a deleted GiST page is old enough.

  8. Avoid early reuse of btree pages, causing incorrect query results.