Re: Anybody have an Oracle PL/SQL reference at hand?

Can anyone check how well the syntax of plpgsql EXCEPTION, as described
at
http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
agrees with what Oracle does? I did some googling but couldn't find
anything that seemed authoritative. I'm wondering in particular if
Oracle allows multiple condition names per WHEN, along the lines of
WHEN condition [ , condition ... ] THEN
handler_statements

Also it would be nice to see a complete list of the "condition" names
that they accept. I whipped up a quick table based on our ERRCODE
macro names, see
http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/pl/plpgsql/src/plerrcodes.h
but I'm certain that's not what we really want to expose to users
in the long run.

regards, tom lane

Tom Lane wrote:

>Can anyone check how well the syntax of plpgsql EXCEPTION, as described
>at
>http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
>agrees with what Oracle does? I did some googling but couldn't find
>anything that seemed authoritative. I'm wondering in particular if
>Oracle allows multiple condition names per WHEN, along the lines of
> WHEN condition [ , condition ... ] THEN
> handler_statements
>
>Also it would be nice to see a complete list of the "condition" names
>that they accept. I whipped up a quick table based on our ERRCODE
>macro names, see
>http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/pl/plpgsql/src/plerrcodes.h
>but I'm certain that's not what we really want to expose to users
>in the long run.
>
>

It appears you can have multiple exceptions in a single handler, but
that the separator is 'OR' rather than ','.

See
http://www.cise.ufl.edu/help/database/oracle-docs/appdev.920/a96624/13_elems17.htm

The page also gives a link to a list of the predefined exceptions.
PL/SQL allows users to define and raise their own exceptions too.

cheers

andrew

Hello,

From I can tell from Oracle pl/SQL programming page 130 ;) it is
identical. However Oracle does have thinkgs like EXCEPTION_INIT.

Here are the name of the Oracle predefined exceptions:

CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
INVALID_CURSOR
INVALID_NUMBER
LOGIN_DENIED
NO_DATA_FOUND
NOT_LOGGED_IN
PROGRAM_ERROR
STORAGE_ERROR
TIMEOUT_ON_RESOURCE
TOO_MANY_ROWS
TRANSACTION_BACKED_OUT
VALUE_ERROR

Sincerely,

Joshua D. Drake

Tom Lane wrote:
> Can anyone check how well the syntax of plpgsql EXCEPTION, as described
> at
> http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
> agrees with what Oracle does? I did some googling but couldn't find
> anything that seemed authoritative. I'm wondering in particular if
> Oracle allows multiple condition names per WHEN, along the lines of
> WHEN condition [ , condition ... ] THEN
> handler_statements
>
> Also it would be nice to see a complete list of the "condition" names
> that they accept. I whipped up a quick table based on our ERRCODE
> macro names, see
> http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/pl/plpgsql/src/plerrcodes.h
> but I'm certain that's not what we really want to expose to users
> in the long run.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd(at)commandprompt(dot)com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Tom Lane wrote:
>> Can anyone check how well the syntax of plpgsql EXCEPTION, as described
>> at
>> http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
>> agrees with what Oracle does?

> It appears you can have multiple exceptions in a single handler, but
> that the separator is 'OR' rather than ','.
> See
> http://www.cise.ufl.edu/help/database/oracle-docs/appdev.920/a96624/13_elems17.htm

Got it. We can do that.

> The page also gives a link to a list of the predefined exceptions.

Hmm. Not only is that a pretty short list, but many of them don't
correspond very closely to the errors that Postgres would raise.
Maybe we should just forget about trying to be compatible with Oracle's
condition names. That still leaves us with needing to decide what our
condition names should be, though.

One thing that just occurred to me is that we should accept the category
names (in errcodes.h, the exceptions with '000' as the last three digits
of the code) as matching any exception in their category.

> PL/SQL allows users to define and raise their own exceptions too.

Yeah. Our RAISE statement really needs a lot of work, but I think it
will have to be left for a future release cycle ...

regards, tom lane

Hi Tom,

I have sent you and the list the HTML page from the oracle tech network
describing all of this. However, it seems to have disappeared in to the
void since you don't seem to have received it and it hasn't hit the list
yet.

You can get a free login to access all the oracle docs and manuals, here:

http://otn.oracle.com/pls/db10g/portal.portal_demo3?selected=1

If you like, however, I can just zip and email you the full PL/SQL PDF
manual that I just downloaded from the above URL.

Let me know if you want the PDF emailed to you.

> Can anyone check how well the syntax of plpgsql EXCEPTION, as described
> at
> http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
> agrees with what Oracle does? I did some googling but couldn't find
> anything that seemed authoritative. I'm wondering in particular if
> Oracle allows multiple condition names per WHEN, along the lines of
> WHEN condition [ , condition ... ] THEN
> handler_statements

It does, but with a different syntax:

---
If you want two or more exceptions to execute the same sequence of
statements, list the exception names in the WHEN clause, separating them
by the keyword OR, as follows:

EXCEPTION
WHEN over_limit OR under_limit OR VALUE_ERROR THEN
-- handle the error

If any of the exceptions in the list is raised, the associated sequence
of statements is executed. The keyword OTHERS cannot appear in the list
of exception names; it must appear by itself. You can have any number of
exception handlers, and each handler can associate a list of exceptions
with a sequence of statements. However, an exception name can appear
only once in the exception-handling part of a PL/SQL block or subprogram.
---

> Also it would be nice to see a complete list of the "condition" names
> that they accept. I whipped up a quick table based on our ERRCODE
> macro names, see
> http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/pl/plpgsql/src/plerrcodes.h
> but I'm certain that's not what we really want to expose to users
> in the long run.

--
ACCESS_INTO_NULL
A program attempts to assign values to the attributes of an
uninitialized object.

CASE_NOT_FOUND
None of the choices in the WHEN clauses of a CASE statement is
selected, and there is no ELSE clause.

COLLECTION_IS_NULL
A program attempts to apply collection methods other than EXISTS to
an uninitialized nested table or varray, or the program attempts to
assign values to the elements of an uninitialized nested table or varray.

CURSOR_ALREADY_OPEN
A program attempts to open an already open cursor. A cursor must be
closed before it can be reopened. A cursor FOR loop automatically opens
the cursor to which it refers, so your program cannot open that cursor
inside the loop.

DUP_VAL_ON_INDEX
A program attempts to store duplicate values in a database column
that is constrained by a unique index.

INVALID_CURSOR
A program attempts a cursor operation that is not allowed, such as
closing an unopened cursor.

INVALID_NUMBER
In a SQL statement, the conversion of a character string into a
number fails because the string does not represent a valid number. (In
procedural statements, VALUE_ERROR is raised.) This exception is also
raised when the LIMIT-clause expression in a bulk FETCH statement does
not evaluate to a positive number.

LOGIN_DENIED
A program attempts to log on to Oracle with an invalid username or
password.

NO_DATA_FOUND
A SELECT INTO statement returns no rows, or your program references a
deleted element in a nested table or an uninitialized element in an
index-by table. Because this exception is used internally by some SQL
functions to signal that they are finished, you should not rely on this
exception being propagated if you raise it within a function that is
called as part of a query.

NOT_LOGGED_ON
A program issues a database call without being connected to Oracle.

PROGRAM_ERROR
PL/SQL has an internal problem.

ROWTYPE_MISMATCH
The host cursor variable and PL/SQL cursor variable involved in an
assignment have incompatible return types. For example, when an open
host cursor variable is passed to a stored subprogram, the return types
of the actual and formal parameters must be compatible.

SELF_IS_NULL
A program attempts to call a MEMBER method, but the instance of the
object type has not been initialized. The built-in parameter SELF points
to the object, and is always the first parameter passed to a MEMBER method.

STORAGE_ERROR
PL/SQL runs out of memory or memory has been corrupted.

SUBSCRIPT_BEYOND_COUNT
A program references a nested table or varray element using an index
number larger than the number of elements in the collection.

SUBSCRIPT_OUTSIDE_LIMIT
A program references a nested table or varray element using an index
number (-1 for example) that is outside the legal range.

SYS_INVALID_ROWID
The conversion of a character string into a universal rowid fails
because the character string does not represent a valid rowid.

TIMEOUT_ON_RESOURCE
A time-out occurs while Oracle is waiting for a resource.

TOO_MANY_ROWS
A SELECT INTO statement returns more than one row.

VALUE_ERROR
An arithmetic, conversion, truncation, or size-constraint error
occurs. For example, when your program selects a column value into a
character variable, if the value is longer than the declared length of
the variable, PL/SQL aborts the assignment and raises VALUE_ERROR. In
procedural statements, VALUE_ERROR is raised if the conversion of a
character string into a number fails. (In SQL statements, INVALID_NUMBER
is raised.)

ZERO_DIVIDE
A program attempts to divide a number by zero.
--

Chris

On Sat, Jul 31, 2004 at 01:43:25PM -0400, Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> > Tom Lane wrote:
> >> Can anyone check how well the syntax of plpgsql EXCEPTION, as described
> >> at
> >> http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
> >> agrees with what Oracle does?
>
> > It appears you can have multiple exceptions in a single handler, but
> > that the separator is 'OR' rather than ','.
> > See
> > http://www.cise.ufl.edu/help/database/oracle-docs/appdev.920/a96624/13_elems17.htm
>
> Got it. We can do that.
>
> > The page also gives a link to a list of the predefined exceptions.
>
> Hmm. Not only is that a pretty short list, but many of them don't
> correspond very closely to the errors that Postgres would raise.
> Maybe we should just forget about trying to be compatible with Oracle's
> condition names. That still leaves us with needing to decide what our
> condition names should be, though.
>
> One thing that just occurred to me is that we should accept the category
> names (in errcodes.h, the exceptions with '000' as the last three digits
> of the code) as matching any exception in their category.
>
> > PL/SQL allows users to define and raise their own exceptions too.
>
> Yeah. Our RAISE statement really needs a lot of work, but I think it
> will have to be left for a future release cycle ...
>

Oracle defines very few named exceptions. Instead, the intention is that
you define a name for a numeric exception and use it yourself.

CREATE OR REPLACE PACKAGE symer AS

en_missing_data CONSTANT NUMBER := -20999;
exc_missing_data EXCEPTION;
PRAGMA EXCEPTION_INIT (exc_missing_data, -20999);

END;

--
Jim C. Nasby, Database Consultant jim(at)nasby(dot)net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

"Jim C. Nasby" <decibel(at)decibel(dot)org> writes:
> Oracle defines very few named exceptions. Instead, the intention is that
> you define a name for a numeric exception and use it yourself.

Yeah, I noticed that. It seems a spectacularly bad idea :-(. What
redeeming social value has it got? AFAICS there are no upsides, only
downsides: you might get the numeric code wrong, and never know it
until your code fails in the field; and even if you always get it
right, having every bit of code invent its own random name for the
same exception doesn't seem like it does anything for readability or
maintainability.

In any case we use SQLSTATEs not SQLCODEs, so we have no hope of being
compatible with Oracle at that level.

regards, tom lane

On Mon, 2 Aug 2004, Tom Lane wrote:

> "Jim C. Nasby" <decibel(at)decibel(dot)org> writes:
> > Oracle defines very few named exceptions. Instead, the intention is that
> > you define a name for a numeric exception and use it yourself.
>
> Yeah, I noticed that. It seems a spectacularly bad idea :-(. What
> redeeming social value has it got? AFAICS there are no upsides, only
> downsides: you might get the numeric code wrong, and never know it
> until your code fails in the field; and even if you always get it
> right, having every bit of code invent its own random name for the
> same exception doesn't seem like it does anything for readability or
> maintainability.

I agree with you that forcing users to declare names for SQLCODEs is not
such a great idea. What I do like, however, is the ability to declare your
own exceptions. For example:

DECLARE
invalid_sale EXCEPTION;
BEGIN
...
IF saleid < 0 THEN
RAISE EXCEPTION invalid_sale;
END IF;

...
IF price < '0.00' THEN
RAISE EXCEPTION invalid_sale;
END IF;
...

EXCEPTION
WHEN invalid_sale THEN
...
END;

This is essentially using the exception system for as a goto mechanism,
which usually I wouldn't like except for the problems created when you
have large PL/PgSQL blocks which may encounter the same conditions in
different parts of the block.

This will also be useful because people will want to emulate Oracle PL/SQL
behaviour of generating an exception if is generated when a SELECT INTO
returns no rows. So, they could do:

SELECT INTO myvar ...
IF NOT FOUND THEN
RAISE EXCEPTION NO_DATA_FOUND;
END IF

I also took a look at the Oracle PL/SQL exceptions in 10g. There are only
21 of them people have much finer granularity with PL/PgSQL. The problem
is that I'd imagine that I'd a lot of PL/SQL code captures the exception
VALUE_ERROR (which seems to cover all of SQLSTATE Class 22 it seems). This
would be a special case to the excecption label map.

There is also the STORAGE_ERROR exception which covers
ERRCODE_OUT_OF_MEMORY, ERRCODE_DISK_FULL, ERRCODE_INSUFFICIENT_RESOURCES,
ERRCODE_IO_ERROR and ERRCODE_DATA_CORRUPTED (!!).

There is also INVALID_CURSOR, which basically covers all the cursor
errors.

I have no evidence that these exceptions are in wide use so, maybe its not
a problem at all.

Anyway, I've attached a patch which adds a few more labels for existing
SQLSTATE error codes where there is a one-to-one mapping from PostgreSQL
to Oracle.

Having now added these new exception labels, and given that there are some
errors not supported as exceptions from within PL/PgSQL (success,
warnings, etc), perhaps should generate our own list of error codes within
the PL/PgSQL documentation by looking at plerrcodes.h ?

Just some thoughts...

Gavin

One other difference when compared with Oracle is that Oracle does not
abort the transaction which raised the exception. Although I generally do
not think this is a great idea, it does allow for things like retry loops.
Assuming we have savepoints, consider the following function which creates
a user account

DECLARE
suffix int;
BEGIN
suffix := 1;
LOOP
BEGIN;
SAVEPOINT start;
INSERT INTO users VALUES(user || suffix);
EXIT;
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
ROLLBACK TO start;
suffix := suffix + 1;
END;
END LOOP;
END;

Again, it might not be great to leave the database in an inconsistent
state when we get to the exception handler and I'd be all for generating
another exception if the (sub) transaction was not rolled back and the
exception handler tried to access data. Just some ideas.

Gavin

Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
> I agree with you that forcing users to declare names for SQLCODEs is not
> such a great idea. What I do like, however, is the ability to declare your
> own exceptions. For example:

Agreed, that would be a good thing to have, but I think it's something
we'll have to leave for the next release cycle.

> I also took a look at the Oracle PL/SQL exceptions in 10g. There are only
> 21 of them people have much finer granularity with PL/PgSQL. The problem
> is that I'd imagine that I'd a lot of PL/SQL code captures the exception
> VALUE_ERROR (which seems to cover all of SQLSTATE Class 22 it seems).

Isn't this covered by our provision for catching whole classes? WHEN
DATA_EXCEPTION THEN catches all of class 22, for example.

> Anyway, I've attached a patch which adds a few more labels for existing
> SQLSTATE error codes where there is a one-to-one mapping from PostgreSQL
> to Oracle.

I had originally gone into this with the idea of duplicating their
labels, but I'm now of the opinion that it's a bad idea and we shouldn't
have just partial agreement. That would just lull porters of Oracle
code into thinking they didn't need to review their exception labels.
So, yeah, it would be easy to offer ZERO_DIVIDE as an accepted spelling,
but will that really improve anyone's life?

This leads into another thing I wanted to have some discussion about.
The way the plpgsql exception code is currently written, it doesn't
notice or complain about bad labels. For instance you could write
WHEN FOOBAR THEN and you'd never get a complaint; the corresponding
handler code would simply never be entered. This is probably bad.
How tense do we need to be about detecting bad condition labels?

Any opinions out there about what to do about these points?

> Having now added these new exception labels, and given that there are some
> errors not supported as exceptions from within PL/PgSQL (success,
> warnings, etc), perhaps should generate our own list of error codes within
> the PL/PgSQL documentation by looking at plerrcodes.h ?

I think referencing Appendix A is the way to do it.

regards, tom lane

Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:

> BEGIN;
> SAVEPOINT start;
> INSERT INTO users VALUES(user || suffix);
> EXIT;
> EXCEPTION
> WHEN UNIQUE_VIOLATION THEN
> ROLLBACK TO start;
> suffix := suffix + 1;
> END;

Right. Essentially, our implementation is supplying the SAVEPOINT and
ROLLBACK TO commands implicitly as part of any block with an EXCEPTION
clause. When we get around to updating the "Oracle porting" guide in
the plpgsql docs, this will need to be clearly explained.

Depending on how tense you want to be about Oracle compatibility, we
could make people actually write their blocks as above --- that is,
the SAVEPOINT and ROLLBACK commands would be a required part of the
exception-block syntax. They wouldn't actually *do* anything, but
they would make the code look more like its Oracle equivalent. I'm not
for this, but maybe someone wants to make the case for it?

regards, tom lane

The upsides, as I see them:

They use one system for handling all exceptions, user generated or not.

They didn't come up with their own arbitrary names for all the error
codes they have. Naming an exception follows all the namespace rules;
for example, the exception code example I gave was in the 'symer'
package, so you would reference all those execptions as:

EXCEPTION
WHEN symer.some_error_name_I_came_up_with THEN

As for waiting for the code to fail in the field, I'm not sure that
really applies... I only trap exceptions that I know might happen and
have some specific way to deal with, otherwise I let them percolate up
the call stack.

Looking at the PGSQL side, I think using one system for both system and
user defined errors is a good thing. Right now I don't know that PGSQL
has any concept of different user defined error codes, but that's
something that might be usefull. It allows an application to trap your
errors based on an error code, instead of depending on parsing error
text.

Oracle has defined probably 10,000 or more error codes, which is why it
would be impractical for them to come up with a definative name for each
one. PostgreSQL isn't at that stage, so it's not as big an issue. But if
one error handling system is created for both internal and
'user-defined' errors then it would be possible to set aside chunks of
error codes for gborg projects, for example. This would allow them to
document the errors that might occure specific to their code. Oracle
kind of does this in two ways.. they segment the error numbers, and they
also prefix each error with a three leter acronym indicating what
general system the error is from.

As anxious as I am to have error trapping capability in plpgsql, it
might be good to consider possible future uses before deciding on a
system to handle it.

On Mon, Aug 02, 2004 at 09:24:29PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <decibel(at)decibel(dot)org> writes:
> > Oracle defines very few named exceptions. Instead, the intention is that
> > you define a name for a numeric exception and use it yourself.
>
> Yeah, I noticed that. It seems a spectacularly bad idea :-(. What
> redeeming social value has it got? AFAICS there are no upsides, only
> downsides: you might get the numeric code wrong, and never know it
> until your code fails in the field; and even if you always get it
> right, having every bit of code invent its own random name for the
> same exception doesn't seem like it does anything for readability or
> maintainability.
>
> In any case we use SQLSTATEs not SQLCODEs, so we have no hope of being
> compatible with Oracle at that level.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On Tue, Aug 03, 2004 at 10:17:14AM -0400, Tom Lane wrote:
> Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
>
> > BEGIN;
> > SAVEPOINT start;
> > INSERT INTO users VALUES(user || suffix);
> > EXIT;
> > EXCEPTION
> > WHEN UNIQUE_VIOLATION THEN
> > ROLLBACK TO start;
> > suffix := suffix + 1;
> > END;
>
> Right. Essentially, our implementation is supplying the SAVEPOINT and
> ROLLBACK TO commands implicitly as part of any block with an EXCEPTION
> clause. When we get around to updating the "Oracle porting" guide in
> the plpgsql docs, this will need to be clearly explained.
>
> Depending on how tense you want to be about Oracle compatibility, we
> could make people actually write their blocks as above --- that is,
> the SAVEPOINT and ROLLBACK commands would be a required part of the
> exception-block syntax. They wouldn't actually *do* anything, but
> they would make the code look more like its Oracle equivalent. I'm not
> for this, but maybe someone wants to make the case for it?
>
> regards, tom lane

If it's not difficult it would probably be good to allow for handling
the rollback yourself. In this example it wouldn't matter; the row
triggering an error won't be inserted. But if you were inserting data
from a multi-row source such as a temporary table it would make a
difference.

By the way, while I know Oracle won't abort the transaction, they might
rollback whatever work the command that failed had done; I'm not really
sure how that's handled.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

"Jim C. Nasby" <decibel(at)decibel(dot)org> writes:
> On Tue, Aug 03, 2004 at 10:17:14AM -0400, Tom Lane wrote:
>> Right. Essentially, our implementation is supplying the SAVEPOINT and
>> ROLLBACK TO commands implicitly as part of any block with an EXCEPTION
>> clause. When we get around to updating the "Oracle porting" guide in
>> the plpgsql docs, this will need to be clearly explained.

> If it's not difficult it would probably be good to allow for handling
> the rollback yourself.

We're not doing that. This is a server-side function we're talking
about: it is executing *inside* the transaction that you want it to fool
with the status of. If you want logic that can issue SAVEPOINT and
ROLLBACK at arbitrary points, code it on the client side.

Oracle seems to have defined PL/SQL as though the function code executes
outside the database engine, which is kind of an odd way to look at it
IMHO. But even if you think it's just the right thing, we can't support
it with anything approximating our current design for PLs.

regards, tom lane

> Depending on how tense you want to be about Oracle compatibility, we
> could make people actually write their blocks as above --- that is,
> the SAVEPOINT and ROLLBACK commands would be a required part of the
> exception-block syntax. They wouldn't actually *do* anything, but
> they would make the code look more like its Oracle equivalent. I'm not
> for this, but maybe someone wants to make the case for it?

So long as I can emulate SQL MERGE with it, I'm happy. ie. I need a
solution to the 'try update, if no rows changed then insert (unique
index)' common race condition issue. If I cannot keep looping that
until it succeeds, then exceptions don't help me...

Chris

>>
>>
>>> BEGIN;
>>> SAVEPOINT start;
>>> INSERT INTO users VALUES(user || suffix);
>>> EXIT;
>>> EXCEPTION
>>> WHEN UNIQUE_VIOLATION THEN
>>> ROLLBACK TO start;
>>> suffix := suffix + 1;
>>> END;

> By the way, while I know Oracle won't abort the transaction, they might
> rollback whatever work the command that failed had done; I'm not really
> sure how that's handled.

How about a new EXCEPTION clause:

EXCEPTION NO ROLLBACK
WHEN UNIQUE...

Chirs

On Tue, 3 Aug 2004, Tom Lane wrote:

> "Jim C. Nasby" <decibel(at)decibel(dot)org> writes:
> > On Tue, Aug 03, 2004 at 10:17:14AM -0400, Tom Lane wrote:
> >> Right. Essentially, our implementation is supplying the SAVEPOINT and
> >> ROLLBACK TO commands implicitly as part of any block with an EXCEPTION
> >> clause. When we get around to updating the "Oracle porting" guide in
> >> the plpgsql docs, this will need to be clearly explained.
>
> > If it's not difficult it would probably be good to allow for handling
> > the rollback yourself.
>
> We're not doing that. This is a server-side function we're talking
> about: it is executing *inside* the transaction that you want it to fool
> with the status of. If you want logic that can issue SAVEPOINT and
> ROLLBACK at arbitrary points, code it on the client side.

Just another take on this: a lot of PL/SQL I've seen uses the EXCEPTIONs
block simply to output strings describing the exception. That is:

EXCEPTION
WHEN foo THEN
RAISE NOTICE 'You cannot foo';
WHEN bar THEN
RAISE NOTICE 'You cannot bar';
END;

In this case, no exception handler is accessing or modifying SQL data.
Would it be worth trying to identify these situations so that we can avoid
subtransaction overhead?

Gavin

On Wed, Aug 04, 2004 at 09:46:22AM +0800, Christopher Kings-Lynne wrote:
> >Depending on how tense you want to be about Oracle compatibility, we
> >could make people actually write their blocks as above --- that is,
> >the SAVEPOINT and ROLLBACK commands would be a required part of the
> >exception-block syntax. They wouldn't actually *do* anything, but
> >they would make the code look more like its Oracle equivalent. I'm not
> >for this, but maybe someone wants to make the case for it?
>
> So long as I can emulate SQL MERGE with it, I'm happy. ie. I need a
> solution to the 'try update, if no rows changed then insert (unique
> index)' common race condition issue. If I cannot keep looping that
> until it succeeds, then exceptions don't help me...

Honestly, I'd *love* to see a merge command built in, assuming it's not
very difficult. I would think that having the database handle this
internally would be much more performant than using pgsql for it.

> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"