Postgres Pro
Downloads
Home   >   mailing lists

Thread: raise exception and transaction handling

raise exception and transaction handling

From
"Marcin Krawczyk"
Date:
Hi,<br />I have a problem with transaction
handling. What I need to do is execute an INSERT command that would not be canceled by the <br/>RAISE EXCEPTION command
inAFTER UPDATE TRIGGER. A piece of code: <br /><br />BEGIN<br />-- some computations <br /><br />bledne := (SELECT
g.q_sumka('Poz.'|| lps || ' - min. cena: ' || cena || ' ' || waluta ||'; ') FROM g.m_lista WHERE idf = NEW.id);<br
/><br/>IF EXISTS (SELECT 1 FROM g.m_lista WHERE idf = NEW.id) THEN<br />RAISE EXCEPTION 'CENY NIE SPELNIAJA WARUNKOW!
%',rtrim(bledne);<br /> BEGIN<br /> INSERT INTO g.m_proba VALUES (1,2); -- this is the operation I need to perform but
theRAISE EXCEPTION above cancels it out <br /> PREPARE TRANSACTION 'a';<br /> COMMIT PREPARED 'a';<br /> END;<br />END
IF;<br/><br />I tried to do it as shown above, with PREPARE and COMMIT but it's not working. <br />

Re: raise exception and transaction handling

From
"Michal Kedziora"
Date:
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 = NEW.id) THEN
                    RAISE EXCEPTION 'CENY NIE SPELNIAJA WARUNKOW! %', rtrim(bledne);
END IF;
 
EXCEPTION 
 when RAISE_EXCEPTION 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 -----
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 
RAISE EXCEPTION command in AFTER UPDATE TRIGGER. A piece of code:

BEGIN
-- some computations

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

IF EXISTS (SELECT 1 FROM g.m_lista WHERE idf = 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.

Re: raise exception and transaction handling

From
"Bart Degryse"
Date:
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 >>>
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 = NEW.id) THEN
                    RAISE EXCEPTION 'CENY NIE SPELNIAJA WARUNKOW! %', rtrim(bledne);
END IF;
 
EXCEPTION 
 when RAISE_EXCEPTION 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 -----
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 
RAISE EXCEPTION command in AFTER UPDATE TRIGGER. A piece of code:

BEGIN
-- some computations

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

IF EXISTS (SELECT 1 FROM g.m_lista WHERE idf = 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.