Thread: SQL/PostgreSQL - Error observed in the QUERY not caught by the “EXCEPTION” block in the stored procedure
SQL/PostgreSQL - Error observed in the QUERY not caught by the “EXCEPTION” block in the stored procedure
From
Eduardo Lúcio Amorim Costa
Date:
I am trying to create a stored procedure to be used in a PostgreSQL DBMS.
The purpose of this stored procedure is to delete all records that present the following problem...
Query:
`
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
`
Based on the above behavior I created the following stored procedure:
Stored procedure:
`
DO $f$
DECLARE
my_file_now BYTEA;
my_id_now UUID;
BEGIN
FOR my_id_now IN SELECT my_id FROM public.my_datatable LOOP
BEGIN
SELECT file
INTO my_file_now
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;
END;
END LOOP;
END;
$f$;
`
QUESTION: Why is the error observed in the query not caught by the "EXCEPTION" block in the stored procedure?
Thanks! =D
--
The purpose of this stored procedure is to delete all records that present the following problem...
Query:
`
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
`
Based on the above behavior I created the following stored procedure:
Stored procedure:
`
DO $f$
DECLARE
my_file_now BYTEA;
my_id_now UUID;
BEGIN
FOR my_id_now IN SELECT my_id FROM public.my_datatable LOOP
BEGIN
SELECT file
INTO my_file_now
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;
END;
END LOOP;
END;
$f$;
`
QUESTION: Why is the error observed in the query not caught by the "EXCEPTION" block in the stored procedure?
Thanks! =D
Eduardo Lúcio![](http://www.lightbase.com.br/wp-content/uploads/2015/12/BrLightBase_Logomarca.jpg)
LightBase Consultoria em Software Público
+55-61-3347-1949 - http://brlight.org - Brasil-DF
![](http://www.lightbase.com.br/wp-content/uploads/2015/12/BrLightBase_Logomarca.jpg)
Software livre! Abrace essa idéia! ![](https://docs.google.com/uc?export=download&id=0B-PslcEmd46YWEhHenJJRGpKZU0&revid=0B-PslcEmd46YaExYMU9ZdGVXM2ZlSUdhMERLcXY1Qmd3enhrPQ)
"Aqueles que negam liberdade aos outros não a merecem para si mesmos."
Abraham Lincoln
Re: SQL/PostgreSQL - Error observed in the QUERY not caught by the “EXCEPTION” block in the stored procedure
From
Tom Lane
Date:
=?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
Re: SQL/PostgreSQL - Error observed in the QUERY not caught by the “EXCEPTION” block in the stored procedure
From
Eduardo Lúcio Amorim Costa
Date:
"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."
->
Yes. I already tried reindexing. Only a few records were recovered, others had to be deleted. The version of PG I use is "10.X".
"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.
[...]
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."
->
Thanks for the suggestions! I found it a bit strange "pgsql" not to "understand" as an exception the "ERROR: missing chunk number 0 for toast value 3483039 in pg_toast_3473493". I also noticed that the "pgsql" returns a code other than "0" when this error occurs. Finally, my only option was to use this customizable bash script https://stackoverflow.com/a/59770772/3223785 that I created to work around the problem. Note that this bash script also uses the pgsql return code (see excerpt "if [[ ${F_GET_EXIT_CODE_R} -ne 0 ]] && [[ $F_GET_STDERR_R == *" chunk number "* ]]; then") to address the issue. For all that can be observed this problem really seems to me a bug.
Thanks! =D
problem --- are you up to date on minor releases? It's also possible
that reindexing that toast table would fix it."
->
Yes. I already tried reindexing. Only a few records were recovered, others had to be deleted. The version of PG I use is "10.X".
"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.
[...]
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."
->
Thanks for the suggestions! I found it a bit strange "pgsql" not to "understand" as an exception the "ERROR: missing chunk number 0 for toast value 3483039 in pg_toast_3473493". I also noticed that the "pgsql" returns a code other than "0" when this error occurs. Finally, my only option was to use this customizable bash script https://stackoverflow.com/a/59770772/3223785 that I created to work around the problem. Note that this bash script also uses the pgsql return code (see excerpt "if [[ ${F_GET_EXIT_CODE_R} -ne 0 ]] && [[ $F_GET_STDERR_R == *" chunk number "* ]]; then") to address the issue. For all that can be observed this problem really seems to me a bug.
Thanks! =D
Em sex., 17 de jan. de 2020 às 01:30, Tom Lane <tgl@sss.pgh.pa.us> escreveu:
Eduardo Lúcio 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
Eduardo Lúcio![](http://www.lightbase.com.br/wp-content/uploads/2015/12/BrLightBase_Logomarca.jpg)
LightBase Consultoria em Software Público
+55-61-3347-1949 - http://brlight.org - Brasil-DF
![](http://www.lightbase.com.br/wp-content/uploads/2015/12/BrLightBase_Logomarca.jpg)
Software livre! Abrace essa idéia! ![](https://docs.google.com/uc?export=download&id=0B-PslcEmd46YWEhHenJJRGpKZU0&revid=0B-PslcEmd46YaExYMU9ZdGVXM2ZlSUdhMERLcXY1Qmd3enhrPQ)
"Aqueles que negam liberdade aos outros não a merecem para si mesmos."
Abraham Lincoln
Re: SQL/PostgreSQL - Error observed in the QUERY not caught by the “EXCEPTION” block in the stored procedure
From
"David G. Johnston"
Date:
On Sat, Jan 25, 2020 at 5:32 PM Eduardo Lúcio Amorim Costa <eduardolucioac@gmail.com> wrote:
Thanks for the suggestions! I found it a bit strange "pgsql" not to "understand" as an exception the "ERROR: missing chunk number 0 for toast value 3483039 in pg_toast_3473493"
Please inline or bottom-post here.
I think Tom's point is that the function you wrote never actually attempted to print out the value of the field so the error never got triggered in the function. You need to actually attempt to manipulate the data to get an error. If you did get the function to actually encounter the error it should (haven't tested myself) be caught in the exception handler; i.e., "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 [query to try]".David J.
Re: SQL/PostgreSQL - Error observed in the QUERY not caught by the “EXCEPTION” block in the stored procedure
From
Eduardo Lúcio Amorim Costa
Date:
Gentlemen,
I found your answers very useful, so I took the liberty of publishing them on the thread I opened about the problem on the internet ( https://stackoverflow.com/a/59922553/3223785 ).
If you do not want this content to continue to be published, please let me know so I can delete it.
Thanks! =D
I found your answers very useful, so I took the liberty of publishing them on the thread I opened about the problem on the internet ( https://stackoverflow.com/a/59922553/3223785 ).
If you do not want this content to continue to be published, please let me know so I can delete it.
Thanks! =D
Em dom., 26 de jan. de 2020 às 03:54, David G. Johnston <david.g.johnston@gmail.com> escreveu:
On Sat, Jan 25, 2020 at 5:32 PM Eduardo Lúcio Amorim Costa <eduardolucioac@gmail.com> wrote:Thanks for the suggestions! I found it a bit strange "pgsql" not to "understand" as an exception the "ERROR: missing chunk number 0 for toast value 3483039 in pg_toast_3473493"Please inline or bottom-post here.I think Tom's point is that the function you wrote never actually attempted to print out the value of the field so the error never got triggered in the function. You need to actually attempt to manipulate the data to get an error. If you did get the function to actually encounter the error it should (haven't tested myself) be caught in the exception handler; i.e., "A more reliable way to trigger the problem is to do some computationthat requires the value of the field, perhaps along the lines of [query to try]".David J.
Eduardo Lúcio![](http://www.lightbase.com.br/wp-content/uploads/2015/12/BrLightBase_Logomarca.jpg)
LightBase Consultoria em Software Público
+55-61-3347-1949 - http://brlight.org - Brasil-DF
![](http://www.lightbase.com.br/wp-content/uploads/2015/12/BrLightBase_Logomarca.jpg)
Software livre! Abrace essa idéia! ![](https://docs.google.com/uc?export=download&id=0B-PslcEmd46YWEhHenJJRGpKZU0&revid=0B-PslcEmd46YaExYMU9ZdGVXM2ZlSUdhMERLcXY1Qmd3enhrPQ)
"Aqueles que negam liberdade aos outros não a merecem para si mesmos."
Abraham Lincoln