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.

I've discovered that not only VACUUM FULL can stumble over such missing
toast values. CREATE INDEX behaves similarly, as the following script
shows: