pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCommit and Exception Block
3+ messages / 2 participants
[nested] [flat]
* Commit and Exception Block
@ 2021-10-07 09:11 soumik.bhattacharjee@kpn.com
2021-10-08 08:00 ` RE: Commit and Exception Block soumik.bhattacharjee@kpn.com
0 siblings, 1 reply; 3+ messages in thread
From: soumik.bhattacharjee@kpn.com @ 2021-10-07 09:11 UTC (permalink / raw)
To: pgsql-admin
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$;
^ permalink raw reply [nested|flat] 3+ messages in thread
* RE: Commit and Exception Block
2021-10-07 09:11 Commit and Exception Block soumik.bhattacharjee@kpn.com
@ 2021-10-08 08:00 ` soumik.bhattacharjee@kpn.com
2021-10-08 15:03 ` Re: Commit and Exception Block Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: soumik.bhattacharjee@kpn.com @ 2021-10-08 08:00 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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$;
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Commit and Exception Block
2021-10-07 09:11 Commit and Exception Block soumik.bhattacharjee@kpn.com
2021-10-08 08:00 ` RE: Commit and Exception Block soumik.bhattacharjee@kpn.com
@ 2021-10-08 15:03 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2021-10-08 15:03 UTC (permalink / raw)
To: soumik.bhattacharjee@kpn.com; +Cc: pgsql-admin@lists.postgresql.org
<soumik.bhattacharjee@kpn.com> writes:
> 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.
I think you're confused about the scope of the exception block.
The syntax is
BEGIN
some code here that might throw an exception
EXCEPTION
some WHEN clauses here to catch exceptions from the covered code
END
You wrote:
> begin
> RAISE SQLSTATE 'MYERR';
> EXCEPTION
> WHEN ...
So this exception block can *only* trap errors arising from that one
RAISE command, not anything earlier in the procedure. Seems unlikely
that's what you wanted.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-10-08 15:03 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-07 09:11 Commit and Exception Block soumik.bhattacharjee@kpn.com
2021-10-08 08:00 ` soumik.bhattacharjee@kpn.com
2021-10-08 15:03 ` Re: Commit and Exception Block Tom Lane <tgl@sss.pgh.pa.us>
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