SQL/PostgreSQL - Error observed in the QUERY not caught by the “EXCEPTION” block in the stored procedure

Поиск
Список
Период
Сортировка
От Eduardo Lúcio Amorim Costa
Тема SQL/PostgreSQL - Error observed in the QUERY not caught by the “EXCEPTION” block in the stored procedure
Дата
Msg-id CAN+8gCjhvxz1+L-u5r8ApYP_4rOM0YDE4KdHd01JTaM5cGuoaw@mail.gmail.com
обсуждение исходный текст
Ответы Re: SQL/PostgreSQL - Error observed in the QUERY not caught by the “EXCEPTION” block in the stored procedure  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
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

--
Eduardo Lúcio
LightBase Consultoria em Software Público
+55-61-3347-1949 - http://brlight.org - Brasil-DF
Software livre! Abrace essa idéia! 
"Aqueles que negam liberdade aos outros não a merecem para si mesmos."
Abraham Lincoln

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: BUG #16207: localization functions upper() and lower() does not work for text returned by convert_from()
Следующее
От: Pendekar Dikala Senja
Дата:
Сообщение: Re: BUG #16205: background worker "logical replication worker" (PID25218) was terminated by signal 11: Segmentation