Syntax decisions for pl/pgsql RAISE extension

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgreSQL(dot)org
Cc: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
Subject: Syntax decisions for pl/pgsql RAISE extension
Date: 2008-05-12 16:53:18
Message-ID: 21943.1210611198@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2008-05-12 17:04:59 Re: Setting a pre-existing index as a primary key
Previous Message Michael Meskes 2008-05-12 16:33:23 Re: another ecpg crash