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.
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 |