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 1mYPRC-0001pZ-5D for pgsql-admin@arkaria.postgresql.org; Thu, 07 Oct 2021 09:11:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mYPRB-0004JG-45 for pgsql-admin@arkaria.postgresql.org; Thu, 07 Oct 2021 09:11:37 +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 1mYPRA-0004J7-FH for pgsql-admin@lists.postgresql.org; Thu, 07 Oct 2021 09:11:36 +0000 Received: from cpdelvb-safe101.kpnxchange.com ([195.121.3.35]) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mYPR4-00074i-Ao for pgsql-admin@postgresql.org; Thu, 07 Oct 2021 09:11:35 +0000 Content-Language: en-US Content-Type: multipart/alternative; boundary="_000_05e4b1ba5e4741e08f63c113b5dd5ef7kpncom_" DKIM-Signature: v=1; a=rsa-sha256; d=kpn.com; s=safemail01; c=relaxed/relaxed; t=1633597886; h=mime-version:date:message-id:subject:from:to:content-type; bh=b7bISc1OODusKYY1KPiGBh25QlbXah0+jA45rHCXxfo=; b=dxJNKeC705CWurzCEQRr81MUu1vB9cS7WEezvXErg57ZqTWmOsHi3aoOoqw+Z9h79JWHRX8PKAz GtcK/NFLbwMnvQIXb8TmGB97GuudTs0E7ipskmQDCDWI2ydUVkq+ocE3MMUPsex+XqxJ+WvBFbKXE mJmYhAwRfmToAo984vQ= Received: from CPSUBM-SAFE107.kpnxchange.com ([10.94.186.36]) by cpdelvb-safe101.kpnxchange.com with Microsoft SMTPSVC(8.5.9600.16384); Thu, 7 Oct 2021 11:11:26 +0200 X-KPNSenderID: 4294967167 Received: from CPEMS-KPN103.KPNCNL.LOCAL ([195.121.122.236]) by CPSUBM-SAFE107.kpnxchange.com over TLS secured channel with Microsoft SMTPSVC(8.5.9600.16384); Thu, 7 Oct 2021 11:11:26 +0200 Received: from CPEMS-KPN04.KPNCNL.LOCAL (192.168.99.143) by CPEMS-KPN103.KPNCNL.LOCAL (192.168.100.142) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.2308.14; Thu, 7 Oct 2021 11:11:26 +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; Thu, 7 Oct 2021 11:11:26 +0200 From: To: Subject: Commit and Exception Block Thread-Topic: Commit and Exception Block Thread-Index: Ade7WozLSi0YxVhISX2Pz2pmpNHjEA== Date: Thu, 7 Oct 2021 09:11:26 +0000 Message-ID: <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: 07 Oct 2021 09:11:26.0719 (UTC) FILETIME=[4EAE68F0:01D7BB5B] X-RcptDomain: postgresql.org List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_05e4b1ba5e4741e08f63c113b5dd5ef7kpncom_ 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_05e4b1ba5e4741e08f63c113b5dd5ef7kpncom_ 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_counter,p_final_count;

    = complete :=3D'completed';

   &= nbsp; return;

end $BODY$;

 

--_000_05e4b1ba5e4741e08f63c113b5dd5ef7kpncom_--