Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mYko8-0004ch-8n for pgsql-admin@arkaria.postgresql.org; Fri, 08 Oct 2021 08:00:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mYko7-0004ph-3S for pgsql-admin@arkaria.postgresql.org; Fri, 08 Oct 2021 08:00:43 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mYko6-0004pY-EK for pgsql-admin@lists.postgresql.org; Fri, 08 Oct 2021 08:00:42 +0000 Received: from cpdelvb-safe102.kpnxchange.com ([195.121.3.36]) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mYko2-00010c-EM for pgsql-admin@lists.postgresql.org; Fri, 08 Oct 2021 08:00:41 +0000 Content-Language: en-US Content-Type: multipart/alternative; boundary="_000_a7dacaf0474d48deaf82fe9ad6289ed9kpncom_" DKIM-Signature: v=1; a=rsa-sha256; d=kpn.com; s=safemail01; c=relaxed/relaxed; t=1633680034; h=mime-version:date:message-id:subject:from:to:content-type; bh=ISjIcQS9kah2eEej7K7fCvCuNO+whfAPlyllCiVWTXI=; b=gi+obnkDol1R7AgkfK9Slg025BV77TvCuEgqTCI3EUMh2Q6IJ79zlSxsVD6FMQQxFhy2QzES6vj r0D+4cBDJS0rytn33kLePGgs0m6vYkX2KnxeiEF01JDKmGSU0SFxHKa24lVmrzfdzpOz3Ul7m52+V GpML9mzfESoID4Jhp40= Received: from CPSUBM-SAFE102.kpnxchange.com ([10.94.186.7]) by cpdelvb-safe102.kpnxchange.com with Microsoft SMTPSVC(8.5.9600.16384); Fri, 8 Oct 2021 10:00:34 +0200 X-KPNSenderID: 4294967167 Received: from CPEMS-KPN102.KPNCNL.LOCAL ([195.121.122.236]) by CPSUBM-SAFE102.kpnxchange.com over TLS secured channel with Microsoft SMTPSVC(8.5.9600.16384); Fri, 8 Oct 2021 10:00:33 +0200 Received: from CPEMS-KPN04.KPNCNL.LOCAL (192.168.99.143) by CPEMS-KPN102.KPNCNL.LOCAL (192.168.100.141) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.2308.14; Fri, 8 Oct 2021 10:00:34 +0200 Received: from CPEMS-KPN04.KPNCNL.LOCAL ([192.168.99.143]) by CPEMS-KPN04.KPNCNL.LOCAL ([192.168.99.143]) with mapi id 15.01.2308.014; Fri, 8 Oct 2021 10:00:34 +0200 From: To: Subject: RE: Commit and Exception Block Thread-Topic: Commit and Exception Block Thread-Index: Ade7WozLSi0YxVhISX2Pz2pmpNHjEAAv/S1A Date: Fri, 8 Oct 2021 08:00:34 +0000 Message-ID: References: <05e4b1ba5e4741e08f63c113b5dd5ef7@kpn.com> In-Reply-To: <05e4b1ba5e4741e08f63c113b5dd5ef7@kpn.com> Accept-Language: nl-NL, en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: MSIP_Label_d2dc6f62-bb58-4b94-b6ca-9af54699d31b_Enabled=true; MSIP_Label_d2dc6f62-bb58-4b94-b6ca-9af54699d31b_SetDate=2021-10-07T09:01:10Z; MSIP_Label_d2dc6f62-bb58-4b94-b6ca-9af54699d31b_Method=Standard; MSIP_Label_d2dc6f62-bb58-4b94-b6ca-9af54699d31b_Name=d2dc6f62-bb58-4b94-b6ca-9af54699d31b; MSIP_Label_d2dc6f62-bb58-4b94-b6ca-9af54699d31b_SiteId=d7790549-8c35-40ea-ad75-954ac3e86be8; MSIP_Label_d2dc6f62-bb58-4b94-b6ca-9af54699d31b_ActionId=39cb67b7-0b19-4575-b3e6-bc246dbd9704; MSIP_Label_d2dc6f62-bb58-4b94-b6ca-9af54699d31b_ContentBits=0 x-originating-ip: [192.168.100.129] MIME-Version: 1.0 X-OriginalArrivalTime: 08 Oct 2021 08:00:33.0904 (UTC) FILETIME=[92381700:01D7BC1A] X-RcptDomain: lists.postgresql.org List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_a7dacaf0474d48deaf82fe9ad6289ed9kpncom_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi Experts, I have a procedure that may encounter an exception. I want to have an excep= tion 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 ac= tive * 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 :=3D 0; p_final_count INTEGER :=3D 0; cnt_result INTEGER :=3D0; 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=3D100; delete from ddd.order where idx=3Dtmprow.idx; -- GET DIAGNOSTICS cnt_result =3D ROW_COUNT; -- IF cnt_result =3D 0 THEN -- RAISE NOTICE 'Removed % rows with amount =3D 0', cnt_result; -- complete :=3D'FOREIGN_KEY_VIOLATION'; -- return; -- END IF; p_counter :=3D p_counter + 1; IF (p_counter !=3D0) 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 :=3D'FOREIGN_KEY_VIOLATION'; RETURN ; WHEN SQLSTATE 'MYERR' then complete :=3D'Procedure Successful'; RETURN ; WHEN no_data_found then complete :=3D'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 i= s %', p_counter,p_final_count; complete :=3D'completed'; return; end $BODY$; --_000_a7dacaf0474d48deaf82fe9ad6289ed9kpncom_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi Experts,

 

I have a procedure that may enc= ounter an exception. I want to have an exception handling block where excep= tion 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
-       &nb=
sp;  Initial Error - 2D000 cannot commit while a subtransaction is active
 

 

  • Moved the exception block within another Begin/E= nd block.
  • But now à= It does not go to the exception handling block itself= .

 

 

Thanks in advance…

 

 

 

CREATE OR REPLACE P= ROCEDURE ddd.dddremove(

   &= nbsp;            p_n= umber_of_rows integer,

   &= nbsp;            INO= UT complete text)

LANGUAGE 'plpgsql'<= o:p>

 

AS $BODY$ DECLARE

tmprow ddd.order%ro= wtype;

p_counter &nbs= p;   INTEGER :=3D 0;

p_final_count INTEG= ER :=3D 0;

cnt_result INTEGER = :=3D0;

begin   &= nbsp; 

   F= OR tmprow IN

   &= nbsp;  select idx from ddd.order where so_created_at< now() - = interval '1460 days'

    = LOOP            = ;   

   &= nbsp;  RAISE notice 'order Id %',tmprow.idx;

   &= nbsp;            &nb= sp; delete from ddd.order_settings where sos_order_id=3D100; 

   &= nbsp;            &nb= sp; delete from ddd.order where idx=3Dtmprow.idx;

--   = ;            GET DIAGNOST= ICS cnt_result =3D ROW_COUNT;

--   = ;            IF cnt_resul= t =3D 0 THEN

--   = ;     RAISE NOTICE 'Removed % rows with amount =3D 0', = cnt_result;

--   = ;     complete :=3D'FOREIGN_KEY_VIOLATION';<= /span>

--   = ;     return;

--   = ;   END IF;

   &= nbsp;            &nb= sp; p_counter :=3D p_counter + 1;

   &= nbsp; 

   &= nbsp;  IF (p_counter !=3D0) then

   &= nbsp;   RAISE notice 'p_counter %',p_counter;

   &= nbsp;     COMMIT;

   &= nbsp;  END IF;

   &= nbsp;  EXIT WHEN p_counter > p_number_of_rows;

  

   &= nbsp;END LOOP;

   begin<= o:p>

    = RAISE SQLSTATE 'MYERR';

   &= nbsp;  EXCEPTION

   &= nbsp;  WHEN FOREIGN_KEY_VIOLATION then

   &= nbsp;  complete :=3D'FOREIGN_KEY_VIOLATION';

   &= nbsp;  RETURN ;

   &= nbsp;  WHEN SQLSTATE 'MYERR' then

   &= nbsp;  complete :=3D'Procedure Successful';

   &= nbsp;  RETURN ;

   &= nbsp;  WHEN no_data_found then

   &= nbsp;  complete :=3D'FOREIGN_KEY_VIOLATION';

   &= nbsp;  RETURN ;

   end;

SELECT COUNT(*)

   &= nbsp; INTO p_final_count

   &= nbsp; FROM ddd.order where so_created_at< now() - interval '1460 days';<= o:p>

 

   RAISE = NOTICE 'Sog Order Count After deleting % rows in the order table is %', p_c= ounter,p_final_count;

    = complete :=3D'completed';

   &= nbsp; return;

end $BODY$;

 

--_000_a7dacaf0474d48deaf82fe9ad6289ed9kpncom_--