Re: [PATCHES] User's exception plpgsql
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] User's exception plpgsql
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] User's exception plpgsql
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] User's exception plpgsql
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] User's exception plpgsql
Neil Conway [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] User's exception plpgsql
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@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] User's exception plpgsql
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] User's exception plpgsql
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] User's exception plpgsql
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] User's exception plpgsql
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] User's exception plpgsql
Neil Conway [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] User's exception plpgsql
Pavel Stehule wrote: ok, but don't forget, please, on exception part. What do you mean? -Neil ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] User's exception plpgsql
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
Re: [PATCHES] User's exception plpgsql
On Fri, 8 Jul 2005, Tom Lane wrote: Neil Conway [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] User's exception plpgsql
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 Index: doc/src/sgml/plpgsql.sgml === RCS file: /Users/neilc/local/cvs/pgsql/doc/src/sgml/plpgsql.sgml,v retrieving revision 1.75 diff -c -r1.75 plpgsql.sgml *** doc/src/sgml/plpgsql.sgml 2 Jul 2005 08:59:47 - 1.75 --- doc/src/sgml/plpgsql.sgml 6 Jul 2005 13:26:22 - *** *** 2117,2123 para The replaceablecondition/replaceable names can be any of those shown in xref linkend=errcodes-appendix. A category name matches ! any error within its category. The special condition name literalOTHERS/ matches every error type except literalQUERY_CANCELED/. (It is possible, but often unwise, to trap --- 2117,2125 para The replaceablecondition/replaceable names can be any of those shown in xref linkend=errcodes-appendix. A category name matches ! any error within its category. You can use exception variable as ! condition name. Exception variable is declared with type ! literalEXCEPTION/literal The special condition name literalOTHERS/ matches every error type except literalQUERY_CANCELED/. (It is possible, but often unwise, to trap *** *** 2571,2577 raise errors. synopsis ! RAISE replaceable class=parameterlevel/replaceable 'replaceable class=parameterformat/replaceable' optional, replaceable class=parameterexpression/replaceable optional, .../optional/optional; /synopsis Possible levels are literalDEBUG/literal, --- 2573,2580 raise errors. synopsis ! RAISE replaceable class=parameterlevel/replaceable ! optionalsystem exception|exception variable/optional 'replaceable class=parameterformat/replaceable' optional, replaceable class=parameterexpression/replaceable optional, .../optional/optional; /synopsis Possible levels are literalDEBUG/literal, *** *** 2588,2593 --- 2591,2600 variables. See xref linkend=runtime-config for more information. /para + +para +You can specify any system exception or any user exception. +/para para Inside the format string, literal%/literal is replaced by the Index: src/include/utils/elog.h === RCS file: /Users/neilc/local/cvs/pgsql/src/include/utils/elog.h,v retrieving revision 1.79 diff -c -r1.79 elog.h *** src/include/utils/elog.h10 Jun 2005 16:23:10 - 1.79 --- src/include/utils/elog.h6 Jul 2005 13:26:22 - *** *** 61,66 --- 61,72 (PGSIXBIT(ch1) + (PGSIXBIT(ch2) 6) + (PGSIXBIT(ch3) 12) + \ (PGSIXBIT(ch4) 18) + (PGSIXBIT(ch5) 24)) + #define MAKE_SQLSTATE_STR(str) \ + ( \ + AssertMacro(strlen(str) == 5), \ + MAKE_SQLSTATE(str[0], str[1], str[2], str[3], str[4]) \ + ) + /* These macros depend on the fact that '0' becomes a zero in SIXBIT */ #define ERRCODE_TO_CATEGORY(ec) ((ec) ((1 12) - 1)) #define ERRCODE_IS_CATEGORY(ec) (((ec) ~((1 12) - 1)) == 0) Index: src/pl/plpgsql/src/gram.y === RCS file: /Users/neilc/local/cvs/pgsql/src/pl/plpgsql/src/gram.y,v retrieving revision 1.80 diff -c -r1.80 gram.y *** src/pl/plpgsql/src/gram.y 2 Jul 2005 17:01:59 - 1.80 --- src/pl/plpgsql/src/gram.y 6 Jul 2005 13:38:35 - *** *** 103,108 --- 103,109 PLpgSQL_exception_block *exception_block; PLpgSQL_nsitem
Re: [PATCHES] User's exception plpgsql
Neil Conway [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [PATCHES] User's exception plpgsql
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] User's exception plpgsql
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; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] User's exception plpgsql
Neil Conway [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] User's exception plpgsql
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
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] User's exception plpgsql
Pavel Stehule [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] User's exception plpgsql
On Wed, 6 Jul 2005, Tom Lane wrote: Pavel Stehule [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] User's exception plpgsql
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('') THEN
e.v. solve this problem. And I hope so can hold others info in future
Pavel
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] User's exception plpgsql
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] User's exception plpgsql
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] User's exception plpgsql
Neil Conway [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] User's exception plpgsql
On Mon, 4 Jul 2005, Tom Lane wrote: Neil Conway [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
