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

From: Alexander Lakhin <exclusion(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #18351: VACUUM FULL fails with error: missing chunk number 0 for toast value XXX
Date: 2024-03-05 18:00:00
Message-ID: d9123f62-64e1-0067-f46b-46286c85d02c@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

27.02.2024 12:00, Alexander Lakhin wrote:
> 17.02.2024 23:00, PG Bug reporting form wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference:      18351
>>
>> The following script:
>> ...
>> VACUUM tbl;
>> VACUUM FULL tbl;
>> " | psql -d db$c >psql-2-$c.log 2>&1 &
>>    done
>> wait
>> grep 'missing chunk number' server.log  && break;
>> done
>>
>

I've discovered that not only VACUUM FULL can stumble over such missing
toast values. CREATE INDEX behaves similarly, as the following script
shows:
for ((c=1;c<=20;c++)); do createdb db$c; done

for ((i=1;i<=100;i++)); do
echo "iteration $i"
  for ((c=1;c<=20;c++)); do
echo "\\d sometable" | psql -d db$c >psql-1-$c.log 2>&1 &
echo "DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl (i int, t text);
ALTER TABLE tbl ALTER COLUMN t SET STORAGE EXTERNAL;
INSERT INTO tbl(i, t) VALUES (1, repeat('1234567890', 250));
DELETE FROM tbl;

VACUUM (VERBOSE) tbl;
CREATE INDEX t_idx ON tbl(t);
" | psql -d db$c >psql-2-$c.log 2>&1 &
  done
wait
grep 'missing chunk number' server.log  && break;
done

The backtrace of the heap_fetch_toast_slice() call emitting error is:
2024-03-05 17:41:07.786 UTC|law|db10|65e75933.208c6b|ERROR:  missing chunk number 0 for toast value 17314 in pg_toast_17289
2024-03-05 17:41:07.786 UTC|law|db10|65e75933.208c6b|BACKTRACE:
heap_fetch_toast_slice at heaptoast.c:784:3
toast_fetch_datum at detoast.c:379:2
detoast_external_attr at detoast.c:54:12
index_form_tuple_context at indextuple.c:111:21
tuplesort_putindextuplevalues at tuplesortvariants.c:678:15
_bt_spool at nbtsort.c:537:1
_bt_build_callback at nbtsort.c:610:24
heapam_index_build_range_scan at heapam_handler.c:1329:22
table_index_build_scan at tableam.h:1781:9
 (inlined by) _bt_spools_heapscan at nbtsort.c:483:15
btbuild at nbtsort.c:329:14
index_build at index.c:3042:10
index_create at index.c:1265:3
DefineIndex at indexcmds.c:1166:3
...

Best regards,
Alexander

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Michael Paquier 2024-03-05 22:45:21 Re: BUG #18314: PARALLEL UNSAFE function does not prevent parallel index build
Previous Message Tom Lane 2024-03-05 15:42:11 Re: bug in function strtoint, on Windows OS won't report ERANGE