Re: ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100 - Mailing list pgsql-general

Tom writes:

>> And when I run the suggested query, I get:
>
>>   efamroot@kat efam=# select chunk_id, chunk_seq, ctid, xmin, xmax, length(chunk_data) from
pg_toast.pg_toast_10919630where chunk_id = 1698936148 order by 1,2;
 
>>     chunk_id  | chunk_seq |     ctid     |    xmin    | xmax | length 
>>   ------------+-----------+--------------+------------+------+--------
>>    1698936148 |         0 | (52888694,2) | 1511390221 |    0 |   1996
>>    1698936148 |         1 | (52888694,4) | 1511390221 |    0 |   1148
>>   (2 rows)

> Hmph.  So if you EXPLAIN that query, does it show it's doing it as an
> indexscan?  I'd expect so, but it's always good to make sure.

It does:

  efamroot@kat efam=# SELECT * FROM efam.sendreference WHERE id = '189909908';
  ERROR:  unexpected chunk number 0 (expected 1) for toast value 1698936148 in pg_toast_10919630
  efamroot@kat efam=# select chunk_id, chunk_seq, ctid, xmin, xmax, length(chunk_data) from pg_toast.pg_toast_10919630
wherechunk_id = 1698936148 order by 1,2;
 
    chunk_id  | chunk_seq |     ctid     |    xmin    | xmax | length 
  ------------+-----------+--------------+------------+------+--------
   1698936148 |         0 | (52888694,2) | 1511390221 |    0 |   1996
   1698936148 |         1 | (52888694,4) | 1511390221 |    0 |   1148
  (2 rows)

  efamroot@kat efam=# explain select chunk_id, chunk_seq, ctid, xmin, xmax, length(chunk_data) from
pg_toast.pg_toast_10919630where chunk_id = 1698936148 order by 1,2;
 
                                                    QUERY PLAN                                                  
  --------------------------------------------------------------------------------------------------------------
   Index Scan using pg_toast_10919630_index on pg_toast_10919630  (cost=0.57..2627179.25 rows=2135674 width=54)
     Index Cond: (chunk_id = 1698936148::oid)
  (2 rows)

> Assuming it does say that, then the other test I had in mind would
> involve "set enable_indexscan = 0", then repeat the EXPLAIN to make
> sure that you now get a seqscan plan (you might need to turn off
> enable_bitmapscan too), then do the query again and see whether the
> results are the same.

Ok (if I don't disable bitmapscan, I get Bitmap Heap Scans in the EXPLAIN, so):

  efamroot@kat efam=# set enable_indexscan = 0;
  SET
  efamroot@kat efam=# set enable_bitmapscan = 0;
  SET
  efamroot@kat efam=# explain select chunk_id, chunk_seq, ctid, xmin, xmax, length(chunk_data) from
pg_toast.pg_toast_10919630where chunk_id = 1698936148 order by 1,2;
 
                                       QUERY PLAN                                      
  -------------------------------------------------------------------------------------
   Sort  (cost=96465280.57..96470619.75 rows=2135674 width=54)
     Sort Key: chunk_seq
     ->  Seq Scan on pg_toast_10919630  (cost=0.00..96240754.39 rows=2135674 width=54)
           Filter: (chunk_id = 1698936148::oid)
  (4 rows)

  efamroot@kat efam=# select chunk_id, chunk_seq, ctid, xmin, xmax, length(chunk_data) from pg_toast.pg_toast_10919630
wherechunk_id = 1698936148 order by 1,2;
 
  [... still waiting for the result, I will return with what it said
       when the server does ...]


  Best regards,

    Adam

-- 
 "No more than that, but very powerful all the                Adam Sjøgren
  same; simple things are good."                        adsj@novozymes.com