Обсуждение: Re: How to insert in a table the error returns by query
Thank you David fot your replay. I have an other question. How can i rollback ALL the query when one of these return an error? My code is like the following: ------------------------------------ BEGIN INSERT INTO table_1 .... INSERT INTO table_2 .... INSERT INTO table_3 .... EXCEPTION WHEN others THEN code:=SQLSTATE; mess:=SQLERRM; es:=code||'|'||mess; RETURN es; END; ------------------------------------ In this case when an error occurs the rollback work only on the wrong query. The other insert are committed. Thank you! -- View this message in context: http://postgresql.nabble.com/How-to-insert-in-a-table-the-error-returns-by-query-tp5835771p5835817.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
Thank you David fot your replay.
I have an other question.
How can i rollback ALL the query when one of these return an error?
My code is like the following:
------------------------------------
BEGIN
INSERT INTO table_1 ....
INSERT INTO table_2 ....
INSERT INTO table_3 ....
EXCEPTION WHEN others THEN
code:=SQLSTATE;
mess:=SQLERRM;
es:=code||'|'||mess;
RETURN es;
END;
------------------------------------
In this case when an error occurs the rollback work only on the wrong query. The other insert are committed.
Based on this statement:
"
When an error is caught by an EXCEPTION clause, the local variables of the PL/pgSQL function remain as they were when the error occurred, but all changes to persistent database state within the block are rolled back"
You have either found a bug (documentation or code) or your actual code is doing something more complex than what you are showing here. If you provide a self-contained test case that exhibits the behavior you are observing it will be possible to determine which of those two possibilities apply.
David J.
View this message in context: Re: How to insert in a table the error returns by query
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>I have an other question. > >How can i rollback ALL the query when one of these return an error? > >My code is like the following: > >------------------------------------ >BEGIN > >INSERT INTO table_1 .... > >INSERT INTO table_2 .... > >INSERT INTO table_3 .... > >EXCEPTION WHEN others THEN > code:=SQLSTATE; > mess:=SQLERRM; > >es:=code||'|'||mess; > > >RETURN es; > >END; >------------------------------------ > >In this case when an error occurs the rollback work only on the wrong query. The other insert are committed. The rollback only takes place on the errored statement, because you are catching the exception. In order to ensure a complete rollback of your transaction (which may have started outside of your function), you'll need to rethrow an error after your exception handling. In order to store a corresponding message within a table, you'll need a separate transaction which can be achieved, - as already mentioned- with dblink. Possibly some <pg>_fwd extensions allow this too, I don't know. regards, Marc Mamin
>In this case when an error occurs the rollback work only on the wrong query. The other insert are committed.
The rollback only takes place on the errored statement, because you are catching the exception.
In order to ensure a complete rollback of your transaction (which may have started outside of your function),
you'll need to rethrow an error after your exception handling.
The 9.4 documentation is in direct conflict with this statement...all persistent updates inside the associated BEGIN/END block should be rolled back.
Transactions MUST start "outside your function" by definition. By not re-throwing the exception any outer block (i.e., the one calling the function) would still end up intact but every statement inside of the function should rollback unless separate blocks are created to isolate the different statements.
David J.
View this message in context: Re: How to insert in a table the error returns by query
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
<div style="direction: ltr;font-family: Tahoma;color: #000000;font-size: 10pt;"><br /> >On Wed, Jan 28, 2015 at 11:53AM, Marc Mamin-2 [via PostgreSQL] <[hidden email]> wrote:<br /> ><br /> ><br /> > >In this casewhen an error occurs the rollback work only on the wrong query. The other insert are committed.<br /> ><br /> ><br/> > The rollback only takes place on the errored statement, because you are catching the exception.<br /> > In order to ensure a complete rollback of your transaction (which may have started outside of your function),<br />> you'll need to rethrow an error after your exception handling.<br /> ><br /> ><br /> > The 9.4 documentationis in direct conflict with this statement...all persistent updates inside the associated BEGIN/END block shouldbe rolled back.<br /><br /> You are right, and a quick test with 9.3.5 is consistent with the doc.<br /> regards,<br/><br /> Marc Mamin<br /><br /> ><br /> >Transactions MUST start "outside your function" by definition. By not re-throwing the exception any outer block (i.e., the one calling the function) would still end up intactbut every statement inside of the function should rollback unless separate blocks are created to isolate the differentstatements.<br /> ><br /> >David J.</div>