BUG: Postgres 14 + vacuum_defer_cleanup_age + FOR UPDATE + UPDATE

From: Michail Nikolaev <michail(dot)nikolaev(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: BUG: Postgres 14 + vacuum_defer_cleanup_age + FOR UPDATE + UPDATE
Date: 2023-01-05 13:12:32
Message-ID: CANtu0ojby3eBdMXfs4QmS+K1avBc7NcRq_Ot5bnzrbwM+uQ55w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello, hackers.

It seems like PG 14 works incorrectly with vacuum_defer_cleanup_age
(or just not cleared rows, not sure) and SELECT FOR UPDATE + UPDATE.
I am not certain, but hot_standby_feedback probably able to cause the
same issues.

Steps to reproduce:

1) Start Postgres like this:

docker run -it -p 5432:5432 --name pg -e
POSTGRES_PASSWORD=postgres -e LANG=C.UTF-8 -d postgres:14.6 -c
vacuum_defer_cleanup_age=1000000

2) Prepare scheme:

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));

3) Prepare file for pgbench:

BEGIN;

SELECT omg.*
FROM something_is_wrong_here AS omg
ORDER BY random()
LIMIT 1
FOR UPDATE
\gset

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

END;

4) Run pgbench:

pgbench -c 50 -j 2 -n -f reproduce.bench 'port= 5432
host=localhost user=postgres dbname=postgres password=postgres' -T 100
-P 1

I was able to see such a set of errors (looks scary):

ERROR: MultiXactId 30818104 has not been created yet -- apparent wraparound
ERROR: could not open file "base/13757/16385.1" (target block
39591744): previous segment is only 24 blocks
ERROR: attempted to lock invisible tuple
ERROR: could not access status of transaction 38195704
DETAIL: Could not open file "pg_subtrans/0246": No such file or directory.

Best regards,
Michail.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Dean Rasheed 2023-01-05 13:21:09 Re: MERGE ... WHEN NOT MATCHED BY SOURCE
Previous Message Dean Rasheed 2023-01-05 12:52:30 Re: Improve tab completion for ALTER FUNCTION/PROCEDURE/ROUTINE