pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedraise exception and transaction handling
3+ messages / 3 participants
[nested] [flat]
* raise exception and transaction handling
@ 2007-07-28 20:54 Marcin Krawczyk <jankes.mk@gmail.com>
2007-07-29 11:33 ` Re: raise exception and transaction handling Michal Kedziora <michalkd@o2.pl>
2007-07-30 08:07 ` Re: raise exception and transaction handling Bart Degryse <Bart.Degryse@indicator.be>
0 siblings, 2 replies; 3+ messages in thread
From: Marcin Krawczyk @ 2007-07-28 20:54 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: raise exception and transaction handling
2007-07-28 20:54 raise exception and transaction handling Marcin Krawczyk <jankes.mk@gmail.com>
@ 2007-07-29 11:33 ` Michal Kedziora <michalkd@o2.pl>
1 sibling, 0 replies; 3+ messages in thread
From: Michal Kedziora @ 2007-07-29 11:33 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: raise exception and transaction handling
2007-07-28 20:54 raise exception and transaction handling Marcin Krawczyk <jankes.mk@gmail.com>
@ 2007-07-30 08:07 ` Bart Degryse <Bart.Degryse@indicator.be>
1 sibling, 0 replies; 3+ messages in thread
From: Bart Degryse @ 2007-07-30 08:07 UTC (permalink / raw)
To: pgsql-sql
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 -----
From: Marcin Krawczyk ( mailto:jankes.mk@gmail.com )
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-07-30 08:07 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-28 20:54 raise exception and transaction handling Marcin Krawczyk <jankes.mk@gmail.com>
2007-07-29 11:33 ` Michal Kedziora <michalkd@o2.pl>
2007-07-30 08:07 ` Bart Degryse <Bart.Degryse@indicator.be>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox