[ADMIN] Catch exceptions outside function
Dear all I ask for your help cause I can't point out the solution to my problem on PG 8.3 I would catch an exception outside any function/procedure but directly within script. BEGIN; -- raise an exception code EXCEPTION WHEN 'exception_type' THEN ROLLBACK; COMMIT; is it possible with PG 8.3? Many thanks in advance. Roberto -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Catch exceptions outside function
Roberto Grandi wrote: I ask for your help cause I can't point out the solution to my problem on PG 8.3 I would catch an exception outside any function/procedure but directly within script. BEGIN; -- raise an exception code EXCEPTION WHEN 'exception_type' THEN ROLLBACK; COMMIT; is it possible with PG 8.3? That's a bit unclear. What do you mean by outside a function but in a script? Can you explain in more detail? The code sample you paste looks like PL/pgSQL. You cannot commit or roll back in PL/pgSQL. If you want to undo in case of error whatever happens in the block, just replace the ROLLBACK with NOOP. Yours, Laurenz Albe -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Catch exceptions outside function
Roberto Grandi wrote Hi this is my script in details, remember that I launch it by .Net code (devart connector): SET LOCAL statement_timeout TO 1000; BEGIN; SELECT pg_sleep(5); -- QUERY that is long running; -- Some exception catch such as EXCEPTION END; I supposed my code can throw an eception for timeout and I would catch it. Otherwise consecutives queries report Transaction is aborted message error. Do you have any suggestion for me? Set your timeout longer than 5 seconds or sleep less than one second. You cannot catch an exception directly in the top-level SQL language portion of a command environment. If an exception gets that far your transaction has failed and you have to ROLLBACK. You can use savepoints to limit how far you have to rollback - see the documentation. Exceptions should not occur (or do so rarely) and so they cannot be simply ignored. If you have code that you expect to throw an exception you should try to rewrite it to test first and return true/false for availability. not always possible so catch and savepoints are ther to do if you must. David J. P.S. version 8.3 is no loner supported. P.P.S please follow list convention and bottom or interleave post. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Catch-exceptions-outside-function-tp5771398p5771426.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Catch exceptions outside function
On Sep 18, 2013, at 5:53 AM, Roberto Grandi roberto.gra...@trovaprezzi.it wrote: Do you have any suggestion for me? After the timeout, roll back the current transaction. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Catch exceptions outside function
-Original Message- From: pgsql-admin-ow...@postgresql.org [mailto:pgsql-admin- ow...@postgresql.org] On Behalf Of Roberto Grandi Sent: Wednesday, September 18, 2013 6:17 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] Catch exceptions outside function Dear all I ask for your help cause I can't point out the solution to my problem on PG 8.3 I would catch an exception outside any function/procedure but directly within script. BEGIN; -- raise an exception code EXCEPTION WHEN 'exception_type' THEN ROLLBACK; COMMIT; is it possible with PG 8.3? Many thanks in advance. Roberto No. It's not possible in 8.3. What you want is basically anonymous plpgsql block, EXCEPTION - is plpgsql, not pure sql, could be used only inside plpgsql function in 8.3. OTOH, starting with 9.0 you can use anonymous plpgsql blocks, and get what you asked for. Regards, Igor Neyman -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Catch exceptions outside function
On Sep 18, 2013, at 7:23 AM, David Johnston pol...@yahoo.com wrote: If an exception gets that far your transaction has failed and you have to ROLLBACK. Right, and after my prior post where I suggested rollback, I realized, it may be the case OP doesn't even realize there's an open transaction, which must eventually be committed or rolled back... -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Catch exceptions outside function
Roberto Grandi wrote Thanks Igor, this is a sufficient idea to take into account for upgrading to 9.x release. Thanks again. There is no 9.x release - singular A release designation requires both the first and second position. 8.4.x 9.0.x 9.1.x 9.2.x 9.3.x An .x can be used in the third position if the patch-release is unknown or should not be relevant - which it is when discussing major features such as the DO statement but not as much when discussing bugs and such which very well may have been identified/fixed by a patch release. If you intended to mean 9.0 or higher it is generally better to state the explicitly 9.0+ or equivalent. Apologies if you already know all this and were just imprecise in your writing but it is a misconception that some people have. Also, note that the Transaction is aborted error message is annoying but harmless. In some cases, such as when you construct and entire script to be executed at-once, you do not care about flow control and you will either commit or rollback at the end depending on whether the script succeeded or failed. In those cases, which I've personally experienced, it would be nice to be able to suppress the warning explicitly (but just that specific one) - but alas that is not currently possible that I am aware. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Catch-exceptions-outside-function-tp5771398p5771489.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
