From: Tom Lane <tgl@sss.pgh.pa.us>
To: Eduardo Lúcio Amorim Costa <eduardolucioac@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: SQL/PostgreSQL - Error observed in the QUERY not caught by the “EXCEPTION” block in the stored procedure
Date: Thu, 16 Jan 2020 23:30:03 -0500
Message-ID: <1524.1579235403@sss.pgh.pa.us> (raw)
In-Reply-To: <CAN+8gCjhvxz1+L-u5r8ApYP_4rOM0YDE4KdHd01JTaM5cGuoaw@mail.gmail.com>
References: <CAN+8gCjhvxz1+L-u5r8ApYP_4rOM0YDE4KdHd01JTaM5cGuoaw@mail.gmail.com>
=?UTF-8?Q?Eduardo_L=C3=BAcio_Amorim_Costa?= <eduardolucioac@gmail.com> 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
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, eduardolucioac@gmail.com
Subject: Re: SQL/PostgreSQL - Error observed in the QUERY not caught by the “EXCEPTION” block in the stored procedure
In-Reply-To: <1524.1579235403@sss.pgh.pa.us>
References: <CAN+8gCjhvxz1+L-u5r8ApYP_4rOM0YDE4KdHd01JTaM5cGuoaw@mail.gmail.com> <1524.1579235403@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