From: soumik.bhattacharjee@kpn.com
To: pgsql-admin@lists.postgresql.org
Subject: RE: Commit and Exception Block
Date: Fri, 8 Oct 2021 08:00:34 +0000
Message-ID: <a7dacaf0474d48deaf82fe9ad6289ed9@kpn.com> (raw)
In-Reply-To: <05e4b1ba5e4741e08f63c113b5dd5ef7@kpn.com>
References: <05e4b1ba5e4741e08f63c113b5dd5ef7@kpn.com>
Hi Experts,
I have a procedure that may encounter an exception. I want to have an exception handling block where exception is to catch is any FK violations.
Could you please suggest where it's getting missed as part of a transaction.
* PG Version : PostgreSQL 12.4
- Initial Error - 2D000 cannot commit while a subtransaction is active
* Moved the exception block within another Begin/End block.
* But now --> It does not go to the exception handling block itself.
Thanks in advance...
CREATE OR REPLACE PROCEDURE ddd.dddremove(
p_number_of_rows integer,
INOUT complete text)
LANGUAGE 'plpgsql'
AS $BODY$ DECLARE
tmprow ddd.order%rowtype;
p_counter INTEGER := 0;
p_final_count INTEGER := 0;
cnt_result INTEGER :=0;
begin
FOR tmprow IN
select idx from ddd.order where so_created_at< now() - interval '1460 days'
LOOP
RAISE notice 'order Id %',tmprow.idx;
delete from ddd.order_settings where sos_order_id=100;
delete from ddd.order where idx=tmprow.idx;
-- GET DIAGNOSTICS cnt_result = ROW_COUNT;
-- IF cnt_result = 0 THEN
-- RAISE NOTICE 'Removed % rows with amount = 0', cnt_result;
-- complete :='FOREIGN_KEY_VIOLATION';
-- return;
-- END IF;
p_counter := p_counter + 1;
IF (p_counter !=0) then
RAISE notice 'p_counter %',p_counter;
COMMIT;
END IF;
EXIT WHEN p_counter > p_number_of_rows;
END LOOP;
begin
RAISE SQLSTATE 'MYERR';
EXCEPTION
WHEN FOREIGN_KEY_VIOLATION then
complete :='FOREIGN_KEY_VIOLATION';
RETURN ;
WHEN SQLSTATE 'MYERR' then
complete :='Procedure Successful';
RETURN ;
WHEN no_data_found then
complete :='FOREIGN_KEY_VIOLATION';
RETURN ;
end;
SELECT COUNT(*)
INTO p_final_count
FROM ddd.order where so_created_at< now() - interval '1460 days';
RAISE NOTICE 'Sog Order Count After deleting % rows in the order table is %', p_counter,p_final_count;
complete :='completed';
return;
end $BODY$;
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 using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: soumik.bhattacharjee@kpn.com, pgsql-admin@lists.postgresql.org
Subject: RE: Commit and Exception Block
In-Reply-To: <a7dacaf0474d48deaf82fe9ad6289ed9@kpn.com>
* 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