Re: User's exception plpgsql

In PL/PgSQL, "END LOOP" is used to terminate loop blocks, and "END IF"
is used to terminate IF blocks. This is needlessly verbose: we could
simply accept "END" in both cases without syntactic ambiguity. I'd like
to make this change, so that END can be used to terminate any kind of
block. There's no need to remove support for the present syntax, of
course, so there's no backward compatibility concern. Oracle's PL/SQL
does require "END IF" and "END LOOP", but folks interested in maximum
compatibility can always use those forms if they like.

Any objections?

-Neil

Neil,

> In PL/PgSQL, "END LOOP" is used to terminate loop blocks, and "END IF"
> is used to terminate IF blocks. This is needlessly verbose: we could
> simply accept "END" in both cases without syntactic ambiguity. I'd like
> to make this change, so that END can be used to terminate any kind of
> block. There's no need to remove support for the present syntax, of
> course, so there's no backward compatibility concern. Oracle's PL/SQL
> does require "END IF" and "END LOOP", but folks interested in maximum
> compatibility can always use those forms if they like.

No problem from me. Since the parser checks for block closure for all
block types, I can't see how this would be a problem.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Neil Conway said:
> In PL/PgSQL, "END LOOP" is used to terminate loop blocks, and "END IF"
> is used to terminate IF blocks. This is needlessly verbose: we could
> simply accept "END" in both cases without syntactic ambiguity. I'd like
> to make this change, so that END can be used to terminate any kind of
> block. There's no need to remove support for the present syntax, of
> course, so there's no backward compatibility concern. Oracle's PL/SQL
> does require "END IF" and "END LOOP", but folks interested in maximum
> compatibility can always use those forms if they like.
>
> Any objections?
>

I'm unkeen. I see no technical advantage - it's just a matter of taste. We
advertise that plpgsql is similar to plsql - we should not do anything to
make that less so IMNSHO. Terseness is not always good, redundancy is not
always bad.

cheers

andrew

"Andrew Dunstan" <andrew(at)dunslane(dot)net> writes:
> Neil Conway said:
>> Any objections?

> I'm unkeen. I see no technical advantage - it's just a matter of taste. We
> advertise that plpgsql is similar to plsql - we should not do anything to
> make that less so IMNSHO. Terseness is not always good, redundancy is not
> always bad.

That was my reaction too, though I'm too tired at this hour to phrase it
so well ;-). The long-term point in my mind is that removing
syntactical redundancy always reduces the ability to detect errors or
report errors acccurately; and it may limit our freedom to introduce
new features later. Consider for example the possibility that Oracle's
next release adds some new frammish that can't be duplicated because we
chose not to distinguish various forms of "END xxx" ...

regards, tom lane

Andrew Dunstan wrote:
> I'm unkeen. I see no technical advantage - it's just a matter of taste.

There is no "technical advantage" to case insensitive keywords, or
dollar quoting, or a variety of other programming language features that
don't change functionality but exist to make using the programming
language easier.

> We advertise that plpgsql is similar to plsql - we should not do
> anything to make that less so IMNSHO.

Do you *really* mean that? This principle would mean we should reject
patches like the CONTINUE statement patch I just applied, for example,
as PL/SQL has no such construct.

In any case, I think you are overestimating the value of strict PL/SQL
compatibility. IMHO, PL/PgSQL should be a useful procedural programming
language first, and a reimplementation of PL/SQL second. We should
provide an equivalent feature (not necessarily with the same syntax) for
all of PL/SQL's useful features, but I don't see the value in copying
Oracle when PL/SQL's implementation of a feature is ugly, broken, or
inconsistent with the rest of Postgres. It's not as if complete
source-level compatibility with PL/SQL has been a goal for PL/PgSQL
anyway (and besides, there are other people, like EnterpriseDB, who can
provide that for those who need it).

> Terseness is not always good, redundancy is not always bad.

Granted -- but why is redundancy a good thing here?

-Neil

Tom Lane wrote:
> The long-term point in my mind is that removing syntactical
> redundancy always reduces the ability to detect errors or report
> errors acccurately

Lexical scoping is unambiguous in a language like PL/PgSQL. Since it is
simple to determine whether a given END matches an IF, LOOP, or BEGIN, I
don't see how it would reduce our "ability to detect errors or report
errors accurately".

> Consider for example the possibility that Oracle's next release adds
> some new frammish that can't be duplicated because we chose not to
> distinguish various forms of "END xxx" ...

As lexical scoping is still unambiguous, we could actually add a K_LOOP
/ K_IF token to the input stream, if that would make you happier :) (Of
course I'm not suggesting this -- the point is that as far as the parser
is concerned, we should have precisely the same information for
disambiguating the input as we used to have.)

BTW, I notice that Oracle actually allows:

<<label>>
LOOP
-- ...
END LOOP label;

whereas we don't allow the optional label following END LOOP. Which goes
to my general point: this frammish has existed in PL/SQL for a while,
but it's not as if people are clamoring for us to implement it. I would
wager that most people care about having *equivalent* features to
PL/SQL, not exactly identical syntax. For example, the lack of
autonomous transactions is something people have asked for in the past,
because it *does* make porting PL/SQL applications more difficult. I
can't see anyone losing any sleep because we are slightly more relaxed
about the input we accept.

-Neil

Neil Conway said:
> Andrew Dunstan wrote:
>> I'm unkeen. I see no technical advantage - it's just a matter of
>> taste.
>
> There is no "technical advantage" to case insensitive keywords, or
> dollar quoting, or a variety of other programming language features
> that don't change functionality but exist to make using the
> programming language easier.
>

But this doesn't make it easier to use - users don't just include those who
write it. The antecedent language of these, Ada, from which this syntax
comes, was explicitly designed to be reader-friendly as opposed to
writer-friendly, and this is a part of that. I can tell you from experience
of programming Ada a long time ago that I have been profoundly grateful that
this was required in the language when disentangling a badly written 1000+
line long multibranch IF statement. And I still find myself having to hunt
for what sort of block a } is closing in C, and I still find it annoying.

>> We advertise that plpgsql is similar to plsql - we should not do
>> anything to make that less so IMNSHO.
>
> Do you *really* mean that? This principle would mean we should reject
> patches like the CONTINUE statement patch I just applied, for example,
> as PL/SQL has no such construct.
>

Well, perhaps I should have qualified that a bit - we shouldn't do it
gratuitously.

Getting the effect of CONTINUE for nested loops can be sufficiently hard
that it is arguable that implementing it is not just syntactic sugar. I seem
to recall muttering about how implementing GOTO wasn't worth the trouble.

>
>> Terseness is not always good, redundancy is not always bad.
>
> Granted -- but why is redundancy a good thing here?
>

see above

cheers

andrew

Andrew Dunstan wrote:
> But this doesn't make it easier to use - users don't just include those who
> write it. The antecedent language of these, Ada, from which this syntax
> comes, was explicitly designed to be reader-friendly as opposed to
> writer-friendly, and this is a part of that.

IMHO it is just needless verbiage that makes programs both harder to
read *and* harder to write, albeit marginally so. I think there is a
reason why Ada-style block terminators are in the minority among
block-structured languages :)

But obviously this is a matter of taste -- does anyone else like or
dislike the current syntax?

-Neil

On Thu, Jun 23, 2005 at 01:41:49AM +1000, Neil Conway wrote:
> Andrew Dunstan wrote:
> >But this doesn't make it easier to use - users don't just include those who
> >write it. The antecedent language of these, Ada, from which this syntax
> >comes, was explicitly designed to be reader-friendly as opposed to
> >writer-friendly, and this is a part of that.
>
> IMHO it is just needless verbiage that makes programs both harder to
> read *and* harder to write, albeit marginally so. I think there is a
> reason why Ada-style block terminators are in the minority among
> block-structured languages :)
>
> But obviously this is a matter of taste -- does anyone else like or
> dislike the current syntax?

"Like" is a bit strong. But it does make functions written in it easier
to read. And given that the primary debugging methodolofy for pl/pgsql
is "Look at it hard and see what might be incorrect" I can't see that
as a bad thing.

I'd trade a whole lot of "harder to write" for even some "likely to
work".

Cheers,
Steve

On Wed, Jun 22, 2005 at 09:23:17AM -0700, Steve Atkins wrote:
> On Thu, Jun 23, 2005 at 01:41:49AM +1000, Neil Conway wrote:
> > Andrew Dunstan wrote:
> > >But this doesn't make it easier to use - users don't just include those who
> > >write it. The antecedent language of these, Ada, from which this syntax
> > >comes, was explicitly designed to be reader-friendly as opposed to
> > >writer-friendly, and this is a part of that.
> >
> > IMHO it is just needless verbiage that makes programs both harder to
> > read *and* harder to write, albeit marginally so. I think there is a
> > reason why Ada-style block terminators are in the minority among
> > block-structured languages :)
> >
> > But obviously this is a matter of taste -- does anyone else like or
> > dislike the current syntax?
>
> "Like" is a bit strong. But it does make functions written in it easier
> to read. And given that the primary debugging methodolofy for pl/pgsql
> is "Look at it hard and see what might be incorrect" I can't see that
> as a bad thing.

Yeah, while we don't have good debugging support in pl/pgsql we
shouldn't be making it harder to read. (FWIW, yes, I think it's useful
for those keywords to be required when you have to look at homongous
functions.)

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"No renuncies a nada. No te aferres a nada."

On Tue, 21 Jun 2005, Andrew Dunstan wrote:

> Neil Conway said:
> > In PL/PgSQL, "END LOOP" is used to terminate loop blocks, and "END IF"
> > is used to terminate IF blocks. This is needlessly verbose: we could
> > simply accept "END" in both cases without syntactic ambiguity. I'd like
> > to make this change, so that END can be used to terminate any kind of
> > block. There's no need to remove support for the present syntax, of
> > course, so there's no backward compatibility concern. Oracle's PL/SQL
> > does require "END IF" and "END LOOP", but folks interested in maximum
> > compatibility can always use those forms if they like.
> >

Hello,

I prefer actual syntax too, Neil. The reason isn't compatibility with
Oracle, but better readibility - it's mean more work with finishing code
but less with debugging

Regards
Pavel

Hi All,

Sorry if this isn¹t the right forum for this question... But, I noticed
that Alvaro wrote

³... we don't have good debugging support in pl/pgsql ...²
>
I got to thinking it¹d be kewl if PgAdmin3 supported an interactive debugger
for pl/pgsql. If anyone¹s interested in expertly tackling such a community
project, with some financial sponsorship from EDB, please contact me
privately.

--Denis Lussier
Founder & Chief Dweeb
EnterpriseDB

Denis Lussier <denisl(at)enterprisedb(dot)com> writes:
> I got to thinking it'd be kewl if PgAdmin3 supported an interactive debugger
> for pl/pgsql.

That's been kicked around before, although I don't think anyone wants to
tie it to pgAdmin specifically. Check the archives...

regards, tom lane

On 6/22/2005 1:29 AM, Neil Conway wrote:

> Tom Lane wrote:
>> The long-term point in my mind is that removing syntactical
>> redundancy always reduces the ability to detect errors or report
>> errors acccurately
>
> Lexical scoping is unambiguous in a language like PL/PgSQL. Since it is
> simple to determine whether a given END matches an IF, LOOP, or BEGIN, I
> don't see how it would reduce our "ability to detect errors or report
> errors accurately".
>
>> Consider for example the possibility that Oracle's next release adds
>> some new frammish that can't be duplicated because we chose not to
>> distinguish various forms of "END xxx" ...
>
> As lexical scoping is still unambiguous, we could actually add a K_LOOP
> / K_IF token to the input stream, if that would make you happier :) (Of
> course I'm not suggesting this -- the point is that as far as the parser
> is concerned, we should have precisely the same information for
> disambiguating the input as we used to have.)
>
> BTW, I notice that Oracle actually allows:
>
> <<label>>
> LOOP
> -- ...
> END LOOP label;

But what if they decide to allow

LOOP
-- ...
IF condition THEN
EXIT;
END LOOP;

at some point? There you'd get ambiguity.

Jan

>
> whereas we don't allow the optional label following END LOOP. Which goes
> to my general point: this frammish has existed in PL/SQL for a while,
> but it's not as if people are clamoring for us to implement it. I would
> wager that most people care about having *equivalent* features to
> PL/SQL, not exactly identical syntax. For example, the lack of
> autonomous transactions is something people have asked for in the past,
> because it *does* make porting PL/SQL applications more difficult. I
> can't see anyone losing any sleep because we are slightly more relaxed
> about the input we accept.
>
> -Neil
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

Tom Lane wrote:

>Denis Lussier <denisl(at)enterprisedb(dot)com> writes:
>
>
>>I got to thinking it'd be kewl if PgAdmin3 supported an interactive debugger
>>for pl/pgsql.
>>
>>
>
>That's been kicked around before, although I don't think anyone wants to
>tie it to pgAdmin specifically. Check the archives...
>
>

I didn't find anything relevant after a quick search, but if memory
serves, one of the objections to PgAdmin was that it was windows only.
This of course is no longer true as of PgAdmin III 1.0. It now support
Win32, Linux and FreeBSD. So perhaps that objection is no longer valid.

On Thu, Jun 23, 2005 at 01:40:18PM -0400, Matthew T. O'Connor wrote:
> Tom Lane wrote:
>
> >Denis Lussier <denisl(at)enterprisedb(dot)com> writes:
> >
> >
> >>I got to thinking it'd be kewl if PgAdmin3 supported an interactive
> >>debugger for pl/pgsql.
> >
> >That's been kicked around before, although I don't think anyone wants to
> >tie it to pgAdmin specifically. Check the archives...
>
> I didn't find anything relevant after a quick search, but if memory
> serves, one of the objections to PgAdmin was that it was windows only.
> This of course is no longer true as of PgAdmin III 1.0. It now support
> Win32, Linux and FreeBSD. So perhaps that objection is no longer valid.

I think the point is that we will have to make some modifications to
PL/pgSQL, so why not make sure we write something that any tool can use?
Say, a well-defined BE/FE protocol extension.

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"Sallah, I said NO camels! That's FIVE camels; can't you count?"
(Indiana Jones)

Alvaro,

I agree, a protocol seems to generally be the best option.

-Jonah

Alvaro Herrera wrote:

>On Thu, Jun 23, 2005 at 01:40:18PM -0400, Matthew T. O'Connor wrote:
>
>
>>Tom Lane wrote:
>>
>>
>>
>>>Denis Lussier <denisl(at)enterprisedb(dot)com> writes:
>>>
>>>
>>>
>>>
>>>>I got to thinking it'd be kewl if PgAdmin3 supported an interactive
>>>>debugger for pl/pgsql.
>>>>
>>>>
>>>That's been kicked around before, although I don't think anyone wants to
>>>tie it to pgAdmin specifically. Check the archives...
>>>
>>>
>>I didn't find anything relevant after a quick search, but if memory
>>serves, one of the objections to PgAdmin was that it was windows only.
>>This of course is no longer true as of PgAdmin III 1.0. It now support
>>Win32, Linux and FreeBSD. So perhaps that objection is no longer valid.
>>
>>
>
>I think the point is that we will have to make some modifications to
>PL/pgSQL, so why not make sure we write something that any tool can use?
>Say, a well-defined BE/FE protocol extension.
>
>
>

Denis, all,

> I got to thinking it¹d be kewl if PgAdmin3 supported an interactive
> debugger for pl/pgsql.  If anyone¹s interested in expertly tackling such a
> community project, with some financial sponsorship from EDB, please contact
> me privately.

Just FYI, EMS Hitech has a windows-only PL/pgSQL debugger. So it's apparently
possible even with the current tech.

Overally, though, we'd want to support something command-line like the Perl
debug shell. Then any tool could use it.

--
Josh Berkus
Aglio Database Solutions
San Francisco

On Thursday 23 June 2005 14:21, Josh Berkus wrote:
> Denis, all,
>
> > I got to thinking it¹d be kewl if PgAdmin3 supported an interactive
> > debugger for pl/pgsql.  If anyone¹s interested in expertly tackling such
> > a community project, with some financial sponsorship from EDB, please
> > contact me privately.
>
> Just FYI, EMS Hitech has a windows-only PL/pgSQL debugger. So it's
> apparently possible even with the current tech.
>

IIRC thier debugger is little more than BEGIN; SELECT foo(); ROLLBACK; which
isn't great as far as debuggers go.

> Overally, though, we'd want to support something command-line like the Perl
> debug shell. Then any tool could use it.

Uh... a lot of tools have issues executing stuff from the command line... what
we need is something sql driven, or at least that opeates at that level,
inside the db.

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

On Wednesday 22 June 2005 11:41, Neil Conway wrote:
> Andrew Dunstan wrote:
> > But this doesn't make it easier to use - users don't just include those
> > who write it. The antecedent language of these, Ada, from which this
> > syntax comes, was explicitly designed to be reader-friendly as opposed to
> > writer-friendly, and this is a part of that.
>
> IMHO it is just needless verbiage that makes programs both harder to
> read *and* harder to write, albeit marginally so. I think there is a
> reason why Ada-style block terminators are in the minority among
> block-structured languages :)
>
> But obviously this is a matter of taste -- does anyone else like or
> dislike the current syntax?
>

-1 on the proposal to me... ambiguous END statements just looks like trouble
to me. I'd actually rather see you implement label...END LOOP label if you
felt like you had to change *something*.

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

Jan Wieck wrote:
> But what if they decide to allow
>
> LOOP
> -- ...
> IF condition THEN
> EXIT;
> END LOOP;
>
> at some point? There you'd get ambiguity.

ISTM this would be ambiguous in any case:

IF condition1 THEN
foo;
IF condition2 THEN
bar;
END IF;

-Neil

My understanding is that EMS Hitech is just doing client side debugging.
That is they are taking your function and creating a new process to follow
the flow of the program. So if they mess up something you may thing your
program is doing one thing when it is really doing something else. Maybe I'm
wrong here but I assume that is what is going on. While this is better than
nothing, it doesn't compare to a built in API in pl/pgsql that would allow
any tool to hook into a function and debug. Would love to work on this if I
had the low level programming skills that the main hackers have.
On 6/23/05, Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> Denis, all,
>
> > I got to thinking it¹d be kewl if PgAdmin3 supported an interactive
> > debugger for pl/pgsql. If anyone¹s interested in expertly tackling such
> a
> > community project, with some financial sponsorship from EDB, please
> contact
> > me privately.
>
> Just FYI, EMS Hitech has a windows-only PL/pgSQL debugger. So it's
> apparently
> possible even with the current tech.
>
> Overally, though, we'd want to support something command-line like the
> Perl
> debug shell. Then any tool could use it.
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

Am Mittwoch, 22. Juni 2005 04:17 schrieb Neil Conway:
> In PL/PgSQL, "END LOOP" is used to terminate loop blocks, and "END IF"
> is used to terminate IF blocks. This is needlessly verbose:

It is required by the SQL standard.

Hello

this patch allows optional using label with END and END LOOP. Ending label
has only informational value, but can enhance readability large block and
enhance likeness with Oracle.

<<main>>LOOP
...
...
END LOOP<<main>>;

Regards
Pavel Stehule

Hello

Per small recent discussion I corrected patch user's exception.

diff: User can choise any sqlstate (without class U0, which I reserve as
range for default values sqlstates - if user don't spec sqlstate, is
used value from this range). There is only basic changes in documentation
and needs enhancing. I am not able to do (I am sorry, my english is poor).

Note: patch don't create deep changes in plpgsql core. Only enhance stmts
DECLARE, RAISE and EXCEPTION condition.

Next ToDo (needs discussion):
+ Optional message in raise stmt for user's or system exception
raise exception division_by_zero;
+ Possibility rethrown exception
raise;

Regards
Pavel Stehule

Peter Eisentraut wrote:
> It is required by the SQL standard.

No, it isn't -- PL/PgSQL is not defined by the SQL standard. I guess
you're referring to SQL/PSM, but that has only a passing resemblance to
PL/PgSQL. Implementing SQL/PSM in some form would definitely be worth
doing (especially now that MySQL have), but I haven't seen any plans to
do that by adapting PL/PgSQL to SQL/PSM.

In any case, there are plenty of cases in which we accept a superset of
the syntax defined by the SQL standard -- DROP TABLE { RESTRICT |
CASCADE }, for example. We have never interpreted compliance with the
SQL specification to mean that we must *only* accept the standard's
syntax and nothing else.

-Neil

Neil Conway wrote:
> No, it isn't -- PL/PgSQL is not defined by the SQL standard. I guess
> you're referring to SQL/PSM, but that has only a passing resemblance
> to PL/PgSQL. Implementing SQL/PSM in some form would definitely be
> worth doing (especially now that MySQL have), but I haven't seen any
> plans to do that by adapting PL/PgSQL to SQL/PSM.

I don't claim to recall the details, but we have frequently referred to
the SQL standard when resolving issues about PL/pgSQL's syntax.

> In any case, there are plenty of cases in which we accept a superset
> of the syntax defined by the SQL standard -- DROP TABLE { RESTRICT |
> CASCADE }, for example. We have never interpreted compliance with the
> SQL specification to mean that we must *only* accept the standard's
> syntax and nothing else.

The cases were we accept a superset of the SQL standard are either
additional features, backward compatibility, or compatibility to other
systems -- none of which seem to apply here.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Mon, 27 Jun 2005, Neil Conway wrote:

> Peter Eisentraut wrote:
> > It is required by the SQL standard.
>
> No, it isn't -- PL/PgSQL is not defined by the SQL standard. I guess
> you're referring to SQL/PSM, but that has only a passing resemblance to
> PL/PgSQL. Implementing SQL/PSM in some form would definitely be worth
> doing (especially now that MySQL have), but I haven't seen any plans to
> do that by adapting PL/PgSQL to SQL/PSM.

PL/pgSQL is different language than SQL/PSM and is little bit nonsenc
adapting them to SQL/PSM. PL/SQL live still - Oracle did some enhancing,
and we can do it too.

Some parts both languages are similar and some enough different. I had
plan start develop new interpret for SQL/PSM two years ago, but I hadn't
knowleages at that time. Situation is different now. If anybody wont to
work on SQL/PSM I will go too. Solution is another pl - pl/psm. We can
adapt gram.y plpgsql to psm

Regards
Pavel Stehule

Pavel Stehule wrote:
> this patch allows optional using label with END and END LOOP. Ending label
> has only informational value, but can enhance readability large block and
> enhance likeness with Oracle.
>
> <<main>>LOOP
> ...
> ...
> END LOOP<<main>>;

Attached is a revised version of this patch. Changes / comments:

- AFAICS Oracle's syntax is actually

<<label>> LOOP
...
END LOOP label;

i.e. the ending block label isn't enclosed in <<>>. I've adjusted the
patch accordingly.

- your patch broke EXIT and CONTINUE, as running the regression tests
would have made clear.

- yyerror() will set plpgsql_error_lineno, so you needn't do it
yourself. I changed it to use ereport(ERROR) anyway, as it seems a bit
more appropriate. I'm not quite happy with the error message text:

ERROR: end label "outer_label" differs from block's label "inner_label"
CONTEXT: compile of PL/pgSQL function "end_label3" near line 6

ERROR: end label "outer_label" specified for unlabelled block
CONTEXT: compile of PL/pgSQL function "end_label4" near line 5

suggestions for improvement are welcome.

BTW, I notice that some but not all the call sites of ereport(ERROR) in
PL/PgSQL's gram.y set plpgsql_error_lineno. Is there a reason for this?

Barring any objections, I'll apply the attached patch to CVS tomorrow.

-Neil

Neil Conway <neilc(at)samurai(dot)com> writes:
> BTW, I notice that some but not all the call sites of ereport(ERROR) in
> PL/PgSQL's gram.y set plpgsql_error_lineno. Is there a reason for this?

Without looking at the code, I think it may be that you only need to set
the variable if you want the error to point someplace different than the
last "lno" nonterminal.

regards, tom lane

Pavel Stehule wrote:
> this patch allows optional using label with END and END LOOP. Ending label
> has only informational value, but can enhance readability large block and
> enhance likeness with Oracle.

Reviewed and applied -- thanks for the patch.

-Neil

Pavel Stehule wrote:
> Per small recent discussion I corrected patch user's exception.

I'll review and apply this in the next day or so.

> Next ToDo (needs discussion):
> + Optional message in raise stmt for user's or system exception
> raise exception division_by_zero;
> + Possibility rethrown exception
> raise;

Both sound pretty reasonable to me.

-Neil

Neil Conway <neilc(at)samurai(dot)com> writes:
> Pavel Stehule wrote:
>> Per small recent discussion I corrected patch user's exception.

> I'll review and apply this in the next day or so.

Have we got a consensus yet on the behavior? There seemed to be no
meeting of the minds at all the last time I paid attention to this
thread ...

regards, tom lane

On Mon, 4 Jul 2005, Tom Lane wrote:

> Neil Conway <neilc(at)samurai(dot)com> writes:
> > Pavel Stehule wrote:
> >> Per small recent discussion I corrected patch user's exception.
>
> > I'll review and apply this in the next day or so.
>
> Have we got a consensus yet on the behavior? There seemed to be no
> meeting of the minds at all the last time I paid attention to this
> thread ...
>

There was only one objection against - requirement of uniqueness, and I
corrected it.

regards
Pavel

Pavel Stehule wrote:
> Per small recent discussion I corrected patch user's exception.

Attached is a revised patch. I haven't looked at the documentation
changes yet (more work is needed I believe) or some of the error message
text.

I was originally hoping to make "exception variables" a little more
full-featured -- it seems silly to DECLARE something that cannot be
initialized with the value of another expression, for example. I can
also see how it would be useful to evaluate an expression variable (e.g.
to print it out for debugging purposes). It would be possible extend the
operations allowed upon exception variables, thinking about this
further, I wonder if there is any point introducing the concept of an
"exception variable" in the first place. What does it buy us over simply
using a string? In other words, if we allowed the syntax:

RAISE LEVEL [ opt_sqlstate ] 'fmt' [, expr ... ]

where `opt_sqlstate' is either empty, a T_WORD we find in the table of
predefined condition names, or an expression that evaluates to a text
value. The text value must be of a certain form (e.g. 5 characters in
length, begins with a "U" and so on).

It might be slightly more difficult to parse this (especially if we
allow 'fmt' to be an expression yielding a string, not just a string
literal), but I don't think it is ambiguous and can be sorted out via
yylex().

-Neil

Neil Conway <neilc(at)samurai(dot)com> writes:
> I wonder if there is any point introducing the concept of an
> "exception variable" in the first place. What does it buy us over simply
> using a string?

Not a lot really, except for keeping things similar to the Oracle way of
doing it ... but that's a nontrivial consideration.

> RAISE LEVEL [ opt_sqlstate ] 'fmt' [, expr ... ]

> It might be slightly more difficult to parse this (especially if we
> allow 'fmt' to be an expression yielding a string, not just a string
> literal), but I don't think it is ambiguous and can be sorted out via
> yylex().

I think it is a bad idea, if not actually impossible, to have an
expression for sqlstate with no separating syntax before the 'fmt';
especially not if you'd like to also allow an expression for the 'fmt'.

At one point we had talked about

RAISE LEVEL [ opt_sqlstate, ] 'fmt' [, expr ... ]

The hard part here is that there isn't any very easy way to tell whether
you have a sqlstate, a fmt, and N exprs, or a fmt and N+1 exprs. The
saving grace of the declared-exception approach for this is that you
can tell by the datatype of the first argument expression which case you
have: if the expression yields text, it's a fmt, if it yields "exception"
(which we assume is an actual datatype) then it's a sqlstate.

We could handle "undeclared exceptions" in such a design by having a
function that converts text to an exception value:

RAISE LEVEL SQLSTATE('12345'), 'format here', ...

and maybe the short-term cheesy thing to do is special-case exactly this
syntax:

RAISE LEVEL [ SQLSTATE(text_expr), ] text_expr [, ... ]

which would give us the minimum functionality with a clear path to
expansion later.

regards, tom lane

Tom Lane wrote:
> I think it is a bad idea, if not actually impossible, to have an
> expression for sqlstate with no separating syntax before the 'fmt';
> especially not if you'd like to also allow an expression for the 'fmt'.

I don't actually see much of a need to allow 'fmt' to be an expression,
especially now that RAISE parameters can be expressions. The ratio of
constant printf() format strings to variable format strings is probably
100:1, for good reason...

> The hard part here is that there isn't any very easy way to tell whether
> you have a sqlstate, a fmt, and N exprs, or a fmt and N+1 exprs. The
> saving grace of the declared-exception approach for this is that you
> can tell by the datatype of the first argument expression which case you
> have

I really don't like the idea of introducing a new concept into the
language ("exception variables") to resolve some ambiguous syntax. It
would be another matter if exception variables actually provided
something that strings do not...

Another solution might be varying the syntax slightly, such as:

RAISE [ opt_sqlstate ] LEVEL 'fmt' [ , expr ... ]

-Neil

> "exception variable" in the first place. What does it buy us over simply
> using a string? In other words, if we allowed the syntax:
>
> RAISE LEVEL [ opt_sqlstate ] 'fmt' [, expr ... ]
>
> where `opt_sqlstate' is either empty, a T_WORD we find in the table of
> predefined condition names, or an expression that evaluates to a text
> value. The text value must be of a certain form (e.g. 5 characters in
> length, begins with a "U" and so on).

I unlike this syntax. Yes, it's easy and clear, but not readable.
Exception variables are better and an way for future. SQL state can be
only one value wich can hold exception variable. And more it's more in
oracle style (I don't wont to copy all Oracle ware into PostgreSQL)

Pavel

p.s. I have patch for rethrow exception which isn't related to user's
exception (but need's finished plpgsql code). Syntax is easy, I hope

RAISE;

Neil Conway <neilc(at)samurai(dot)com> writes:
> Tom Lane wrote:
>> I think it is a bad idea, if not actually impossible, to have an
>> expression for sqlstate with no separating syntax before the 'fmt';
>> especially not if you'd like to also allow an expression for the 'fmt'.

> I don't actually see much of a need to allow 'fmt' to be an expression,

Well, in any case we have a problem if there's no comma. Consider

RAISE NOTICE '12' !! '345', ...

Is !! an infix operator (using both strings as arguments) or a postfix
operator (in which case '345' is the format)?

> Another solution might be varying the syntax slightly, such as:

> RAISE [ opt_sqlstate ] LEVEL 'fmt' [ , expr ... ]

This would require promoting all the options for LEVEL into fully
reserved words. You really can't get around the fact that you need
something pretty identifiable to terminate the expression.

It might work to require parentheses:

RAISE LEVEL ( sqlstate expression ), 'fmt' [, ...]

The comma after the right paren is optional from a formal point of view,
but I'd still consider it better design to use one than not. (For one
reason, it would make it much easier to catch mismatched-parens problems.)

regards, tom lane

> and maybe the short-term cheesy thing to do is special-case exactly this
> syntax:
>
> RAISE LEVEL [ SQLSTATE(text_expr), ] text_expr [, ... ]
>
> which would give us the minimum functionality with a clear path to
> expansion later.
>
or only RAISE LEVEL SQLSTATE(text_expr)|text_expr [, ...]

if I use registered sqlstate, plpgsql knows text message. But I think this
syntax has more questions than exception's variables. It's really problem
declare one exceptio's variable? It's similar like using constant
variables or magic values.

Pavel

DECLARE not_money EXCEPTION=SQLSTATE('U1101');
BEGIN
IF account < 0 THEN
RAISE EXCEPTION not_money;
...

or

BEGIN
IF account < 0 THEN
RAISE SQLSTATE ('U1101') 'Not money';

Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)cz> writes:
> if I use registered sqlstate, plpgsql knows text message.

No, it does not. I already pointed out that tying a single error
message to a SQLSTATE is unreasonable, because that's not how the
SQL committee intended SQLSTATEs to be used. I haven't looked at
this patch yet, but if it's doing things that way it is wrong.

regards, tom lane

>
> This would require promoting all the options for LEVEL into fully
> reserved words. You really can't get around the fact that you need
> something pretty identifiable to terminate the expression.
>
> It might work to require parentheses:
>
> RAISE LEVEL ( sqlstate expression ), 'fmt' [, ...]
>
? what sense has sqlstate expression? like any expression returns sqlstate
type?
SQLSTATE('')|user exception|system exception

Pavel

On Wed, 6 Jul 2005, Tom Lane wrote:

> Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)cz> writes:
> > if I use registered sqlstate, plpgsql knows text message.
>
> No, it does not. I already pointed out that tying a single error
> message to a SQLSTATE is unreasonable, because that's not how the
> SQL committee intended SQLSTATEs to be used. I haven't looked at
> this patch yet, but if it's doing things that way it is wrong.
>
no, raise stmt still needs message text (patch)

> regards, tom lane
>

What I wont. Maybe I going in wrong direction. Please, correct me. User's
exception needs and will needs message text. I don't wont to introduce
wrong programming style. But if I use exception variable and have to use
its, then there is not only SQLSTATE but there exist name of exception
too. But I wont to simplify using system's exception. The system knows all
what need: name, text, sqlstate. And in mostly time I don't wont to
substitute text of system message. But if I wont to show it, I have to
copy it.

example:

raise exception div_by_zero; -- I wont to use system message, why not?

but now, I have to do
raise exception div_by_zero, 'division by zero ...'

Regards
Pavel

>
> I really don't like the idea of introducing a new concept into the
> language ("exception variables") to resolve some ambiguous syntax. It
> would be another matter if exception variables actually provided
> something that strings do not...
>

In this time e.variables does it - only holds sqlstate and name.

You see only raise stmt. But there is part of begin exception block too.
without e.v. you have to catch users exception only via OTHERS or you have
to change syntax.

EXCEPTION WHEN SQLSTATE('0000') THEN

e.v. solve this problem. And I hope so can hold others info in future

Pavel

Tom Lane wrote:
> RAISE NOTICE '12' !! '345', ...
>
> Is !! an infix operator (using both strings as arguments) or a postfix
> operator (in which case '345' is the format)?

Ah, I see. I would be content to allow opt_sqlstate to be either a
string literal, a T_WORD (predefined error condition), or a TEXT
variable. If users need to throw a sqlstate that is derived from a SQL
expression, they can always assign to a TEXT variable and then specify
that variable to RAISE.

>> RAISE [ opt_sqlstate ] LEVEL 'fmt' [ , expr ... ]

This syntax might be slightly better anyway, as allowing two string
literals without any intervening tokens is a bit ugly. We would still
need to restrict opt_sqlstate as described above, though.

-Neil

Neil Conway wrote:
> Ah, I see. I would be content to allow opt_sqlstate to be either a
> string literal, a T_WORD (predefined error condition), or a TEXT
> variable. If users need to throw a sqlstate that is derived from a SQL
> expression, they can always assign to a TEXT variable and then specify
> that variable to RAISE.

>>> RAISE [ opt_sqlstate ] LEVEL 'fmt' [ , expr ... ]

BTW, do have we reached a consensus on this?

-Neil

Neil Conway <neilc(at)samurai(dot)com> writes:
> BTW, do have we reached a consensus on this?

Doesn't look that way --- I tend to agree with you that we could avoid
inventing declared exceptions at all, but Pavel is definitely not happy
with it, and AFAIR no one else has weighed in. Maybe we need to take
the discussion back to pghackers to draw a wider audience.

regards, tom lane

On Fri, 8 Jul 2005, Neil Conway wrote:

> Neil Conway wrote:
> > Ah, I see. I would be content to allow opt_sqlstate to be either a
> > string literal, a T_WORD (predefined error condition), or a TEXT
> > variable. If users need to throw a sqlstate that is derived from a SQL
> > expression, they can always assign to a TEXT variable and then specify
> > that variable to RAISE.
>
> >>> RAISE [ opt_sqlstate ] LEVEL 'fmt' [ , expr ... ]
>
> BTW, do have we reached a consensus on this?
>
> -Neil
>

ok, but don't forget, please, on exception part.

Pavel

Pavel Stehule wrote:
> ok, but don't forget, please, on exception part.

What do you mean?

-Neil

On Fri, 8 Jul 2005, Tom Lane wrote:

> Neil Conway <neilc(at)samurai(dot)com> writes:
> > BTW, do have we reached a consensus on this?
>
> Doesn't look that way --- I tend to agree with you that we could avoid
> inventing declared exceptions at all, but Pavel is definitely not happy
> with it, and AFAIR no one else has weighed in. Maybe we need to take
> the discussion back to pghackers to draw a wider audience.
>

I am not happy (this is only half of step), but I don't expect better
discussion. My opinion is so exception variable has more possibilities,
but this solution is usefull and funkcional too. And we can introduce
exception variables later without problems if will be good time.

Discussion on pghackers was, but not too much people contributed. And
more, I don't see user's exception as big qustion this days.

> regards, tom lane
>

On Fri, 8 Jul 2005, Neil Conway wrote:

> Pavel Stehule wrote:
> > ok, but don't forget, please, on exception part.
>
> What do you mean?
>
> -Neil
>

BEGIN
EXCEPTION WHEN * THEN

equvalent rules for raise have to be in *

is true?

Pavel

Pavel Stehule wrote:
> BEGIN
> EXCEPTION WHEN * THEN
>
> equvalent rules for raise have to be in *
>
> is true?

I'm sorry, but I'm still not sure what you mean.

-Neil

Where are we on this patch? Is there something to apply?

---------------------------------------------------------------------------

Pavel Stehule wrote:
> On Fri, 8 Jul 2005, Neil Conway wrote:
>
> > Pavel Stehule wrote:
> > > ok, but don't forget, please, on exception part.
> >
> > What do you mean?
> >
> > -Neil
> >
>
> BEGIN
> EXCEPTION WHEN * THEN
>
> equvalent rules for raise have to be in *
>
> is true?
>
> Pavel
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
> Where are we on this patch? Is there something to apply?

Not at the moment. I believe we have agreed that it would be better to
remove the concept of "exception variables" and just use strings, but I
haven't implemented this yet. I'm happy to do that, but I might not get
a chance till Wednesday.

-Neil

Neil Conway wrote:
> Not at the moment. I believe we have agreed that it would be better to
> remove the concept of "exception variables" and just use strings, but I
> haven't implemented this yet.

BTW, one minor annoyance I noticed: a builtin condition name can
actually map to multiple SQLSTATE values. If we allow a builtin
condition name to be specified to RAISE, this means we'll actually need
to pass around a list of SQLSTATE values that are thrown by the RAISE,
rather than a single SQLSTATE. This seems pretty ugly, though --
especially considering that only a handful of the builtin condition
names actually do map to multiple SQLSTATEs. Does anyone have a better
suggestion?

-Neil

On Tue, 26 Jul 2005, Neil Conway wrote:

> Neil Conway wrote:
> > Not at the moment. I believe we have agreed that it would be better to
> > remove the concept of "exception variables" and just use strings, but I
> > haven't implemented this yet.
>
> BTW, one minor annoyance I noticed: a builtin condition name can
> actually map to multiple SQLSTATE values.

can you show sample, please?

If we allow a builtin
> condition name to be specified to RAISE, this means we'll actually need
> to pass around a list of SQLSTATE values that are thrown by the RAISE,
> rather than a single SQLSTATE. This seems pretty ugly, though --
> especially considering that only a handful of the builtin condition
> names actually do map to multiple SQLSTATEs. Does anyone have a better
> suggestion?
>

Exception variables can solve it, but its dead concept. We can have list
of prohibited condition names and for its throw compile error

condition name is ambigous

Pavel

> -Neil
>

hello,
sorry, exception variables don't solve this problem too. But we can detect
it in compile-time. I don't wont to complicate raise syntax.

best regards
Pavel

Pavel Stehule wrote:
> can you show sample, please?

modifying_sql_data_not_permitted, null_value_not_allowed,
prohibited_sql_statement_attempted and reading_sql_data_not_permitted
are the examples I can see from scanning plerrcodes.h. If we had this to
do over again, I'm not sure I see the point in mapping a single
condition names to multiple SQLSTATEs, but it's probably too late to
undo that now.

> Exception variables can solve it, but its dead concept. We can have list
> of prohibited condition names and for its throw compile error condition
> name is ambigous

Yeah, that's possible, but it doesn't seem any nicer :-(

-Neil

Neil Conway <neilc(at)samurai(dot)com> writes:
> Pavel Stehule wrote:
>> can you show sample, please?

> modifying_sql_data_not_permitted, null_value_not_allowed,
> prohibited_sql_statement_attempted and reading_sql_data_not_permitted
> are the examples I can see from scanning plerrcodes.h. If we had this to
> do over again, I'm not sure I see the point in mapping a single
> condition names to multiple SQLSTATEs, but it's probably too late to
> undo that now.

Those cases are for places where the spec defines similar cases under
the error classes "SQL Routine Exception" and "External Routine Exception".
You can blame me for having assumed that plpgsql didn't need to
distinguish these cases.

A quick grep says that the only one of these codes being generated today
is
contrib/dblink/dblink.c: (errcode(ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED),
and that's for a "you should not do that" case, which it's very unlikely
anyone is specifically trapping for. So I see no
backwards-compatibility argument that we can't change this. How would
you want to do it better?

regards, tom lane

Tom Lane wrote:
> Those cases are for places where the spec defines similar cases under
> the error classes "SQL Routine Exception" and "External Routine Exception".
> You can blame me for having assumed that plpgsql didn't need to
> distinguish these cases.

Well, in and of itself, I agree it is probably better to combine similar
SQLSTATEs into a single logical condition. However, considering the
problem it poses for implementing RAISE with builtin condition names,
IMHO it would be a net win to get rid of it, if we can't find a better
solution.

> So I see no backwards-compatibility argument that we can't change
> this. How would you want to do it better?

I would just change the mapping from condition names to SQLSTATEs to be
one-to-one. If a client application does need to trap multiple SQLSTATEs
for a logically similar condition, they can always specify "WHEN x OR y
OR ..."

-Neil