[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: [SQL] raise exception and transaction handling
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 - 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 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: [SQL] raise exception and transaction handling
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" <[EMAIL PROTECTED]> 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 - From: Marcin Krawczyk ( mailto:[EMAIL PROTECTED] ) 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 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.