I've started to look over Pavel's revised RAISE patch
http://archives.postgresql.org/pgsql...5/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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

>>> Tom Lane <tgl@sss.pgh.pa.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


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, May 13, 2008 at 2:53 AM, Tom Lane <tgl@sss.pgh.pa.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

"Brendan Jurd" <direvus@gmail.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2008/5/12 Kevin Grittner <Kevin.Grittner@wicourts.gov>:
>>>> Tom Lane <tgl@sss.pgh.pa.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
>
>

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2008/5/12 Tom Lane <tgl@sss.pgh.pa.us>:
> "Brendan Jurd" <direvus@gmail.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...7/msg00176.php

Pavel

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

"Pavel Stehule" <pavel.stehule@gmail.com> writes:
> 2008/5/12 Tom Lane <tgl@sss.pgh.pa.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2008/5/12 Tom Lane <tgl@sss.pgh.pa.us>:
> I've started to look over Pavel's revised RAISE patch
> http://archives.postgresql.org/pgsql...5/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
>

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2008/5/12 Tom Lane <tgl@sss.pgh.pa.us>:
> "Pavel Stehule" <pavel.stehule@gmail.com> writes:
>> 2008/5/12 Tom Lane <tgl@sss.pgh.pa.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
>

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2008/5/12 Tom Lane <tgl@sss.pgh.pa.us>:
> "Kevin Grittner" <Kevin.Grittner@wicourts.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
>

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers