Horton

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

Michail Nikolaev <michail.nikolaev@gmail.com>

View thread View on postgr.es
From: Michail Nikolaev <michail.nikolaev@gmail.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Matthias van de Meent <boekewurm+postgres@gmail.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2023-01-07T18:06:06Z
Lists: pgsql-hackers

Attachments

Hello.

The few things I have got so far:

1) It is not required to order by random() to reproduce the issue - it
could be done using queries like:

      BEGIN;
      SELECT omg.*
         FROM something_is_wrong_here AS omg
         ORDER BY value -- change is here
         LIMIT 1
         FOR UPDATE
      \gset


      UPDATE something_is_wrong_here SET value = :value + 1 WHERE id = :id;
      COMMIT;

But for some reason it is harder to reproduce without random in my
case (typically need to wait for about a minute with 100 connections).

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:

      DROP TABLE IF EXISTS something_is_wrong_here;

      CREATE TABLE something_is_wrong_here (id bigserial PRIMARY KEY,
value numeric(15,4) DEFAULT 0 NOT NULL);

      INSERT INTO something_is_wrong_here (value) (SELECT 10000 from
generate_series(0, 100));

      SELECT txid_current() \gset

      SELECT :txid_current + 1 as txid \gset

      ALTER SYSTEM SET vacuum_defer_cleanup_age to :txid;SELECT
pg_reload_conf();

I have attached some scripts if someone goes to reproduce.

Best regards,
Michail.

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.