Re: BUG #18351: VACUUM FULL fails with error: missing chunk number 0 for toast value XXX

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