Syntax decisions for pl/pgsql RAISE extension

I've started to look over Pavel's revised RAISE patch
http://archives.postgresql.org/pgsql-patches/2008-05/msg00187.php
and I've got a few quibbles with the syntax choices.

Pavel proposes extending RAISE like this:

RAISE level 'format' [, expression [, ...] ] [ USING ( option = value [, ... ] ) ]

the part before USING being what we had already. Each "option" keyword
is one of SQLSTATE, CONDITION, DETAIL, or HINT, and each "value" is a
string-valued expression. SQLSTATE takes a value like '22012' while the
(mutually exclusive) CONDITION takes a value like 'DIVISION_BY_ZERO'.
DETAIL and HINT allow those parts of an error report to be filled in.

I'd like to propose the following changes:

1. The parentheses around the USING list seem useless; let's drop 'em.

2. I think the separation between SQLSTATE and CONDITION is just
complication. A SQLSTATE is required to be exactly 5 digits and/or
upper case ASCII letters; I see no realistic prospect that any condition
name would ever look like a SQLSTATE (and we could certainly adjust
condition names to prevent it, if anyone would make such an unhappy
choice). So I think we could unify these options into one. I think
CODE might be a better choice for the option name than SQLSTATE (since
the latter already has a meaning in pl/pgsql, ie the function that
gives you the code for the currently thrown error) --- thoughts?

3. I think we should allow the user to specify the error message the
same way as the other options, that is
RAISE level USING MESSAGE = string_expression [ , ... ]
The %-format business has always struck me as a bit weird, and it's
much more so if we aren't handling the other error report components
in the same fashion. So we ought to provide an alternative that's
more uniform.

Now, the elephant in the room is the issue of Oracle compatibility.
None of this looks anything even a little bit like Oracle's RAISE
command. Oracle allows
RAISE exception_name ;
RAISE ;
where the second case is allowed only in an EXCEPTION handler and
means to re-throw the current error. I think the latter is a very
good idea and we ought to support it. Right now there's no way to
re-throw an error without information loss, and that'll get a lot
worse with these additions to what RAISE can throw. I'm less
excited about the condition-name-only syntax; that seems awfully
impoverished given the lack of any way to supply a specific error
message or data values. Still, we could imagine people wanting
something like
RAISE condition_name USING message = string_expression
where the condition_name would substitute for the CODE option.
I think we could support this as long as the condition name were
given as an exception name rather than a string literal (otherwise
it looks too much like our legacy syntax). Comments? Is anyone
excited about that one way or the other?

Lastly: to allow users to catch errors thrown with user-defined
SQLSTATEs, Pavel proposes extending the syntax of EXCEPTION WHEN lists
so that an error code can be specified in either of these styles:
DIVISION_BY_ZERO
SQLSTATE 22012
I find the second style rather weird, and I think it probably doesn't
even work for cases like 2201F (which isn't going to get lexed as
a single token). I would suggest a quoted literal and drop the noise
word, so that the alternatives are
DIVISION_BY_ZERO
'22012'
Comments?

If we can get some consensus I'll undertake to adjust the patch
accordingly.

regards, tom lane

>>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Now, the elephant in the room is the issue of Oracle compatibility.
> None of this looks anything even a little bit like Oracle's RAISE
> command. Oracle allows
> RAISE exception_name ;
> RAISE ;

I'm probably in the minority, but I care more about SQL/PSM
compatibility than Oracle compatibility. I would hope that the ISO
standard is at least a gorilla sitting in the corner of the room.

If it's not too impractical, a nod toward these would be good:

DECLARE condition-name CONDITION FOR SQLSTATE VALUE character-literal

SIGNAL condition-name

-Kevin

On Tue, May 13, 2008 at 2:53 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 1. The parentheses around the USING list seem useless; let's drop 'em.

Yes.

>
> 2. I think the separation between SQLSTATE and CONDITION is just
> complication. A SQLSTATE is required to be exactly 5 digits and/or
> upper case ASCII letters; I see no realistic prospect that any condition
> name would ever look like a SQLSTATE (and we could certainly adjust
> condition names to prevent it, if anyone would make such an unhappy
> choice). So I think we could unify these options into one. I think
> CODE might be a better choice for the option name than SQLSTATE (since
> the latter already has a meaning in pl/pgsql, ie the function that
> gives you the code for the currently thrown error) --- thoughts?
>

Yes. CODE has a nice symmetry with the use of errcode in ereport as well.

> 3. I think we should allow the user to specify the error message the
> same way as the other options, that is
> RAISE level USING MESSAGE = string_expression [ , ... ]
> The %-format business has always struck me as a bit weird, and it's
> much more so if we aren't handling the other error report components
> in the same fashion. So we ought to provide an alternative that's
> more uniform.
>

I agree that the % formatting in the RAISE message is weird, but it is
useful. When you're writing an exception message you almost always
want to substitute in information about the values (causing|involved
in) the exception. With MESSAGE = string you would have to
concatenate the pieces together with ||, which is longer and less
readable.

I support adding the MESSAGE option (again, nice symmetry with
ereport), but will probably continue to use the %-formatted message
style in my applications.

What would we do if the user specifies a %-formatted message as well
as a MESSAGE option? I think it would be reasonable to bail out with
a message explaining that they should use the formatted message XOR
the MESSAGE option.

> Now, the elephant in the room is the issue of Oracle compatibility.
> None of this looks anything even a little bit like Oracle's RAISE
> command. Oracle allows
> RAISE exception_name ;
> RAISE ;
> where the second case is allowed only in an EXCEPTION handler and
> means to re-throw the current error. I think the latter is a very
> good idea and we ought to support it. Right now there's no way to
> re-throw an error without information loss, and that'll get a lot
> worse with these additions to what RAISE can throw.

Yes! I've wished for a re-throw ability several times in the past.

> I'm less
> excited about the condition-name-only syntax; that seems awfully
> impoverished given the lack of any way to supply a specific error
> message or data values. Still, we could imagine people wanting
> something like
> RAISE condition_name USING message = string_expression
> where the condition_name would substitute for the CODE option.
> I think we could support this as long as the condition name were
> given as an exception name rather than a string literal (otherwise
> it looks too much like our legacy syntax). Comments? Is anyone
> excited about that one way or the other?

I like "RAISE condition_name", can we support it in conjunction with
the current syntax? That is:

RAISE level [condition] [string literal, [parameter, ...]] [USING
[option = value, ...]]

Cheers,
BJ

"Brendan Jurd" <direvus(at)gmail(dot)com> writes:
> I agree that the % formatting in the RAISE message is weird, but it is
> useful.

Sure, I'm not proposing removing it.

> What would we do if the user specifies a %-formatted message as well
> as a MESSAGE option?

Throw an error (just like if they specified the same option type twice).

> I like "RAISE condition_name", can we support it in conjunction with
> the current syntax? That is:

> RAISE level [condition] [string literal, [parameter, ...]] [USING
> [option = value, ...]]

Well, it's sort of a mess because level has to become optional in order
to be Oracle-compatible (or PSM-compliant, if Kevin is correct). We
could get away with it only if the condition were not allowed to be
a string literal, which I guess is tolerable but it's a bit annoying.
It would get less annoying if we allowed user-declared exception names.
I find the Oracle syntax for those to be spectacularly awful:

DECLARE
deadlock_detected EXCEPTION;
PRAGMA EXCEPTION_INIT(deadlock_detected, -60);

but it sounds like SQL/PSM's syntax isn't so bad. I could live with
the reported

DECLARE
condition-name CONDITION FOR SQLSTATE VALUE character-literal

However, that's a separate feature and I don't want to get into it as
part of the current patch.

regards, tom lane

2008/5/12 Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>:
>>>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Now, the elephant in the room is the issue of Oracle compatibility.
>> None of this looks anything even a little bit like Oracle's RAISE
>> command. Oracle allows
>> RAISE exception_name ;
>> RAISE ;
>
> I'm probably in the minority, but I care more about SQL/PSM
> compatibility than Oracle compatibility. I would hope that the ISO
> standard is at least a gorilla sitting in the corner of the room.
>
> If it's not too impractical, a nod toward these would be good:
>
> DECLARE condition-name CONDITION FOR SQLSTATE VALUE character-literal
>
> SIGNAL condition-name
>
> -Kevin

plpgsql can't be SQL/PSM compatible - it's goal other language
plpgpsm, and there is condition declared via standard.

Pavel
>
>

2008/5/12 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> "Brendan Jurd" <direvus(at)gmail(dot)com> writes:
>> I agree that the % formatting in the RAISE message is weird, but it is
>> useful.
>
> Sure, I'm not proposing removing it.
>
>> What would we do if the user specifies a %-formatted message as well
>> as a MESSAGE option?
>
> Throw an error (just like if they specified the same option type twice).
>
>> I like "RAISE condition_name", can we support it in conjunction with
>> the current syntax? That is:
>
>> RAISE level [condition] [string literal, [parameter, ...]] [USING
>> [option = value, ...]]
>
> Well, it's sort of a mess because level has to become optional in order
> to be Oracle-compatible (or PSM-compliant, if Kevin is correct). We
> could get away with it only if the condition were not allowed to be
> a string literal, which I guess is tolerable but it's a bit annoying.
> It would get less annoying if we allowed user-declared exception names.
> I find the Oracle syntax for those to be spectacularly awful:
>
> DECLARE
> deadlock_detected EXCEPTION;
> PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
>
> but it sounds like SQL/PSM's syntax isn't so bad. I could live with
> the reported
>
> DECLARE
> condition-name CONDITION FOR SQLSTATE VALUE character-literal
>
> However, that's a separate feature and I don't want to get into it as
> part of the current patch.
>
> regards, tom lane
>

Tom, it's exactly like my patch that you rejected two years ago.

http://archives.postgresql.org/pgsql-patches/2005-07/msg00176.php

Pavel

"Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
> 2008/5/12 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> It would get less annoying if we allowed user-declared exception names.

> Tom, it's exactly like my patch that you rejected two years ago.

Uh, no, not "exactly like" --- that patch doesn't have anything to do
with the SQL/PSM syntax, and not much with the SQL/PSM semantics.
As I read the spec, a condition name isn't a variable and so you can't
do runtime assignment to it (and unlike Neil, I don't think you should
be able to do so).

regards, tom lane

2008/5/12 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> I've started to look over Pavel's revised RAISE patch
> http://archives.postgresql.org/pgsql-patches/2008-05/msg00187.php
> and I've got a few quibbles with the syntax choices.
>
> Pavel proposes extending RAISE like this:
>
> RAISE level 'format' [, expression [, ...] ] [ USING ( option = value [, ... ] ) ]

>
> the part before USING being what we had already. Each "option" keyword
> is one of SQLSTATE, CONDITION, DETAIL, or HINT, and each "value" is a
> string-valued expression. SQLSTATE takes a value like '22012' while the
> (mutually exclusive) CONDITION takes a value like 'DIVISION_BY_ZERO'.
> DETAIL and HINT allow those parts of an error report to be filled in.
>
> I'd like to propose the following changes:
>
> 1. The parentheses around the USING list seem useless; let's drop 'em.

it hasn't any precedent in PostgreSQL. But option list in parenthesesis
>
> 2. I think the separation between SQLSTATE and CONDITION is just
> complication. A SQLSTATE is required to be exactly 5 digits and/or
> upper case ASCII letters; I see no realistic prospect that any condition
> name would ever look like a SQLSTATE (and we could certainly adjust
> condition names to prevent it, if anyone would make such an unhappy
> choice). So I think we could unify these options into one. I think
> CODE might be a better choice for the option name than SQLSTATE (since
> the latter already has a meaning in pl/pgsql, ie the function that
> gives you the code for the currently thrown error) --- thoughts?
>

CODE isn't well name. It's too much general. If you would to drop one
identifier I prefer CONDITION or some similar (minim. ERRCODE). In
plpgsql SQLSTATE is keyword, and in some implementations it's implicit
variables too. Using it, it's more readable - more verbose - it's in
spirit of PL/SQL. Maybe:

CONDITION = expression returning name | SQLSTATE expression returning SQLSTATE.

> 3. I think we should allow the user to specify the error message the
> same way as the other options, that is
> RAISE level USING MESSAGE = string_expression [ , ... ]
> The %-format business has always struck me as a bit weird, and it's
> much more so if we aren't handling the other error report components
> in the same fashion. So we ought to provide an alternative that's
> more uniform.
>
> Now, the elephant in the room is the issue of Oracle compatibility.
> None of this looks anything even a little bit like Oracle's RAISE
> command. Oracle allows
> RAISE exception_name ;
> RAISE ;
> where the second case is allowed only in an EXCEPTION handler and
> means to re-throw the current error. I think the latter is a very
> good idea and we ought to support it. Right now there's no way to
> re-throw an error without information loss, and that'll get a lot
> worse with these additions to what RAISE can throw. I'm less
> excited about the condition-name-only syntax; that seems awfully
> impoverished given the lack of any way to supply a specific error
> message or data values. Still, we could imagine people wanting
> something like
> RAISE condition_name USING message = string_expression
> where the condition_name would substitute for the CODE option.
> I think we could support this as long as the condition name were
> given as an exception name rather than a string literal (otherwise
> it looks too much like our legacy syntax). Comments? Is anyone
> excited about that one way or the other?

I agree with this syntax, but I propose using code only with SQLSTATE keyword

RAISE 22345 is ugly
RAISE SQLSTATE 22345 is better and on this position can be
parametrized - now I thing, so SQLSTATE and CONDITION shouldn't be
defined in USING.

variants:
RAISE unique_violation USING message = 'aaaa', hint = 'aaaa';
RAISE SQLSTATE USING message ...
RAISE variable USING ...
RAISE SQLSTATE USING ...

>
> Lastly: to allow users to catch errors thrown with user-defined
> SQLSTATEs, Pavel proposes extending the syntax of EXCEPTION WHEN lists
> so that an error code can be specified in either of these styles:
> DIVISION_BY_ZERO
> SQLSTATE 22012
> I find the second style rather weird, and I think it probably doesn't
> even work for cases like 2201F (which isn't going to get lexed as
> a single token). I would suggest a quoted literal and drop the noise
> word, so that the alternatives are
> DIVISION_BY_ZERO
> '22012'
> Comments?

it's true - it's have to quoted literal or other hand, solve it on
lexer level. But it's not important on plpgsql - there we can choice
the most simple solution.

Regards
Pavel Stehule

>
> If we can get some consensus I'll undertake to adjust the patch
> accordingly.
>
> regards, tom lane
>

2008/5/12 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
>> 2008/5/12 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>>> It would get less annoying if we allowed user-declared exception names.
>
>> Tom, it's exactly like my patch that you rejected two years ago.
>
> Uh, no, not "exactly like" --- that patch doesn't have anything to do
> with the SQL/PSM syntax, and not much with the SQL/PSM semantics.
> As I read the spec, a condition name isn't a variable and so you can't
> do runtime assignment to it (and unlike Neil, I don't think you should
> be able to do so).
>

In plpgsql I prefer PL/SQL syntax. Mix SQL/PSM and PL/SQL will be
mismas. But I like idea, so you can set dynamically SQLSTATE and other
params - because you can write own wrapper for RAISE statement. It's
can be usable for centralized exception management. I can do it in C,
but there are lot of users, that could use only plpgsql.

> regards, tom lane
>

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> I'm probably in the minority, but I care more about SQL/PSM
> compatibility than Oracle compatibility.

Well, a different line of attack would be to leave RAISE as-is and adopt
the SQL/PSM syntax for a modernized command. What I'm seeing in Part 4
is

<signal statement> ::=
SIGNAL <signal value>
[ <set signal information> ]

<signal value> ::=
<condition name>
| <sqlstate value>

<condition name> ::=
<identifier>

<sqlstate value> ::=
SQLSTATE [ VALUE ] <character string literal>

<set signal information> ::=
SET <signal information item list>

<signal information item list> ::=
<signal information item> [ { <comma> <signal information item> }... ]

<signal information item> ::=
<condition information item name> <equals operator> <simple value specification>

If we're willing to invent Postgres-specific <condition information item
names> for MESSAGE, DETAIL, etc, then this is just about isomorphic to
the proposed RAISE syntax, except that if you want an elog level other
than ERROR you'd have to specify it as an item in the SET-list.

BTW, the spec also uses <condition name> and <sqlstate value> as above
in handler declarations, so it looks like both Pavel and I got it wrong
about how to extend the EXCEPTION syntax: it should be
SQLSTATE [VALUE] 'xxxxx'

regards, tom lane

2008/5/12 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> I'm probably in the minority, but I care more about SQL/PSM
>> compatibility than Oracle compatibility.
>
> Well, a different line of attack would be to leave RAISE as-is and adopt
> the SQL/PSM syntax for a modernized command. What I'm seeing in Part 4
> is
>
> <signal statement> ::=
> SIGNAL <signal value>
> [ <set signal information> ]
>
> <signal value> ::=
> <condition name>
> | <sqlstate value>
>
> <condition name> ::=
> <identifier>
>
> <sqlstate value> ::=
> SQLSTATE [ VALUE ] <character string literal>
>
> <set signal information> ::=
> SET <signal information item list>
>
> <signal information item list> ::=
> <signal information item> [ { <comma> <signal information item> }... ]
>
> <signal information item> ::=
> <condition information item name> <equals operator> <simple value specification>
>
> If we're willing to invent Postgres-specific <condition information item
> names> for MESSAGE, DETAIL, etc, then this is just about isomorphic to
> the proposed RAISE syntax, except that if you want an elog level other
> than ERROR you'd have to specify it as an item in the SET-list.
>
> BTW, the spec also uses <condition name> and <sqlstate value> as above
> in handler declarations, so it looks like both Pavel and I got it wrong
> about how to extend the EXCEPTION syntax: it should be
> SQLSTATE [VALUE] 'xxxxx'
>
> regards, tom lane
>

I like this syntax, but I am not if it's good idea add new similar
statement. I don't know - but maybe it's can be better then extending
RAISE - and way to get consensus.

Regards
Pavel Stehule

2008/5/12 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> I'm probably in the minority, but I care more about SQL/PSM
>> compatibility than Oracle compatibility.
>
> Well, a different line of attack would be to leave RAISE as-is and adopt
> the SQL/PSM syntax for a modernized command. What I'm seeing in Part 4
> is
>
> <signal statement> ::=
> SIGNAL <signal value>
> [ <set signal information> ]
>
> <signal value> ::=
> <condition name>
> | <sqlstate value>
>
> <condition name> ::=
> <identifier>
>
> <sqlstate value> ::=
> SQLSTATE [ VALUE ] <character string literal>
>
> <set signal information> ::=
> SET <signal information item list>
>
> <signal information item list> ::=
> <signal information item> [ { <comma> <signal information item> }... ]
>
> <signal information item> ::=
> <condition information item name> <equals operator> <simple value specification>
>
> If we're willing to invent Postgres-specific <condition information item
> names> for MESSAGE, DETAIL, etc, then this is just about isomorphic to
> the proposed RAISE syntax, except that if you want an elog level other
> than ERROR you'd have to specify it as an item in the SET-list.
>
> BTW, the spec also uses <condition name> and <sqlstate value> as above
> in handler declarations, so it looks like both Pavel and I got it wrong
> about how to extend the EXCEPTION syntax: it should be
> SQLSTATE [VALUE] 'xxxxx'
>

next step can be extension of GET DIAGNOSTIC statement ...

Pavel

p.s. CASE statement going from SQL/PSM too. so why not?

> regards, tom lane
>

"Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
> I like this syntax, but I am not if it's good idea add new similar
> statement. I don't know - but maybe it's can be better then extending
> RAISE - and way to get consensus.

I looked a bit more at the SQL spec. It already defines a <condition
information item name> MESSAGE_TEXT, which arguably is what we should
use for the primary message item, but that seems unpleasantly long for
something that's going to be used in pretty much every SIGNAL command.
Also there's a question of whether it's supposed to mean the *complete*
message delivered to a client, which would subsume DETAIL, HINT, etc
in our scheme. So I'm a bit tempted to stick with MESSAGE, DETAIL,
and HINT as the settable options if we go with SQL/PSM-derived syntax.
We'd also want LEVEL or something to be able to specify non-ERROR
elog levels.

Also, as to the re-throw-an-error capability, SQL/PSM defines a RESIGNAL
command that does this. I propose implementing only the parameterless
variant of this, at least for the time being. (The spec appears to
intend that RESIGNAL can override selected fields of the error being
re-thrown, which doesn't strike me as a terribly good idea --- you could
end up with a completely nonsensical error report.)

regards, tom lane

On Monday 12 May 2008 14:40:46 Pavel Stehule wrote:
> 2008/5/12 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> > "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
> >> 2008/5/12 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> >>> It would get less annoying if we allowed user-declared exception names.
> >>
> >> Tom, it's exactly like my patch that you rejected two years ago.
> >
> > Uh, no, not "exactly like" --- that patch doesn't have anything to do
> > with the SQL/PSM syntax, and not much with the SQL/PSM semantics.
> > As I read the spec, a condition name isn't a variable and so you can't
> > do runtime assignment to it (and unlike Neil, I don't think you should
> > be able to do so).
>
> In plpgsql I prefer PL/SQL syntax. Mix SQL/PSM and PL/SQL will be
> mismas. But I like idea, so you can set dynamically SQLSTATE and other
> params - because you can write own wrapper for RAISE statement. It's
> can be usable for centralized exception management. I can do it in C,
> but there are lot of users, that could use only plpgsql.
>

I think nod's toward PL/SQL compatability should be given in general. If
people want a PSM style language, let's work on getting pl/psm better
maintained or integrated.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> writes:
> On Monday 12 May 2008 14:40:46 Pavel Stehule wrote:
>> In plpgsql I prefer PL/SQL syntax.

> I think nod's toward PL/SQL compatability should be given in general.

This position seems just about entirely unhelpful for resolving the
problem at hand, because PL/SQL hasn't *got* syntax that does what
we want.

It might lead us to favor RAISE without parameter over RESIGNAL, but
that's a pretty trivial point anyway.

regards, tom lane

2008/5/12 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
>> I like this syntax, but I am not if it's good idea add new similar
>> statement. I don't know - but maybe it's can be better then extending
>> RAISE - and way to get consensus.
>
> I looked a bit more at the SQL spec. It already defines a <condition
> information item name> MESSAGE_TEXT, which arguably is what we should
> use for the primary message item, but that seems unpleasantly long for
> something that's going to be used in pretty much every SIGNAL command.
> Also there's a question of whether it's supposed to mean the *complete*
> message delivered to a client, which would subsume DETAIL, HINT, etc
> in our scheme. So I'm a bit tempted to stick with MESSAGE, DETAIL,
> and HINT as the settable options if we go with SQL/PSM-derived syntax.
> We'd also want LEVEL or something to be able to specify non-ERROR
> elog levels.
>

I agree

> Also, as to the re-throw-an-error capability, SQL/PSM defines a RESIGNAL
> command that does this. I propose implementing only the parameterless
> variant of this, at least for the time being. (The spec appears to
> intend that RESIGNAL can override selected fields of the error being
> re-thrown, which doesn't strike me as a terribly good idea --- you could
> end up with a completely nonsensical error report.)
>

ok

> regards, tom lane
>

who write this patch?
Pavel

On May 12, 2008, at 11:53 AM, Tom Lane wrote:
> 3. I think we should allow the user to specify the error message the
> same way as the other options, that is
> RAISE level USING MESSAGE = string_expression [ , ... ]
> The %-format business has always struck me as a bit weird, and it's
> much more so if we aren't handling the other error report components
> in the same fashion. So we ought to provide an alternative that's
> more uniform.

I'm entirely in favor of allowing string expressions; that's always
been a huge shortcoming in RAISE.

I'm entirely opposed to getting rid of % substitution. It's
incredibly useful; I have it in probably 80% of my RAISE statements.
It encourages providing more detailed error messages, which is a Good
Thing.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

"Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
> who write this patch?

Well, like I said, I'm willing to adjust the patch to whatever syntax
we come up with.

After sleeping on it I'm a bit less excited about using the SQL/PSM
SIGNAL syntax; the reason being that if we use that, and then sometime
in the future we read the spec more closely and find out that it demands
different behavior than RAISE has, we'd have a compatibility problem.
Inventing PG-only additions to RAISE doesn't carry that risk.

So right now I'm thinking I like my original proposal
http://archives.postgresql.org/pgsql-hackers/2008-05/msg00357.php
with the exception that we should go with
SQLSTATE 'xyzzy'
as the syntax in EXCEPTION lists. Also I'm willing to go with
ERRCODE rather than CODE as the name of the USING option, since
Pavel didn't like CODE. (I don't want to use SQLSTATE for it,
because with this syntax it's pretty clear that SQLSTATE means
one of the 5-letter codes, *not* a condition name.)

regards, tom lane

2008/5/13 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
>> who write this patch?
>
> Well, like I said, I'm willing to adjust the patch to whatever syntax
> we come up with.
>
> After sleeping on it I'm a bit less excited about using the SQL/PSM
> SIGNAL syntax; the reason being that if we use that, and then sometime
> in the future we read the spec more closely and find out that it demands
> different behavior than RAISE has, we'd have a compatibility problem.
> Inventing PG-only additions to RAISE doesn't carry that risk.
>
> So right now I'm thinking I like my original proposal
> http://archives.postgresql.org/pgsql-hackers/2008-05/msg00357.php
> with the exception that we should go with
> SQLSTATE 'xyzzy'
> as the syntax in EXCEPTION lists. Also I'm willing to go with
> ERRCODE rather than CODE as the name of the USING option, since
> Pavel didn't like CODE. (I don't want to use SQLSTATE for it,
> because with this syntax it's pretty clear that SQLSTATE means
> one of the 5-letter codes, *not* a condition name.)
>
> regards, tom lane
>

+1

Regards
Pavel Stehule

On May 13, 2008, at 11:53 AM, Tom Lane wrote:
> So right now I'm thinking I like my original proposal
> http://archives.postgresql.org/pgsql-hackers/2008-05/msg00357.php
> with the exception that we should go with
> SQLSTATE 'xyzzy'
> as the syntax in EXCEPTION lists.

Not to be a PITA about this, but I reeally think users are going to
complain if we remove the % replacement stuff... Is there no way to
keep that with the new syntax?
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Decibel! <decibel(at)decibel(dot)org> writes:
> Not to be a PITA about this, but I reeally think users are going to
> complain if we remove the % replacement stuff... Is there no way to
> keep that with the new syntax?

Uh, I didn't remove anything.

regards, tom lane

> So right now I'm thinking I like my original proposal
> http://archives.postgresql.org/pgsql-hackers/2008-05/msg00357.php
> with the exception that we should go with
> SQLSTATE 'xyzzy'
> as the syntax in EXCEPTION lists. Also I'm willing to go with
> ERRCODE rather than CODE as the name of the USING option, since

Other db's go with SQLCODE and SQLSTATE.
Would SQLCODE be better than ERRCODE ?

SQLCODE is usually an integer value, but the values correspond to
the strings used in pg. (Think of the strings as typedefs for a number,
like DEVIDE_BY_ZERO == -11028 SQLSTATE '22012')

Andreas

"Zeugswetter Andreas OSB sIT" <Andreas(dot)Zeugswetter(at)s-itsolutions(dot)at> writes:
> Other db's go with SQLCODE and SQLSTATE.
> Would SQLCODE be better than ERRCODE ?

No, because SQLCODE has a specific meaning, and it's *not* either a
condition name or a SQLSTATE --- it's the old SQL89-era error code
numbering. I think this would just create confusion.

regards, tom lane