Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 8241D9FA2AE for ; Mon, 30 Jul 2007 05:07:40 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 58685-02 for ; Mon, 30 Jul 2007 05:07:38 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3]) by postgresql.org (Postfix) with ESMTP id AA1DD9FA26E for ; Mon, 30 Jul 2007 05:07:36 -0300 (ADT) Received: from webaccess.indicator.be ([10.100.1.7]) by interscan.indicator.com with InterScan Message Security Suite; Mon, 30 Jul 2007 09:51:15 +0200 Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise; Mon, 30 Jul 2007 10:07:34 +0200 Message-Id: <46ADB85B.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Date: Mon, 30 Jul 2007 10:07:23 +0200 From: "Bart Degryse" To: Subject: Re: raise exception and transaction handling References: <95f6bf9b0707281354n2f4772abmaba91132c466b3ac@mail.gmail.com><00 2d01c7d1d4$475e41e0$3e01a8c0@michalk> In-Reply-To: <002d01c7d1d4$475e41e0$3e01a8c0@michalk> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__Part3413D22B.0__=" X-imss-version: 2.047 X-imss-result: Passed X-imss-approveListMatch: *@indicator.be X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.096 tagged_above=0 required=5 tests=AWL=0.279, BAYES_40=-0.185, HTML_MESSAGE=0.001, UNPARSEABLE_RELAY=0.001 X-Spam-Level: X-Archive-Number: 200707/182 X-Sequence-Number: 28917 --=__Part3413D22B.0__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Alternative that always works no matter how many raise exceptions there = are: Create a (perlu) function that opens a second connection to your database = and does the insert into m_proba. Since this is a seperate connection, it's also a seperate transaction and = thus not rolled back by your "main" transaction. >>> "Michal Kedziora" 2007-07-29 13:33 >>> Hi, Marcin=20 =20 I'm not familiar with PREPARE TRANSACTION maby it could be done in that = way.=20 But, you can use a EXCEPTION clause, and there put your insert.=20 =20 IF EXISTS (SELECT 1 FROM g.m_lista WHERE idf =3D NEW.id) THEN RAISE EXCEPTION 'CENY NIE SPELNIAJA WARUNKOW! %', = rtrim(bledne); END IF; =20 EXCEPTION=20 when RAISE_EXCEPTION then=20 INSERT INTO g.m_proba VALUES (1,2); END; =20 It will work corect if you have only one RAISE EXCEPTION,becouse RAISE_EXCE= PTION concern every exception called by RAISE command. I hope that will help.=20 ----- Origi nal Message -----=20 From: Marcin Krawczyk ( mailto:jankes.mk@gmail.com )=20 To: pgsql-sql@postgresql.org=20 Sent: Saturday, July 28, 2007 10:54 PM Subject: [SQL] raise exception and transaction handling Hi, I have a problem with transaction handling. What I need to do is execute = an INSERT command that would not be canceled by the=20 RAISE EXCEPTION command in AFTER UPDATE TRIGGER. A piece of code:=20 BEGIN -- some computations=20 bledne :=3D (SELECT g.q_sumka('Poz.' || lps || ' - min. cena: ' || cena || = ' ' || waluta ||'; ') FROM g.m_lista WHERE idf =3D NEW.id); IF EXISTS (SELECT 1 FROM g.m_lista WHERE idf =3D NEW.id) THEN RAISE EXCEPTION 'CENY NIE SPELNIAJA WARUNKOW! %', rtrim(bledne); BEGIN INSERT INTO g.m_proba VALUES (1,2); -- this is the operation I need to = perform but the RAISE EXCEPTION above cancels it out=20 PREPARE TRANSACTION 'a'; COMMIT PREPARED 'a'; END; END IF; I tried to do it as shown above, with PREPARE and COMMIT but it's not = working.=20 --=__Part3413D22B.0__= Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Description: HTML

Alternative that always works no matter how many raise exceptions = there are:

Create a (perlu) function that opens a second connection to your = database and does the insert into m_proba.

Since this is a seperate connection, it's also a seperate transaction = and thus not rolled back by your "main" transaction.

>>> = "Michal Kedziora" <michalkd@o2.pl> 2007-07-29 13:33 >>>
<= /DIV>

Hi, Marcin

I'm not familiar with PREPARE TRANSACTION maby it could be done in that = way.

But, you can use a EXCEPTION clause, and = there put your insert.

IF EXISTS (SELECT 1 FROM g.m_lista WHERE idf =3D NEW.id) THEN
= ; = RAISE EXCEPTION 'CENY NIE SPELNIAJA WARUNKOW! %', = rtrim(bledne);
END IF;

EXCEPTION
when RAISE_EXCEPT= ION then
INSERT INTO g.m_proba VALUES (1,2);
END;

It will work corect if you have only one = RAISE EXCEPTION,becouse = RAISE_EXCEPTION concern every exception called by RAISE command.

I hope that will help.

----- Origi

nal Message -----

= From: Marcin Krawczyk

To: pgsql-sql@postgresql.org =

Sent: Saturday, July 28, 2007 10:54 = PM

Subject: [SQL] raise exception and = transaction handling

Hi,
I have a problem with transaction handling. What= I need to do is execute an INSERT&= nbsp;command that would not be canceled by&nb= sp;the
RAISE EXCEPTION command in AFTER UPDATE = TRIGGER. A piece of code:

BEGIN
-- some = computations

bledne :=3D (SELECT g.q_sumka('Poz.' || lps || ' - = min. cena: ' || cena || ' ' || waluta ||'; ') FROM g.m_lista WHERE idf =3D = NEW.id);

IF EXISTS (SELECT 1 FROM g.m_lista WHERE idf =3D NEW.id) = THEN
RAISE EXCEPTION 'CENY NIE SPELNIAJA WARUNKOW! %', rtrim(bledne);BEGIN
INSERT INTO g.m_proba VALUES (1,2); -- this is the operation I = need to perform but the RAISE EXCEPTION above cancels it out
PREPARE = TRANSACTION 'a';
COMMIT PREPARED 'a';
END;
END IF;

I tried = to do it as shown above, with PREPARE and COMMIT but = it's not working.

--=__Part3413D22B.0__=--