Sketch of extending error handling for subtransactions in functions
Currently, the way that ereport/elog processing works is:
1. Collect up all the error parameter information into an ErrorData
structure. (This is somewhat nontrivial in itself, at least in the
ereport case, but I'll gloss over that here.)
2. Construct a log message and/or a client Error or Notice message
and send it off to the right places.
3. Discard the ErrorData structure.
4. If severity level < ERROR, return to caller; if = ERROR,
longjmp(Warn_restart) to return control to postgres.c; if > ERROR,
bail out via proc_exit.
This needs some refactoring if we are going to be able to use nested
transactions for trapping errors in functions. In particular, if
the error is to be trapped then it should not be reported to the client
(and probably not to the log either, though I suppose that might be
debatable). Also control had better not bounce all the way out to
postgres.c.
What I propose is that we leave the non-ERROR cases (both NOTICE and
FATAL branches) acting as above. In the ERROR case, though, I think
elog.c should longjmp to Warn_restart just after step 1 is complete,
without having reported the error anywhere. In the simple case where
there is no error trapping involved, the longjmp will still lead back
to postgres.c, and we will add code there that calls back into elog.c
to print out and release the ErrorData info (ie, steps 2 and 3 get
factored out as subroutines that are called from PostgresMain, instead
of being done directly in errfinish).
To implement error trapping, the PL languages will change Warn_restart
to point at their own handlers whenever a TRY-block is running.
What such a handler would normally do is:
* Restore the outer value of Warn_restart. (This must be
done first, to avoid infinite loop if any error happens
in the next steps.)
* Save off a copy of whatever information it wants about
the error. (Probably we should just provide a function
to copy the whole ErrorData structure out of elog.c's
private memory context.)
* Release elog.c's ErrorData structure (step 3 above).
At this point we are out of the critical error-handling
code and able to support a new error.
* Roll back to the savepoint that was established at entry to
the TRY block. This cleans up the rest of the backend's
state to finish recovering from the error.
* Execute whatever error-trapping code the user has provided.
The copied error information would be available for
inspection here.
* Release the copied error information when no longer needed.
This doesn't seem to require any great amount of new code, just
refactoring of code that exists now.
One issue is that it may break existing PLs that override Warn_restart,
since the semantics of doing that will have changed a bit. We can
easily fix the PLs that are in our own CVS, but what are the
implications for other PLs such as PL/R and PL/SH? Joe, Peter, any
comments?
I am somewhat tempted to rename the setjmp variable Warn_restart to
something else, so as to catch any code that is still expecting the
old behavior (besides, it was never a very good name anyway). On the
other hand, there may be cases where a PL's code doesn't actually need
to change, and if so a rename would just break it unnecessarily. Any
votes which way to jump?
regards, tom lane
Are you suggesting these changes for 7.5?
---------------------------------------------------------------------------
Tom Lane wrote:
Currently, the way that ereport/elog processing works is:
1. Collect up all the error parameter information into an ErrorData
structure. (This is somewhat nontrivial in itself, at least in the
ereport case, but I'll gloss over that here.)2. Construct a log message and/or a client Error or Notice message
and send it off to the right places.3. Discard the ErrorData structure.
4. If severity level < ERROR, return to caller; if = ERROR,
longjmp(Warn_restart) to return control to postgres.c; if > ERROR,
bail out via proc_exit.This needs some refactoring if we are going to be able to use nested
transactions for trapping errors in functions. In particular, if
the error is to be trapped then it should not be reported to the client
(and probably not to the log either, though I suppose that might be
debatable). Also control had better not bounce all the way out to
postgres.c.What I propose is that we leave the non-ERROR cases (both NOTICE and
FATAL branches) acting as above. In the ERROR case, though, I think
elog.c should longjmp to Warn_restart just after step 1 is complete,
without having reported the error anywhere. In the simple case where
there is no error trapping involved, the longjmp will still lead back
to postgres.c, and we will add code there that calls back into elog.c
to print out and release the ErrorData info (ie, steps 2 and 3 get
factored out as subroutines that are called from PostgresMain, instead
of being done directly in errfinish).To implement error trapping, the PL languages will change Warn_restart
to point at their own handlers whenever a TRY-block is running.
What such a handler would normally do is:
* Restore the outer value of Warn_restart. (This must be
done first, to avoid infinite loop if any error happens
in the next steps.)
* Save off a copy of whatever information it wants about
the error. (Probably we should just provide a function
to copy the whole ErrorData structure out of elog.c's
private memory context.)
* Release elog.c's ErrorData structure (step 3 above).
At this point we are out of the critical error-handling
code and able to support a new error.
* Roll back to the savepoint that was established at entry to
the TRY block. This cleans up the rest of the backend's
state to finish recovering from the error.
* Execute whatever error-trapping code the user has provided.
The copied error information would be available for
inspection here.
* Release the copied error information when no longer needed.This doesn't seem to require any great amount of new code, just
refactoring of code that exists now.One issue is that it may break existing PLs that override Warn_restart,
since the semantics of doing that will have changed a bit. We can
easily fix the PLs that are in our own CVS, but what are the
implications for other PLs such as PL/R and PL/SH? Joe, Peter, any
comments?I am somewhat tempted to rename the setjmp variable Warn_restart to
something else, so as to catch any code that is still expecting the
old behavior (besides, it was never a very good name anyway). On the
other hand, there may be cases where a PL's code doesn't actually need
to change, and if so a rename would just break it unnecessarily. Any
votes which way to jump?regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Bruce Momjian <pgman@candle.pha.pa.us> writes:
Are you suggesting these changes for 7.5?
Yes. This is an integral part of finishing nested transactions.
regards, tom lane
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Are you suggesting these changes for 7.5?
Yes. This is an integral part of finishing nested transactions.
Oh, is this exceptions in functions or the ability to allow functions to
keep executing after an SQL error?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Bruce Momjian <pgman@candle.pha.pa.us> writes:
Tom Lane wrote:
Yes. This is an integral part of finishing nested transactions.
Oh, is this exceptions in functions or the ability to allow functions to
keep executing after an SQL error?
Those are the same thing, aren't they? But yes, that's the point here.
regards, tom lane
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Tom Lane wrote:
Yes. This is an integral part of finishing nested transactions.
Oh, is this exceptions in functions or the ability to allow functions to
keep executing after an SQL error?Those are the same thing, aren't they? But yes, that's the point here.
So it allows functions to use subtransactions and recover from errors.
I thought that was more than we could do for 7.5 and in fact the release
notes now saw that will be done in a future release.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Tom Lane wrote:
One issue is that it may break existing PLs that override Warn_restart,
since the semantics of doing that will have changed a bit. We can
easily fix the PLs that are in our own CVS, but what are the
implications for other PLs such as PL/R and PL/SH? Joe, Peter, any
comments?
I am somewhat tempted to rename the setjmp variable Warn_restart to
something else, so as to catch any code that is still expecting the
old behavior (besides, it was never a very good name anyway). On the
other hand, there may be cases where a PL's code doesn't actually need
to change, and if so a rename would just break it unnecessarily. Any
votes which way to jump?
It sounds like a good plan, and I'm sure I can adjust either way. Of
course it would be nice if no changes were needed on the PL side unless
they are specifically being changed to take advantage of subtransactions ;-)
Probably the hardest part is to keep the PL code readable while still
supporting cvs tip and 7.4 (and 7.3 for that matter). This is yet
another good example why I think bundling/synchronizing PLs with the
backend is a good thing.
Joe
Bruce Momjian <pgman@candle.pha.pa.us> writes:
So it allows functions to use subtransactions and recover from errors.
I thought that was more than we could do for 7.5 and in fact the release
notes now saw that will be done in a future release.
I think there's only a day or two's work between here and there, and it
would be foolish not to have the feature if we can get it.
As I see it, we need:
1. The elog.c factoring described in this thread.
2. An extension to the SPI API to allow execution of commands within
a subtransaction, with catching of errors.
3. A bit of work on plpgsql to support some kind of EXCEPTION syntax.
I might decide to forget about SPI and trap errors directly in plpgsql,
but in any case it doesn't seem out of reach.
I was just looking around the net to see exactly what Oracle's PL/SQL
syntax is. It doesn't seem too unreasonable syntax-wise:
BEGIN
... controlled statements ...
EXCEPTION
WHEN exception_name THEN
... error handling statements ...
WHEN exception_name THEN
... error handling statements ...
...
WHEN OTHERS THEN
... error handling statements ...
END;
There's nothing here we couldn't do. However, it seems that Oracle
thinks you should throw in explicit SAVEPOINT and ROLLBACK statements
on top of this! That's just weird. It might be that we should
deliberately *not* adopt the exact syntax they are using, just so we
don't create compatibility gotchas.
regards, tom lane
I agree we don't want to add a savepoint on top of the exceptions as you
stated below.
I am _still_ unclear on what still needs to be done to complete NT and
PITR. Are you more aware of the open issues?
---------------------------------------------------------------------------
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
So it allows functions to use subtransactions and recover from errors.
I thought that was more than we could do for 7.5 and in fact the release
notes now saw that will be done in a future release.I think there's only a day or two's work between here and there, and it
would be foolish not to have the feature if we can get it.As I see it, we need:
1. The elog.c factoring described in this thread.
2. An extension to the SPI API to allow execution of commands within
a subtransaction, with catching of errors.3. A bit of work on plpgsql to support some kind of EXCEPTION syntax.
I might decide to forget about SPI and trap errors directly in plpgsql,
but in any case it doesn't seem out of reach.I was just looking around the net to see exactly what Oracle's PL/SQL
syntax is. It doesn't seem too unreasonable syntax-wise:BEGIN
... controlled statements ...
EXCEPTION
WHEN exception_name THEN
... error handling statements ...
WHEN exception_name THEN
... error handling statements ...
...
WHEN OTHERS THEN
... error handling statements ...
END;There's nothing here we couldn't do. However, it seems that Oracle
thinks you should throw in explicit SAVEPOINT and ROLLBACK statements
on top of this! That's just weird. It might be that we should
deliberately *not* adopt the exact syntax they are using, just so we
don't create compatibility gotchas.regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Bruce Momjian <pgman@candle.pha.pa.us> writes:
I am _still_ unclear on what still needs to be done to complete NT and
PITR. Are you more aware of the open issues?
NT: feature-wise, we need to commit the SAVEPOINT-syntax patch, which
I think needs only minor adjustments, and we need to do something with
exception handling in plpgsql, which is what I'm looking at now.
Bug-wise, we had a list of about a dozen bugs and performance problems,
which I think Alvaro is whittling down. IIRC there were not any that
would really be "can't enter beta with this unfixed" things, even if
he can't get them all done by the end of the month.
PITR: I think the only issue that's really on the table is adding
support that lets us reliably identify the starting and ending WAL
offsets associated with a tar dump. The former is needed so DBAs
know how far back they must save WAL segments, and the latter is
needed so that restore can sanity-check stopping-point requests.
This is basically what you wanted to add backup-start and backup-stop
server-side functions to support. I believe Simon is going to take
care of this (if not he'd better speak up).
Other than the spectacular lack of documentation, I don't think we are
in bad shape at all.
regards, tom lane
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
I am _still_ unclear on what still needs to be done to complete NT and
PITR. Are you more aware of the open issues?NT: feature-wise, we need to commit the SAVEPOINT-syntax patch, which
I think needs only minor adjustments, and we need to do something with
exception handling in plpgsql, which is what I'm looking at now.
Bug-wise, we had a list of about a dozen bugs and performance problems,
which I think Alvaro is whittling down. IIRC there were not any that
would really be "can't enter beta with this unfixed" things, even if
he can't get them all done by the end of the month.PITR: I think the only issue that's really on the table is adding
support that lets us reliably identify the starting and ending WAL
offsets associated with a tar dump. The former is needed so DBAs
know how far back they must save WAL segments, and the latter is
needed so that restore can sanity-check stopping-point requests.
This is basically what you wanted to add backup-start and backup-stop
server-side functions to support. I believe Simon is going to take
care of this (if not he'd better speak up).Other than the spectacular lack of documentation, I don't think we are
in bad shape at all.
OK, that helps. Alvaro had a number of items floating around and I
wasn't sure where he was on them. Same with PITR. Once the number of
open items for a project is over about six, I get lost. Is there a
sense you and the patch authors have identified them all and are working
on them so I don't have to worry about them? I don't care if they
aren't done as much as I don't want them to be forgotten.
Agreed on the docs. I should be done the release notes tomorrow and
then the missing doc items can be listed and addressed.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Bruce Momjian <pgman@candle.pha.pa.us> writes:
Tom Lane wrote:
Other than the spectacular lack of documentation, I don't think we are
in bad shape at all.
OK, that helps. Alvaro had a number of items floating around and I
wasn't sure where he was on them. Same with PITR. Once the number of
open items for a project is over about six, I get lost. Is there a
sense you and the patch authors have identified them all and are working
on them so I don't have to worry about them? I don't care if they
aren't done as much as I don't want them to be forgotten.
I think we know what we have to do. Alvaro, Simon, do you feel that
we are on track for beta at the end of the month? Could you post
your current to-do lists for NT and PITR?
regards, tom lane
On Sun, 2004-07-25 at 04:22, Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Tom Lane wrote:
Other than the spectacular lack of documentation, I don't think we are
in bad shape at all.OK, that helps. Alvaro had a number of items floating around and I
wasn't sure where he was on them. Same with PITR. Once the number of
open items for a project is over about six, I get lost. Is there a
sense you and the patch authors have identified them all and are working
on them so I don't have to worry about them? I don't care if they
aren't done as much as I don't want them to be forgotten.I think we know what we have to do. Alvaro, Simon, do you feel that
we are on track for beta at the end of the month? Could you post
your current to-do lists for NT and PITR?
Yes, I feel we are on track for beta.
All known/reported bugs located and solved.
Issue that Create Database doesn't generate redo not addressed, but
lower priority issue.
The TODO list below is about a third of what it was about 8 days ago.
All the refactoring I'd thought about is done, some error pockets
cleaned up - as well as a few investigations that I hadn't had time for.
I'm personally expecting to deliver these before Beta...
1. today (not in SGML)
2. design posted today
PITR TODO for 7.5
=================
HIGH
1. Documentation (sr) (now)
- Backup and Recovery chapter
- WAL chapter
MEDIUM
2. Start/End Backup: Parameter value sanity, e.g.
- is recoveryTargetTime > last checkpoint time
- is recoveryTargetTime > backup end time
- is recoveryTargetXid > last checkpoint xid
- is recoveryTargetXid > backup end txnid
3. Full test plan
- same status as regression tests
- allow re-test of functionality
- confirm viability on all ports
- re-write "example scenarios" into more of a test plan, so people can
see what's been tested and what to test their port against
- test scripts
- test server against those
4. Off-line log inspector (sr) (aug/sept)
- Locate a potential recovery point
- Confirm that the logs have the potential for recovery (to that point)
5. Write logging/redo for resource managers/action types
- Create Database (aug/sept?)
- any others?
LOWER
6. Performance testing
OTHER - related to WAL, but not PITR
7. PreallocXlogFiles
Comments, suggestions and additions welcome.
All assistance in completing the above is most welcome,
--
Best Regards, Simon Riggs
On Sun, 25 Jul 2004, Simon Riggs wrote:
HIGH
1. Documentation (sr) (now)
- Backup and Recovery chapter
- WAL chapter
Docs, as always, can be delivered/improved throughout beta ...
----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Tom Lane wrote:
| Bruce Momjian <pgman@candle.pha.pa.us> writes:
|
|>So it allows functions to use subtransactions and recover from errors.
|>I thought that was more than we could do for 7.5 and in fact the release
|>notes now saw that will be done in a future release.
|
|
| I think there's only a day or two's work between here and there, and it
| would be foolish not to have the feature if we can get it.
|
| As I see it, we need:
|
| 1. The elog.c factoring described in this thread.
|
| 2. An extension to the SPI API to allow execution of commands within
| a subtransaction, with catching of errors.
|
| 3. A bit of work on plpgsql to support some kind of EXCEPTION syntax.
|
| I might decide to forget about SPI and trap errors directly in plpgsql,
| but in any case it doesn't seem out of reach.
|
| I was just looking around the net to see exactly what Oracle's PL/SQL
| syntax is. It doesn't seem too unreasonable syntax-wise:
|
| BEGIN
| ... controlled statements ...
| EXCEPTION
| WHEN exception_name THEN
| ... error handling statements ...
| WHEN exception_name THEN
| ... error handling statements ...
| ...
| WHEN OTHERS THEN
| ... error handling statements ...
| END;
Is this sintax SQL standard driven ?
If not I'd prefere this one:
~ TRY
~ ... controlled statements ...
~ CATCH INTEGER THEN
~ ... error handling statements ...
~ CATCH VARCHAR THEN
~ ... error handling statements ...
~ ...
~ CATCH OTHERS THEN
... error handling statements ...
~ END;
and of course who trhow the exception:
~ THROW 3::INTEGER;
in this way who throw the exception can also transfer informations
on what is going on.
Am I may be not understanding what are you trying to do ?
Regards
Gaetano Mendola
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFBBCsu7UpzwH2SGd4RAo1aAKDUX+afgx+RjBdCtG9sdGp9eT6j1QCfasdq
hcm4Vt2RLsoN5cSvIfbgGiw=
=iJgV
-----END PGP SIGNATURE-----
Marc G. Fournier wrote:
On Sun, 25 Jul 2004, Simon Riggs wrote:
HIGH
1. Documentation (sr) (now)
- Backup and Recovery chapter
- WAL chapterDocs, as always, can be delivered/improved throughout beta ...
Yes, but without PITR docs there is no way to test the feature.
Many of our docs don't require docs for testing, but PITR does.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073On Sun, 25 Jul 2004, Bruce Momjian wrote:
Marc G. Fournier wrote:
On Sun, 25 Jul 2004, Simon Riggs wrote:
HIGH
1. Documentation (sr) (now)
- Backup and Recovery chapter
- WAL chapterDocs, as always, can be delivered/improved throughout beta ...
Yes, but without PITR docs there is no way to test the feature.
Many of our docs don't require docs for testing, but PITR does.
Shouldn't there be regression tests for doing that anyway? Or isn't that
possible to do in the regression tests?
----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
Marc G. Fournier wrote:
On Sun, 25 Jul 2004, Bruce Momjian wrote:
Marc G. Fournier wrote:
On Sun, 25 Jul 2004, Simon Riggs wrote:
HIGH
1. Documentation (sr) (now)
- Backup and Recovery chapter
- WAL chapterDocs, as always, can be delivered/improved throughout beta ...
Yes, but without PITR docs there is no way to test the feature.
Many of our docs don't require docs for testing, but PITR does.Shouldn't there be regression tests for doing that anyway? Or isn't that
possible to do in the regression tests?
Good question. Simon wants to build a test suite. It appeared on his
TODO list. However, I think it would be separate from the regression
tests.
Also, we need manual testing because people will do goofy things that
the test will not try, I am sure. :-)
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073On Sun, 25 Jul 2004, Bruce Momjian wrote:
Also, we need manual testing because people will do goofy things that
the test will not try, I am sure. :-)
Agreed, but just want to make sure that Simon realizes that this 'level'
of documentation is required for release, not for beta ... in fact, be
nice if we could use those 'goofy things' as a pseudo example of where
PITR is useful ...
----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
Marc G. Fournier wrote:
On Sun, 25 Jul 2004, Bruce Momjian wrote:
Also, we need manual testing because people will do goofy things that
the test will not try, I am sure. :-)Agreed, but just want to make sure that Simon realizes that this 'level'
of documentation is required for release, not for beta ... in fact, be
nice if we could use those 'goofy things' as a pseudo example of where
PITR is useful ...
Agreed. We don't need eligance at this stage, just instructions for
people to follow. Simon, don't kill yourself on the docs.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073On Mon, 2004-07-26 at 02:12, Marc G. Fournier wrote:
On Sun, 25 Jul 2004, Bruce Momjian wrote:
Also, we need manual testing because people will do goofy things that
the test will not try, I am sure. :-)Agreed, but just want to make sure that Simon realizes that this 'level'
of documentation is required for release, not for beta ... in fact, be
nice if we could use those 'goofy things' as a pseudo example of where
PITR is useful ...
I think you're arguing in favour of me not completing the todo list,
which is great cos I wasn't going to be able to. :)
...and we can do more later.
Best Regards, Simon Riggs
Gaetano Mendola <mendola@bigfoot.com> writes:
Tom Lane wrote:
| I was just looking around the net to see exactly what Oracle's PL/SQL
| syntax is. It doesn't seem too unreasonable syntax-wise:
| [ snip pl/sql syntax ]
Is this sintax SQL standard driven ?
No, AFAIK it's just Oracle's syntax.
If not I'd prefere this one:
[ some other syntax ]
Can you point to any SQL standard or existing database that uses your
suggestion? Oracle is certainly the de facto standard in this area,
and plpgsql in particular is an unabashed effort to follow their PL/SQL
implementation...
If we decide that we're going to deliberately vary from Oracle's syntax
and semantics, then I have no problem with try/catch as the keywords.
(That's actually my programming heritage as well, I was using exception
handling with those keywords back in the late 70s at HP.)
~ CATCH INTEGER THEN
~ ... error handling statements ...
~ CATCH VARCHAR THEN
er ... I'm not clear why type names would have anything to do with
exceptions. What's your vision here exactly?
regards, tom lane
I was just looking around the net to see exactly what Oracle's PL/SQL
syntax is. It doesn't seem too unreasonable syntax-wise:BEGIN
... controlled statements ...
EXCEPTION
WHEN exception_name THEN
... error handling statements ...
WHEN exception_name THEN
... error handling statements ...
...
WHEN OTHERS THEN
... error handling statements ...
END;There's nothing here we couldn't do. However, it seems that Oracle
thinks you should throw in explicit SAVEPOINT and ROLLBACK statements
on top of this! That's just weird. It might be that we should
deliberately *not* adopt the exact syntax they are using, just so we
don't create compatibility gotchas.
That is because they usually use this to handle the exception of only one
potentially failing statement, which does not rollback any prev statements
(except in pg).
Thus in Oracle you need savepoints in a lot fewer cases. It is only in those seldom
cases, that you add savepoints on top of blocks with exceptions in Oracle.
But yes, I think doing implicit savepoints for plpgsql blocks that contain an
"exception ..." handler is absolutely the way to proceed. For maximum protability
that savepoint should probably travel along with every successful statement after
the BEGIN (that of course is debateable).
BEGIN
--implicit savepoint "x"
update 1 -- succeeds
--implicit release old "x", new savepoint "x"
update 2 -- fails
--position "y"
update 3 -- succeeds
EXCEPTION -- rollback to savepoint "x"
WHEN ....
-- transfer control to position "y"
END; -- implicit RELEASE savepoint "x"
Doing this only for blocks with an exception handler would not impose any overhead
for conventional plpgsql funcs.
Andreas
PS: can someone please help me get through the lists spam blocker, get Marc to contact me,
or I don't know what else I can do
Import Notes
Resolved by subject fallback
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Tom Lane wrote:
| Gaetano Mendola <mendola@bigfoot.com> writes:
|
|>Tom Lane wrote:
|>| I was just looking around the net to see exactly what Oracle's PL/SQL
|>| syntax is. It doesn't seem too unreasonable syntax-wise:
|>| [ snip pl/sql syntax ]
|
|
|>Is this sintax SQL standard driven ?
|
|
| No, AFAIK it's just Oracle's syntax.
|
|
|>If not I'd prefere this one:
|> [ some other syntax ]
|
|
| Can you point to any SQL standard or existing database that uses your
| suggestion? Oracle is certainly the de facto standard in this area,
| and plpgsql in particular is an unabashed effort to follow their PL/SQL
| implementation...
I didn't know we where following the Oracle syntax indeed.
|
| er ... I'm not clear why type names would have anything to do with
| exceptions. What's your vision here exactly?
|
| regards, tom lane
Because I believe that exception handler need to have some informations
in order to handle the exception and only the exception_name is not enough,
so who generate the exception can "throw" a type that the handler can use
( I have in mind the C++/Java exception handler ):
CATCH INTEGER THEN
~ < Work with the integer $1 in order to manage what is going on >
CATCH VARCHAR THEN
~ < Insert in the logs table the message $1 >
...
but may be I'm completely missing the target that you are going to achieve.
Regards
Gaetano Mendola
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFBBMsf7UpzwH2SGd4RAqM1AKCSg4jOT7J52nZRU98Np/JAIcAz4wCbB/O3
J94yQ7NFB/JE3uUCR/OgkUs=
=oWOX
-----END PGP SIGNATURE-----
Am Sonntag, 25. Juli 2004 01:48 schrieb Tom Lane:
One issue is that it may break existing PLs that override Warn_restart,
since the semantics of doing that will have changed a bit. We can
easily fix the PLs that are in our own CVS, but what are the
implications for other PLs such as PL/R and PL/SH? Joe, Peter, any
comments?
PL/sh is OK, but both PL/Ruby and PL/Java play around with Warn_restart.
I am somewhat tempted to rename the setjmp variable Warn_restart to
something else, so as to catch any code that is still expecting the
old behavior (besides, it was never a very good name anyway). On the
other hand, there may be cases where a PL's code doesn't actually need
to change, and if so a rename would just break it unnecessarily. Any
votes which way to jump?
Maybe the authors (in Cc) can comment what support they need.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
Peter Eisentraut <peter_e@gmx.net> writes:
PL/sh is OK, but both PL/Ruby and PL/Java play around with Warn_restart.
Are they using it to fake try/catch behavior? If so, see later thread
about moving to a simple set of try/catch macros.
I currently have this up and seemingly working for the main backend,
but I still need to look at revising pltcl and plpython to match.
I think that will be straightforward, but you never know till it's
done...
regards, tom lane
Tom Lane wrote:
Peter Eisentraut <peter_e@gmx.net> writes:
PL/sh is OK, but both PL/Ruby and PL/Java play around with Warn_restart.
Are they using it to fake try/catch behavior? If so, see later thread
about moving to a simple set of try/catch macros.I currently have this up and seemingly working for the main backend,
but I still need to look at revising pltcl and plpython to match.
I think that will be straightforward, but you never know till it's
done...regards, tom lane
I'm using macros to fake try/catch behavior in PL/Java. I see no
immedate problem switching to a supported version of the macros.
Regards,
Thomas Hallgren