BUG: Postgres 14 + vacuum_defer_cleanup_age + FOR UPDATE + UPDATE

Поиск
Список
Период
Сортировка
От Michail Nikolaev
Тема BUG: Postgres 14 + vacuum_defer_cleanup_age + FOR UPDATE + UPDATE
Дата
Msg-id CANtu0ojby3eBdMXfs4QmS+K1avBc7NcRq_Ot5bnzrbwM+uQ55w@mail.gmail.com
обсуждение исходный текст
Ответы Re: BUG: Postgres 14 + vacuum_defer_cleanup_age + FOR UPDATE + UPDATE  (Michail Nikolaev <michail.nikolaev@gmail.com>)
Re: BUG: Postgres 14 + vacuum_defer_cleanup_age + FOR UPDATE + UPDATE  (Matthias van de Meent <boekewurm+postgres@gmail.com>)
Список 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.



В списке pgsql-hackers по дате отправления:

Предыдущее
От: Dean Rasheed
Дата:
Сообщение: Re: Improve tab completion for ALTER FUNCTION/PROCEDURE/ROUTINE
Следующее
От: Dean Rasheed
Дата:
Сообщение: Re: MERGE ... WHEN NOT MATCHED BY SOURCE