FAQ
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

Search Discussions

  • Kevin Grittner at May 12, 2008 at 5:34 pm

    Tom Lane 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
  • Pavel Stehule at May 12, 2008 at 6:13 pm

    2008/5/12 Kevin Grittner <Kevin.Grittner@wicourts.gov>:
    Tom Lane 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
  • Tom Lane at May 12, 2008 at 6:44 pm

    "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'

    regards, tom lane
  • Pavel Stehule at May 12, 2008 at 6:53 pm

    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'

    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
  • Tom Lane at May 12, 2008 at 7:47 pm

    "Pavel Stehule" <pavel.stehule@gmail.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
  • Pavel Stehule at May 13, 2008 at 4:07 am

    2008/5/12 Tom Lane <tgl@sss.pgh.pa.us>:
    "Pavel Stehule" <pavel.stehule@gmail.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
  • Tom Lane at May 13, 2008 at 4:55 pm

    "Pavel Stehule" <pavel.stehule@gmail.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
  • Pavel Stehule at May 13, 2008 at 5:56 pm

    2008/5/13 Tom Lane <tgl@sss.pgh.pa.us>:
    "Pavel Stehule" <pavel.stehule@gmail.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
  • Decibel! at May 14, 2008 at 12:36 am

    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@decibel.org
    Give your computer some brain candy! www.distributed.net Team #1828
  • Tom Lane at May 14, 2008 at 1:56 am

    Decibel! <decibel@decibel.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
  • Zeugswetter Andreas OSB sIT at May 14, 2008 at 8:30 am

    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
  • Tom Lane at May 14, 2008 at 1:51 pm

    "Zeugswetter Andreas OSB sIT" <Andreas.Zeugswetter@s-itsolutions.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
  • Pavel Stehule at May 12, 2008 at 6:57 pm

    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
  • Brendan Jurd at May 12, 2008 at 5:42 pm

    On Tue, May 13, 2008 at 2:53 AM, Tom Lane 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
  • Tom Lane at May 12, 2008 at 6:12 pm

    "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
  • Pavel Stehule at May 12, 2008 at 6:17 pm

    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-patches/2005-07/msg00176.php

    Pavel
  • Tom Lane at May 12, 2008 at 6:30 pm

    "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
  • Pavel Stehule at May 12, 2008 at 6:40 pm

    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
  • Robert Treat at May 13, 2008 at 1:16 am

    On Monday 12 May 2008 14:40:46 Pavel Stehule wrote:
    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.
    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
  • Tom Lane at May 13, 2008 at 2:41 am

    Robert Treat 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
  • Pavel Stehule at May 12, 2008 at 6:36 pm

    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-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
  • Decibel! at May 13, 2008 at 4:12 pm

    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@decibel.org
    Give your computer some brain candy! www.distributed.net Team #1828

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMay 12, '08 at 4:53p
activeMay 14, '08 at 1:51p
posts23
users7
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase