Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1isJGw-0003Fa-J9 for pgsql-bugs@arkaria.postgresql.org; Fri, 17 Jan 2020 04:30:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1isJGu-00064w-RW for pgsql-bugs@arkaria.postgresql.org; Fri, 17 Jan 2020 04:30:12 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1isJGu-00064p-Fm for pgsql-bugs@lists.postgresql.org; Fri, 17 Jan 2020 04:30:12 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1isJGo-0002Ne-1a for pgsql-bugs@lists.postgresql.org; Fri, 17 Jan 2020 04:30:11 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id 00H4U35n001525; Thu, 16 Jan 2020 23:30:03 -0500 From: Tom Lane To: =?UTF-8?Q?Eduardo_L=C3=BAcio_Amorim_Costa?= cc: pgsql-bugs@lists.postgresql.org Subject: Re: =?UTF-8?Q?SQL=2FPostgreSQL_=2D_Error_observed_in_the_QUERY_not_cau?= =?UTF-8?Q?ght_by_the_=E2=80=9CEXCEPTION=E2=80=9D_block_in_the_stored_procedure?= In-reply-to: References: Comments: In-reply-to =?UTF-8?Q?Eduardo_L=C3=BAcio_Amorim_Costa?= message dated "Wed, 15 Jan 2020 14:31:57 -0300" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1523.1579235403.1@sss.pgh.pa.us> Date: Thu, 16 Jan 2020 23:30:03 -0500 Message-ID: <1524.1579235403@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk =?UTF-8?Q?Eduardo_L=C3=BAcio_Amorim_Costa?= writes: > my_database=# SELECT file INTO my_file_now FROM public.my_datatable WHERE > my_id='2fdf5297-8d4a-38bc-bb26-b8a4b7ba47ec'; > ERROR: missing chunk number 0 for toast value 3483039 in pg_toast_3473493 We've fixed a few bugs over the years that manifest in that type of problem --- are you up to date on minor releases? It's also possible that reindexing that toast table would fix it. > Based on the above behavior I created the following stored procedure: > ... > my_file_now BYTEA; > ... > SELECT file > INTO my_file_now > FROM public.my_datatable WHERE my_id=my_id_now; > QUESTION: Why is the error observed in the query not caught by the > "EXCEPTION" block in the stored procedure? I think that plpgsql will not bother to dereference a TOAST pointer when storing it into a local variable (although this statement is very possibly version-dependent, and you didn't say what PG version you are using). A more reliable way to trigger the problem is to do some computation that requires the value of the field, perhaps along the lines of PERFORM md5(file) FROM public.my_datatable WHERE my_id=my_id_now; > EXCEPTION > WHEN OTHERS THEN > RAISE NOTICE 'CORRUPTED MY_ID - % ', my_id_now; > DELETE FROM public.my_datatable WHERE my_id=my_id_now; I don't know that I'd give a procedure like this license to delete my entire table :-(. If you really don't care how much data survives, why not just TRUNCATE the table and be done with it? Otherwise, printing the list of troublesome rows for manual review seems way more prudent. regards, tom lane