Message-ID: <d9123f62-64e1-0067-f46b-46286c85d02c@gmail.com>
Date: Tue, 5 Mar 2024 21:00:00 +0300
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.4.2
Subject: Re: BUG #18351: VACUUM FULL fails with error: missing chunk number 0
 for toast value XXX
Content-Language: en-US
From: Alexander Lakhin <exclusion@gmail.com>
To: pgsql-bugs@lists.postgresql.org
References: <18351-f6e06364b3a2e669@postgresql.org>
 <80abbbf0-588b-f808-c264-77c3ebee83f3@gmail.com>
In-Reply-To: <80abbbf0-588b-f808-c264-77c3ebee83f3@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/d9123f62-64e1-0067-f46b-46286c85d02c%40gmail.com>
Precedence: bulk

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