On Wed, Jan 28, 2015 at 10:49 AM, ciamblex [via PostgreSQL] <[hidden email]> wrote:
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.