From: Peter Petrov <p.petrov@postgrespro.ru>
To: Alexander Lakhin <exclusion@gmail.com>
To: pgsql-bugs@lists.postgresql.org
Cc: reshkekirill@gmail.com
Subject: Re: BUG #18351: VACUUM FULL fails with error: missing chunk number 0 for toast value XXX
Date: Fri, 19 Apr 2024 16:22:05 +0300
Message-ID: <0be50698-450b-4bf8-9d34-b559e34b5d4f@postgrespro.ru> (raw)
In-Reply-To: <d9123f62-64e1-0067-f46b-46286c85d02c@gmail.com>
References: <18351-f6e06364b3a2e669@postgresql.org>
<80abbbf0-588b-f808-c264-77c3ebee83f3@gmail.com>
<d9123f62-64e1-0067-f46b-46286c85d02c@gmail.com>
Hello, Alexander!
I suspect that the main problem here is that we calculate
data_oldest_nonremovable for main and toast-tables.
1)
I took your example and printed xmin and xid for all entries in PGPROC,
the following was produced:
pgprocno 79, dbid 16402, xid 0, xmin 1469, statusFlags 2*
*
pgprocno 88, dbid 16402, xid 0, xmin 1469, statusFlags 0
MyDatabaseId 16402, my_pgprocno 79, latest_completed 1479,
oldest_considered_running 1449, shared_oldest_nonremovable 1449,
catalog_oldest_nonremovable 1469, data_oldest_nonremovable 1469
GlobalVisDataRels.maybe_needed = 1469
GlobalVisDataRels.definitely_needed = 1469
The first proc was doing VACUUM (VERBOSE) tbl*.*
Since it was lazy vacuum we didn't take it into account*.
*The second proc was doing \d sometable.
It's xmin became data_oldest_nonremovable.
*
*2)
Let's have a look into (0,1) tuple from the tbl**relation during vacuum:
xmin 1475, xmax 1479, t_infomask 1286*
*
The tuple couldn't be removed since xmax > definitely_needed
1479 > 1469
3)
When the toast table was vacuuming, the second proc has
already finished its query.
pgprocno 79, dbid 16402, xid 0, xmin 1478, statusFlags 2
pgprocno 88, dbid 16402, xid 0, xmin 0, statusFlags 0
Therefore, we got the following horizons:
MyDatabaseId 16402, my_pgprocno 79, latest_completed 1479
oldest_considered_running 1468, shared_oldest_nonremovable 1468
catalog_oldest_nonremovable 1480, data_oldest_nonremovable 1480
GlobalVisDataRels.maybe_needed = 1480
GlobalVisDataRels.definitely_needed = 1480
4)
Let's have a look into (0, 1) and (0,2) tuples**from the toast table
They were related with the (0,1) tuple from the tbl relation:
blockno 0, offnum 2, xmin 1475, xmax 1479, t_infomask 1282
blockno 0, offnum 1, xmin 1475, xmax 1479, t_infomask 1282*
*
Since their xmax < maybe_needed then they were removed.
As a result, we got the situation when the tuple in the tbl relation
referenced to non-existing tuples from the toast relation.
*
*Therefore, there are two questions:
1)
Is it possible to process toast table by using the same horizons for the
main table?
2)
Is maybe_needed = definitely_needed a corner case?
Should it be handled differently?
Thank you in advance.
Sincerely yours.
Peter Petrov.
On 05.03.2024 21:00, Alexander Lakhin wrote:
>
> I've discovered that not only VACUUM FULL can stumble over such missing
> toast values. CREATE INDEX behaves similarly, as the following script
> shows:
>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: p.petrov@postgrespro.ru, exclusion@gmail.com, pgsql-bugs@lists.postgresql.org, reshkekirill@gmail.com
Subject: Re: BUG #18351: VACUUM FULL fails with error: missing chunk number 0 for toast value XXX
In-Reply-To: <0be50698-450b-4bf8-9d34-b559e34b5d4f@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox