Обсуждение: 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
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
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 "Transactionis aborted message error". Do you have any suggestion for me? Thanks again Roberto ----- Messaggio originale ----- Da: "Albe Laurenz" <laurenz.albe@wien.gv.at> A: "Roberto Grandi *EXTERN*" <roberto.grandi@trovaprezzi.it>, pgsql-admin@postgresql.org Inviato: Mercoledì, 18 settembre 2013 12:41:01 Oggetto: RE: 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
Roberto Grandi wrote: > 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? I see. You cannot catch an exception in SQL, as far as I know (unless you use a DO statement, which was introduced in PostgreSQL 9.0). You could use a "poor man's DO" by creating a function that does what you want, catch the exception in PL/pgSQL, call the function and drop it. But I really think that this should be handled on the application side, i.e. your code should ignore the exception. Yours, Laurenz Albe
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.
On Sep 18, 2013, at 5:53 AM, Roberto Grandi <roberto.grandi@trovaprezzi.it> wrote: > Do you have any suggestion for me? After the timeout, roll back the current transaction. -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice
> -----Original Message----- > From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin- > owner@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 plpgsqlfunction in 8.3. OTOH, starting with 9.0 you can use anonymous plpgsql blocks, and get what you asked for. Regards, Igor Neyman
On Sep 18, 2013, at 7:23 AM, David Johnston <polobo@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'san open transaction, which must eventually be committed or rolled back... -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice
Thanks Igor, this is a sufficient idea to take into account for upgrading to 9.x release. Thanks again. Roberto ----- Messaggio originale ----- Da: "Igor Neyman" <ineyman@perceptron.com> A: "Roberto Grandi" <roberto.grandi@trovaprezzi.it>, pgsql-admin@postgresql.org Inviato: Mercoledì, 18 settembre 2013 15:37:12 Oggetto: RE: Catch exceptions outside function > -----Original Message----- > From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin- > owner@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 plpgsqlfunction in 8.3. OTOH, starting with 9.0 you can use anonymous plpgsql blocks, and get what you asked for. Regards, Igor Neyman
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.