Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cWlFn-0001o6-PT for pgsql-admin@arkaria.postgresql.org; Thu, 26 Jan 2017 14:42:23 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cWlFm-0002F0-S5 for pgsql-admin@arkaria.postgresql.org; Thu, 26 Jan 2017 14:42:22 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cWgbY-0002ZV-Hr for pgsql-admin@postgresql.org; Thu, 26 Jan 2017 09:44:32 +0000 Received: from mx-lv-r2.lailio.net ([85.254.146.189]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cWgbV-0007id-6T for pgsql-admin@postgresql.org; Thu, 26 Jan 2017 09:44:31 +0000 Received: from z1.cloud.zimbrazilla.net (unknown [178.16.20.83]) (using TLSv1.2 with cipher AECDH-AES256-SHA (256/256 bits)) (No client certificate requested) by mx-lv-r2.lailio.net (Postfix) with ESMTPS id B70BA4222D3; Thu, 26 Jan 2017 11:44:14 +0200 (EET) Received: from z1.cloud.zimbrazilla.net ([127.0.0.1]) by localhost (z1.cloud.zimbrazilla.net [127.0.0.1]) (amavisd-new, port 10032) with ESMTP id eg8zhbrLHKK3; Thu, 26 Jan 2017 11:44:21 +0200 (EET) X-Virus-Scanned: amavisd-new at cloud.zimbrazilla.net Received: from z1.cloud.zimbrazilla.net ([127.0.0.1]) by localhost (z1.cloud.zimbrazilla.net [127.0.0.1]) (amavisd-new, port 10026) with ESMTP id 2-tn1pX6A-Hs; Thu, 26 Jan 2017 11:44:21 +0200 (EET) Received: from z1.cloud.zimbrazilla.net (localhost [127.0.0.1]) by z1.cloud.zimbrazilla.net (Postfix) with ESMTP id 9961D620713; Thu, 26 Jan 2017 11:44:21 +0200 (EET) Date: Thu, 26 Jan 2017 11:44:21 +0200 (EET) From: Maris Jansons To: Jorge Torralba Cc: pgsql-admin Message-ID: <1735117324.25930609.1485423861541.JavaMail.zimbra@lailio.net> In-Reply-To: References: <2141896725.20994473.1485356017414.JavaMail.zimbra@lailio.net> <1715943237.23967907.1485379847032.JavaMail.zimbra@lailio.net> Subject: Re: Help: unfixable/undead error - missing chunk number 0 for toast value 413511 in pg_toast_25477 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=_c061c9a4-ab69-4440-b88b-0c06925001d3" Thread-Topic: Help: unfixable/undead error - missing chunk number 0 for toast value 413511 in pg_toast_25477 Thread-Index: lF4AY+4uRTwK8gF3rnaR1XADh3YniA== X-Pg-Spam-Score: -5.1 (-----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --=_c061c9a4-ab69-4440-b88b-0c06925001d3 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit 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? Best Regards, Maris From: "Jorge Torralba" To: "Maris Jansons" Cc: "pgsql-admin" Sent: Wednesday, January 25, 2017 11:52:08 PM Subject: Re: [ADMIN] Help: unfixable/undead error - missing chunk number 0 for toast value 413511 in pg_toast_25477 I have run this with success several times. I modified it to be intuitive. There may be a typo or two but have a look at this: DO $f$ DECLARE baddata TEXT; badid INT; BEGIN FOR badid IN SELECT id FROM badtable LOOP BEGIN SELECT badcolumn INTO columndata FROM badtable where id = badid; EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'Data for ID % is corrupt', badid; CONTINUE; END; END LOOP; END; $f$ --=_c061c9a4-ab69-4440-b88b-0c06925001d3 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
I have identified the problematic column, i= n 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 LO= OP
    BEGIN
       = ; SELECT api_response
        INTO ba= ddata
        FROM api_logs where id= =3D badid;
    EXCEPTION
    &nbs= p;   WHEN OTHERS THEN
      &nbs= p;     RAISE NOTICE 'Data for ID % is corrupt', badid;<= br>            CONTI= NUE;
    END;
END LOOP;
END;
$f$
However this script finishes without errors, but if I r= un simple
> select api_response from api_logs;
= I get:
ERROR:  missing chunk number= 0 for toast value 413511 in pg_toast_25477
<= div>

Maybe I have messed something up in the script?

Best Regards,
Maris

From: "Jorge Torralba" <jorge.torralba@gmail.com>
To: = "Maris Jansons" <maris@lailio.net>
Cc: "pgsql-admin" <pg= sql-admin@postgresql.org>
Sent: Wednesday, January 25, 2017 11= :52:08 PM
Subject: Re: [ADMIN] Help: unfixable/undead error - mis= sing chunk number 0 for toast value 413511 in pg_toast_25477
=
I have run = this with success several times.  I modified it to be intuitive. There= may be a typo or two but have a look at this:

DO $f$

DECLARE

    baddata TEXT;<= /span>

    badid INT;

BEGIN

FOR badid IN SELECT id FROM = badtable LOOP

    BEGIN

        = SELECT badcolumn

        = INTO columndata

        = FROM badtable where id =3D badid;

    EXCEPTION

        = WHEN OTHERS THEN

        =     RAISE NOTICE 'Data for ID % is corrupt', badid;

        =     CONTINUE;

    END;

END LOOP;

END;

$f$

--=_c061c9a4-ab69-4440-b88b-0c06925001d3--