From: Tom Lane <tgl@sss.pgh.pa.us>
To: Maris Jansons <maris@lailio.net>
Cc: Jorge Torralba <jorge.torralba@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Help: unfixable/undead error - missing chunk number 0 for toast value 413511 in pg_toast_25477
Date: Thu, 26 Jan 2017 11:03:03 -0500
Message-ID: <4224.1485446583@sss.pgh.pa.us> (raw)
In-Reply-To: <1735117324.25930609.1485423861541.JavaMail.zimbra@lailio.net>
References: <2141896725.20994473.1485356017414.JavaMail.zimbra@lailio.net>
<CACut7uQ0Z_+CR2ygXbtnXbsibKuxhLemwNAck6jVMAoq4xm9kQ@mail.gmail.com>
<1715943237.23967907.1485379847032.JavaMail.zimbra@lailio.net>
<CACut7uR8ohqdDkn-343SZbBwu-stXRLeZf051dgyrQVaFidQ+g@mail.gmail.com>
<1735117324.25930609.1485423861541.JavaMail.zimbra@lailio.net>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Maris Jansons <maris@lailio.net> writes:
> I have identified the problematic column, in my case it is api_response, my version of script:
> DO $f$
> DECLARE
> baddata TEXT;
> badid INT;
> BEGIN
> FOR badid IN SELECT id FROM api_logs LOOP
> BEGIN
> SELECT api_response
> INTO baddata
> FROM api_logs where id = badid;
> EXCEPTION
> WHEN OTHERS THEN
> RAISE NOTICE 'Data for ID % is corrupt', badid;
> CONTINUE;
> END;
> END LOOP;
> END;
> $f$
> However this script finishes without errors, but if I run simple
>> select api_response from api_logs;
> I get:
> ERROR: missing chunk number 0 for toast value 413511 in pg_toast_25477
> Maybe I have messed something up in the script?
Oh ... what is happening, I think, is that the toast pointers are getting
stored into the "baddata" local variable as-is. The problem would only
get detected if the system has to fetch the toasted value, and this code
doesn't make it do so.
You could do something that requires fetching the value, perhaps
SELECT api_response || '' INTO baddata ...
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, maris@lailio.net, jorge.torralba@gmail.com
Subject: Re: Help: unfixable/undead error - missing chunk number 0 for toast value 413511 in pg_toast_25477
In-Reply-To: <4224.1485446583@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox