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.