Re: [BUGS] BUG #14651: Uninitialized page fix corrupted TOAST table

nilesoien@gmail.com writes:
> It's been doing this ever since (about two days). I think I figured out
> which table data.pg_toast.pg_toast_9303780 supports :

> $ oid2name -o 9303780 -d data
>> From database "data":
>   Filenode     Table Name
> -------------------------
>    9303780  rdvtrack_fd05

That's not hugely reliable because filenode isn't necessarily equal to
oid.  I'd try this to be sure:

select relname from pg_class where reltoastrelid = 'pg_toast.pg_toast_9303780'::regclass;

> And interestingly I can still select from that table :

> data=# select count(*) from hmi.rdvtrack_fd05;

A "select count(*)" would only fetch the main table rows, not out-of-line
TOAST data, so this proves little.  What you want is to see if you can
retrieve all the data in the table.  An easy way to do that is to try to
pg_dump it ("pg_dump -t problem_table ...").

> It looks like there's a damaged block in the TOAST table, but
> does the fact that I can do the select on the table supported by
> the TOAST table mean that this damaged block has no entries?

It's possible but I'd hardly bet on it yet.

> Can I do this :

> SET zero_damaged_pages = on;
> VACUUM FULL data.pg_toast.pg_toast_9303780;
> (I'll get a warning that it's zeroing out the damaged block)
> REINDEX TABLE data.pg_toast.pg_toast_9303780;

You can, but my bet is that you'll lose data.  You may have little
choice though.  (I wouldn't bother with the FULL, btw, since you
already know that a regular vacuum will hit the page.)  Where you
probably will end up is identifying the main-table row(s) that reference
this page and deleting them.

An angle that might be interesting is to check the physical length of
the toast table and see whether the problem page is the last page or
nearly last.  If so, the problem might have originated in a failed
relation-extension attempt.  This would also suggest that the main
table row(s) referencing the bad page are recent.
        regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs