agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedRe: How to insert in a table the error returns by query
6+ messages / 3 participants
[nested] [flat]
* Re: How to insert in a table the error returns by query
@ 2015-01-28 16:55 ` David G Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: David G Johnston @ 2015-01-28 16:55 UTC (permalink / raw)
To: pgsql-sql
ciamblex wrote
> I need to save in a table the error code (SQLSTATE) and the error message
> (SQLERRM) returned by an insert or an update. My procedure must execute an
> insert, and if an error occurs, it must be saved into an apposite table.
>
> But the problem is that if I use the EXCEPTION BLOCK, when an error occurs
> the transaction is aborted and any command after cannot be execute.
>
> How can I save the error returned by a query in a table, using PLPGSQL???
>
> Thanks
The typical solution seems to be to use dblink to open an independent
session that is not affected by rollback when the main session cronks. I'm
not sure if FDWs work for this purpose...
David J.
--
View this message in context: http://postgresql.nabble.com/How-to-insert-in-a-table-the-error-returns-by-query-tp5835771p5835803.h...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to insert in a table the error returns by query
@ 2015-01-28 17:49 ` ciamblex <gianluca.civiello@yahoo.it>
2015-01-28 18:22 ` Re: How to insert in a table the error returns by query David G Johnston <david.g.johnston@gmail.com>
2015-01-28 18:52 ` Re: How to insert in a table the error returns by query Marc Mamin <M.Mamin@intershop.de>
1 sibling, 2 replies; 6+ messages in thread
From: ciamblex @ 2015-01-28 17:49 UTC (permalink / raw)
To: pgsql-sql
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.h...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to insert in a table the error returns by query
2015-01-28 17:49 ` Re: How to insert in a table the error returns by query ciamblex <gianluca.civiello@yahoo.it>
@ 2015-01-28 18:22 ` David G Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: David G Johnston @ 2015-01-28 18:22 UTC (permalink / raw)
To: pgsql-sql
On Wed, Jan 28, 2015 at 10:49 AM, ciamblex [via PostgreSQL] <
ml-node+s1045698n5835817h51@n5.nabble.com> 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
"
http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html
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: http://postgresql.nabble.com/How-to-insert-in-a-table-the-error-returns-by-query-tp5835771p5835827.h...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to insert in a table the error returns by query
2015-01-28 17:49 ` Re: How to insert in a table the error returns by query ciamblex <gianluca.civiello@yahoo.it>
@ 2015-01-28 18:52 ` Marc Mamin <M.Mamin@intershop.de>
2015-01-28 19:03 ` Re: How to insert in a table the error returns by query David G Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Marc Mamin @ 2015-01-28 18:52 UTC (permalink / raw)
To: ciamblex <gianluca.civiello@yahoo.it>; pgsql-sql
>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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to insert in a table the error returns by query
2015-01-28 17:49 ` Re: How to insert in a table the error returns by query ciamblex <gianluca.civiello@yahoo.it>
2015-01-28 18:52 ` Re: How to insert in a table the error returns by query Marc Mamin <M.Mamin@intershop.de>
@ 2015-01-28 19:03 ` David G Johnston <david.g.johnston@gmail.com>
2015-01-29 18:01 ` Re: How to insert in a table the error returns by query Marc Mamin <M.Mamin@intershop.de>
0 siblings, 1 reply; 6+ messages in thread
From: David G Johnston @ 2015-01-28 19:03 UTC (permalink / raw)
To: pgsql-sql
On Wed, Jan 28, 2015 at 11:53 AM, Marc Mamin-2 [via PostgreSQL] <
ml-node+s1045698n5835837h71@n5.nabble.com> wrote:
>
> >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: http://postgresql.nabble.com/How-to-insert-in-a-table-the-error-returns-by-query-tp5835771p5835838.h...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to insert in a table the error returns by query
2015-01-28 17:49 ` Re: How to insert in a table the error returns by query ciamblex <gianluca.civiello@yahoo.it>
2015-01-28 18:52 ` Re: How to insert in a table the error returns by query Marc Mamin <M.Mamin@intershop.de>
2015-01-28 19:03 ` Re: How to insert in a table the error returns by query David G Johnston <david.g.johnston@gmail.com>
@ 2015-01-29 18:01 ` Marc Mamin <M.Mamin@intershop.de>
0 siblings, 0 replies; 6+ messages in thread
From: Marc Mamin @ 2015-01-29 18:01 UTC (permalink / raw)
To: David G Johnston <david.g.johnston@gmail.com>; pgsql-sql
>On Wed, Jan 28, 2015 at 11:53 AM, Marc Mamin-2 [via PostgreSQL] <[hidden email]> wrote:
>
>
> >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.
You are right, and a quick test with 9.3.5 is consistent with the doc.
regards,
Marc Mamin
>
>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.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2015-01-29 18:01 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-01-28 16:55 ` David G Johnston <david.g.johnston@gmail.com>
2015-01-28 17:49 ` ciamblex <gianluca.civiello@yahoo.it>
2015-01-28 18:22 ` David G Johnston <david.g.johnston@gmail.com>
2015-01-28 18:52 ` Marc Mamin <M.Mamin@intershop.de>
2015-01-28 19:03 ` David G Johnston <david.g.johnston@gmail.com>
2015-01-29 18:01 ` Marc Mamin <M.Mamin@intershop.de>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox