Thread: Missing chunk number on a view?

Missing chunk number on a view?

From

Keaton Adams

Date:

11 June 2008, 18:53:18

I searched the archives and found references to the “missing chunk number 0 for toast value X” error and tried the suggestions, but can’t seem to resolve this particular problem.

This is a test/QA system that uses an iSCSI disk array. Yesterday the array hiccupped and had to be rebooted while PostgreSQL was up and running. Now we are receiving the following error when running SQL against a particular view/table set:

Jun 11 08:18:45 mxlqa401 postgres[14176]: [1-1] ERROR: unexpected chunk number 1 (expected 0) for toast value 84631845
Jun 11 08:18:45 mxlqa401 postgres[14176]: [1-2] STATEMENT: SELECT SUM(p.hits) - SUM(p.denied) AS cumm_allowed,
Jun 11 08:18:45 mxlqa401 postgres[14176]: [1-3]            SUM(p.bytes_cs) AS cumm_bytes_cs,
Jun 11 08:18:45 mxlqa401 postgres[14176]: [1-4]            SUM(p.bytes_sc) AS cumm_bytes_sc,
Jun 11 08:18:45 mxlqa401 postgres[14176]: [1-5]            SUM(p.denied) AS cumm_denied,
Jun 11 08:18:45 mxlqa401 postgres[14176]: [1-6]            CAST (FLOOR(EXTRACT (EPOCH FROM p.created - '2008-06-11 06:00:00Z'::timestamptz)) AS INT8) / 360
0 AS startiv
Jun 11 08:18:45 mxlqa401 postgres[14176]: [1-7]     FROM kda_log_data AS p
Jun 11 08:18:45 mxlqa401 postgres[14176]: [1-8]     WHERE p.id = 34781768
Jun 11 08:18:45 mxlqa401 postgres[14176]: [1-9]       AND p.created >= '2008-06-11 06:00:00Z'::timestamptz
Jun 11 08:18:45 mxlqa401 postgres[14176]: [1-10]       AND p.created < '2008-06-12 06:00:00Z'::timestamptz
Jun 11 08:18:45 mxlqa401 postgres[14176]: [1-11]     GROUP BY CAST (FLOOR(EXTRACT (EPOCH FROM p.created - '2008-06-11 06:00:00Z'::timestamptz)) AS INT8) / 3600

The object kda_log_data is a view based on a series of selects against an entire table set joined by union statements.

When I attempt to drop the view I receive the following:

keaton=# drop view kda_log_data cascade;
ERROR: missing chunk number 0 for toast value 84631845

When I attempt to drop one of the underlying tables associated with the view I get:

keaton=# drop table kda_log_data_2006w40;
NOTICE: rule _RETURN on view kda_log_data depends on table kda_log_data_2006w40
NOTICE: view wds_log_data depends on rule _RETURN on view kda_log_data
ERROR: missing chunk number 0 for toast value 84631845

So I can’t drop the view with the cascade option and I can’t drop the underlying tables. I did copy out as much as the data as I could and my intention was to drop the view/tables, rebuild them and reload the data, but I can’t seem to get past the “DROP VIEW/DROP TABLE” step.

I did try to do a pg_dump on the database and it blew up with the same “missing chunk number” error.

Suggestions?

Thanks,

Keaton

Re: Missing chunk number on a view?

From

Tom Lane

Date:

11 June 2008, 20:20:23

Keaton Adams <kadams@mxlogic.com> writes:
> When I attempt to drop the view I receive the following:

> keaton=# drop view kda_log_data cascade;
> ERROR:  missing chunk number 0 for toast value 84631845

Presumably the toasted object in question is pg_rewrite's textual
representation of the view's ON SELECT rule.  If you're lucky, this is
just an index problem and reindexing pg_rewrite's toast table index will
fix it.  Since you didn't mention your PG version, I can't give you a
cookbook recipe for that ...

            regards, tom lane

Re: Missing chunk number on a view?

From

Keaton Adams

Date:

11 June 2008, 21:17:03

Sorry. Good point. I always try to remember to include that important info:

PostgreSQL 8.1.4 on RHEL 4 (we’re moving to 8.3.x on RHEL 5 this summer)

Thanks again,

Keaton

On 6/11/08 2:20 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

Keaton Adams <kadams@mxlogic.com> writes:
> When I attempt to drop the view I receive the following:

> keaton=# drop view kda_log_data cascade;
> ERROR: missing chunk number 0 for toast value 84631845

Presumably the toasted object in question is pg_rewrite's textual
representation of the view's ON SELECT rule. If you're lucky, this is
just an index problem and reindexing pg_rewrite's toast table index will
fix it. Since you didn't mention your PG version, I can't give you a
cookbook recipe for that ...

regards, tom lane

Re: Missing chunk number on a view?

From

Tom Lane

Date:

11 June 2008, 23:03:03

Keaton Adams <kadams@mxlogic.com> writes:
> Sorry.  Good point.  I always try to remember to include that important info:
> PostgreSQL 8.1.4 on RHEL 4  (we're moving to 8.3.x on RHEL 5 this summer)

okay, in 8.1 I get

regression=# select reltoastrelid from pg_class where relname = 'pg_rewrite';
 reltoastrelid
---------------
         10305
(1 row)

regression=# select reltoastidxid::regclass from pg_class where oid = 10305;
        reltoastidxid
------------------------------
 pg_toast.pg_toast_2618_index
(1 row)

So it looks like you want to try

regression=# reindex index pg_toast.pg_toast_2618_index;
REINDEX

although it wouldn't hurt to redo the first two steps just to be sure
that's the right one on your installation too.

            regards, tom lane