Re: [PATCHES] PL/pgSQL: SELECT INTO EXACT
On Fri, 2005-08-12 at 21:53 -0400, Bruce Momjian wrote: This has been saved for the 8.2 release: Just to clarify: the SELECT INTO EXACT patch was abandoned in favor of the #option select_into_1_row patch. I submitted both patches as part of the same -patches thread, but the latter solution, the #option select_into_1_row patch, superseded the SELECT INTO EXACT idea. The correct patch is at http://archives.postgresql.org/pgsql-patches/2005-08/msg00070.php This should be the only patch that gets applied. ---(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] PL/pgSQL: #option select_into_1_row (was SELECT INTO
On Tue, 2005-08-09 at 15:01 +, Matt Miller wrote: Attached is a patch that implements the #option select_into_1_row directive as suggested. Is this patch good-to-go? Can it be queued? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] 5 new entries for FAQ
PIf you're really desparate, you can increase the pagesize a bit (to Should be desperate, not desparate. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] PL/pgSQL: #option select_into_1_row (was SELECT INTO
On Mon, 2005-08-08 at 17:18 -0400, Tom Lane wrote:
If you think that this should be a global option instead of a
per-statement one, something like the (undocumented) #option hack might
be a good way to specify it; that would give it per-function scope,
which seems reasonable.
create function myfn(...) returns ... as $$
#option select_into_1_row
declare ...
$$ language plpgsql;
Attached is a patch that implements the #option select_into_1_row
directive as suggested.
Is it time to document this directive?
Index: src/pl/plpgsql/src/gram.y
===
RCS file: /var/local/pgcvs/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 8 Aug 2005 22:53:36 -
***
*** 224,229
--- 224,230
%token O_OPTION
%token O_DUMP
+ %token O_SELECT_INTO_1_ROW
%%
***
*** 249,254
--- 250,259
{
plpgsql_DumpExecTree = true;
}
+ | O_OPTION O_SELECT_INTO_1_ROW
+ {
+ plpgsql_SelectInto1Row = true;
+ }
;
opt_semi :
Index: src/pl/plpgsql/src/pl_comp.c
===
RCS file: /var/local/pgcvs/pgsql/src/pl/plpgsql/src/pl_comp.c,v
retrieving revision 1.92
diff -c -r1.92 pl_comp.c
*** src/pl/plpgsql/src/pl_comp.c 6 Jul 2005 16:42:10 - 1.92
--- src/pl/plpgsql/src/pl_comp.c 9 Aug 2005 14:32:43 -
***
*** 78,83
--- 78,84
int plpgsql_error_lineno;
char *plpgsql_error_funcname;
bool plpgsql_DumpExecTree = false;
+ bool plpgsql_SelectInto1Row = false;
bool plpgsql_check_syntax = false;
PLpgSQL_function *plpgsql_curr_compile;
***
*** 309,314
--- 310,316
plpgsql_ns_init();
plpgsql_ns_push(NULL);
plpgsql_DumpExecTree = false;
+ plpgsql_SelectInto1Row = false;
datums_alloc = 128;
plpgsql_nDatums = 0;
Index: src/pl/plpgsql/src/pl_exec.c
===
RCS file: /var/local/pgcvs/pgsql/src/pl/plpgsql/src/pl_exec.c,v
retrieving revision 1.151
diff -c -r1.151 pl_exec.c
*** src/pl/plpgsql/src/pl_exec.c 28 Jul 2005 07:51:13 - 1.151
--- src/pl/plpgsql/src/pl_exec.c 9 Aug 2005 14:49:45 -
***
*** 1649,1665
elog(ERROR, unsupported target);
/*
! * Run the query
*/
! exec_run_select(estate, stmt-query, 1, NULL);
tuptab = estate-eval_tuptable;
n = estate-eval_processed;
/*
! * If the query didn't return any rows, set the target to NULL and
! * return.
*/
! if (n == 0)
{
exec_move_row(estate, rec, row, NULL, tuptab-tupdesc);
exec_eval_cleanup(estate);
--- 1649,1672
elog(ERROR, unsupported target);
/*
! * Run the query, bringing back up to 2 rows if necessary
*/
! exec_run_select(estate, stmt-query, plpgsql_SelectInto1Row ? 2 : 1, NULL);
tuptab = estate-eval_tuptable;
n = estate-eval_processed;
/*
! * If the #option select_into_1_row directive was specified, and the query didn't
! * find exactly 1 row, then exit without setting the target. If this directive was
! * not specified then set the target, either to NULL if no rows were found or to
! * the value of the first row found.
*/
! if (plpgsql_SelectInto1Row n != 1)
! {
! exec_eval_cleanup(estate);
! return PLPGSQL_RC_OK;
! }
! else if (n == 0)
{
exec_move_row(estate, rec, row, NULL, tuptab-tupdesc);
exec_eval_cleanup(estate);
Index: src/pl/plpgsql/src/plpgsql.h
===
RCS file: /var/local/pgcvs/pgsql/src/pl/plpgsql/src/plpgsql.h,v
retrieving revision 1.64
diff -c -r1.64 plpgsql.h
*** src/pl/plpgsql/src/plpgsql.h 22 Jun 2005 01:35:02 - 1.64
--- src/pl/plpgsql/src/plpgsql.h 8 Aug 2005 22:53:36 -
***
*** 644,649
--- 644,650
**/
extern bool plpgsql_DumpExecTree;
+ extern bool plpgsql_SelectInto1Row;
extern bool plpgsql_SpaceScanned;
extern int plpgsql_nDatums;
extern PLpgSQL_datum **plpgsql_Datums;
Index: src/pl/plpgsql/src/scan.l
===
RCS file: /var/local/pgcvs/pgsql/src/pl/plpgsql/src/scan.l,v
retrieving revision 1.42
diff -c -r1.42 scan.l
*** src/pl/plpgsql/src/scan.l 26 Jun 2005 19:16:07 - 1.42
--- src/pl/plpgsql/src/scan.l 8 Aug 2005 22:53:36 -
***
*** 186,191
--- 186,192
^#option { return O_OPTION; }
dump { return O_DUMP; }
+ select_into_1_row { return O_SELECT_INTO_1_ROW; }
/* --
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PATCHES] PL/pgSQL: SELECT INTO EXACT
On Fri, 2005-07-29 at 17:52 -0400, Tom Lane wrote: Matt Miller [EMAIL PROTECTED] writes: This patch implements an optional EXACT keyword after the INTO keyword of the PL/pgSQL SELECT INTO command. ... when SELECTing INTO ... leave the targets untouched if the query does not return exactly one row. I dislike the choice of EXACT, too, as it (a) adds a new reserved word and (b) doesn't seem to convey quite what is happening anyway. Not sure about a better word though ... anyone? I don't know how to avoid adding a keyword, unless the proposed EXACT behavior just replaces the current behavior, potentially breaking existing code. Is there a precedent for language-specific GUC vars? I think the EXACT behavior is more reasonable overall, and maybe a stepped approach can replace the current behavior with the EXACT flavor. To that end the option could support either EXACT or NOEXACT, with NOEXACT initially being the default. Eventually EXACT could become the default, and finally the NOEXACT option could be dropped altogether. At that point the EXACT keyword would be dropped as well. I can attach a patch that supports [EXACT | NOEXACT]. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] PL/pgSQL: SELECT INTO EXACT
On Mon, 2005-08-08 at 17:18 -0400, Tom Lane wrote: Matt Miller [EMAIL PROTECTED] writes: On Fri, 2005-07-29 at 17:52 -0400, Tom Lane wrote: I dislike the choice of EXACT, too, as it (a) adds a new reserved word and (b) doesn't seem to convey quite what is happening anyway. Not sure about a better word though ... anyone? I can attach a patch that supports [EXACT | NOEXACT]. Somehow, proposing two new reserved words instead of one doesn't seem very responsive to my gripe :-(. My intention was to introduce the idea that the current behavior should be changed, and to then suggest a path that eventually eliminates all the new reserved words. If you think that this should be a global option instead of a per-statement one, something like the (undocumented) #option hack might be a good way to specify it; that would give it per-function scope, which seems reasonable. create function myfn(...) returns ... as $$ #option select_into_1_row declare ... $$ language plpgsql; Thanks, I'll take a look at this. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PATCHES] PL/pgSQL: EXCEPTION NOSAVEPOINT
This was motivated by the SELECT INTO EXACT discussion at http://archives.postgresql.org/pgsql-patches/2005-07/msg00559.php. The idea is to allow a PL/pgSQL exception to not automatically rollback the work done by the current block. The benefit is that exception handling can be used as a program flow control technique, without invoking transaction management mechanisms. This also adds additional means to enhanced Oracle PL/SQL compatibility. The patch implements an optional NOSAVEPOINT keyword after the EXCEPTION keyword that begins the exception handler definition. Here is an excerpt from the patched documentation: beginning of excerpt--- If NOSAVEPOINT is not specified then a transaction savepoint is established immediately prior to the execution of statements. If an exception is raised then the effects of statements on the database are rolled back to this savepoint. If NOSAVEPOINT is specified then no savepoint is established. In this case a handled exception does not roll back the effects of statements. An unhandled exception, however, will still propagate out as usual, and any database effects may or may not be rolled back, depending on the characteristics of the enclosing block(s). Tip: Establishing a savepoint can be expensive. If you do not need the ability rollback the block's effect on the database, then either use the NOSAVEPOINT option, or avoid the EXCEPTION clause altogether. end of excerpt--- Implementation question: In pl_exec.c the new option causes the BeginInternalSubTransaction, ReleaseCurrentSubTransaction, and RollbackAndReleaseCurrentSubTransaction function calls to be skipped. However, the corresponding MemoryContextSwitchTo and related calls are still performed. Should these calls also be dependent on the new option? Would that be more correct, and/or a performance improvement? Index: doc/src/sgml/plpgsql.sgml === RCS file: /var/local/pgcvs/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 3 Aug 2005 19:42:48 - *** *** 2086,2092 replaceabledeclarations/replaceable /optional BEGIN replaceablestatements/replaceable ! EXCEPTION WHEN replaceablecondition/replaceable optional OR replaceablecondition/replaceable ... /optional THEN replaceablehandler_statements/replaceable optional WHEN replaceablecondition/replaceable optional OR replaceablecondition/replaceable ... /optional THEN --- 2086,2092 replaceabledeclarations/replaceable /optional BEGIN replaceablestatements/replaceable ! EXCEPTION optionalNOSAVEPOINT/optional WHEN replaceablecondition/replaceable optional OR replaceablecondition/replaceable ... /optional THEN replaceablehandler_statements/replaceable optional WHEN replaceablecondition/replaceable optional OR replaceablecondition/replaceable ... /optional THEN *** *** 2104,2117 processing of the replaceablestatements/replaceable is abandoned, and control passes to the literalEXCEPTION/ list. The list is searched for the first replaceablecondition/replaceable ! matching the error that occurred. If a match is found, the ! corresponding replaceablehandler_statements/replaceable are ! executed, and then control passes to the next statement after ! literalEND/. If no match is found, the error propagates out ! as though the literalEXCEPTION/ clause were not there at all: ! the error can be caught by an enclosing block with ! literalEXCEPTION/, or if there is none it aborts processing ! of the function. /para para --- 2104,2140 processing of the replaceablestatements/replaceable is abandoned, and control passes to the literalEXCEPTION/ list. The list is searched for the first replaceablecondition/replaceable ! matching the error that occurred. If a match is found, then the ! exception is considered handled, and the corresponding ! replaceablehandler_statements/replaceable are executed. Control ! then passes to the next statement after literalEND/. If no match ! is found, the unhandled error propagates out as though the ! literalEXCEPTION/ clause were not there at all. The error can then ! be caught by an enclosing block with literalEXCEPTION/, or if there ! is none it aborts processing of the function. ! /para ! ! para ! If literalNOSAVEPOINT/literal is not specified then a transaction ! savepoint is established immediately prior to the execution of ! replaceablestatements/replaceable. If an exception is raised then ! the effects of replaceablestatements/replaceable on the database ! are rolled back to
Re: [PATCHES] PL/pgSQL: EXCEPTION NOSAVEPOINT
On Wed, 2005-08-03 at 16:25 -0400, Tom Lane wrote: The idea is to allow a PL/pgSQL exception to not automatically rollback the work done by the current block. This fundamentally breaks the entire backend. Yeah, but besides that, can you quick commit this to HEAD so I don't have to keep track of it locally? Just kidding. You do not have the option to continue processing after elog(ERROR); the (sub)transaction rollback is necessary to clean up inconsistent state. Okay, I'll look at this more closely. Can you give me an example of what can go wrong? ---(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
[PATCHES] PL/pgSQL: SELECT INTO EXACT
This patch implements an optional EXACT keyword after the INTO keyword of the PL/pgSQL SELECT INTO command. The motivation is to come closer to Oracle's SELECT INTO behavior: when SELECTing INTO scalar targets, raise an exception and leave the targets untouched if the query does not return exactly one row. This patch does not go so far as to raise an exception, but it can simplify porting efforts from PL/SQL. I also feel that this EXACT behavior is overall a bit cleaner than the current PL/pgSQL behavior. Maybe I've just been brainwashed by years of Oracle'ing. Here are three excerpts from the patched PL/pgSQL documentation: If the EXACT option is specified, then target will not be set unless the query returns exactly one row You can check the special FOUND variable after a SELECT INTO to determine whether the statement was successful. ... an EXACT query is successful only if exactly 1 row is returned. ...GET DIAGNOSTICS (see Section 35.6.6) to retrieve ROW_COUNT. After a SELECT INTO EXACT statement ROW_COUNT ... will be equal to 0, 1, or 2, indicating no matching rows, exactly one matching row, or greater than one matching row, respectively. Index: doc/src/sgml/plpgsql.sgml === RCS file: /var/local/pgcvs/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 29 Jul 2005 19:19:56 - *** *** 1067,1073 variable, or list of scalar variables. This is done by: synopsis ! SELECT INTO replaceabletarget/replaceable replaceableselect_expressions/replaceable FROM ...; /synopsis where replaceabletarget/replaceable can be a record variable, a row --- 1067,1073 variable, or list of scalar variables. This is done by: synopsis ! SELECT INTO optionalEXACT/optional replaceabletarget/replaceable replaceableselect_expressions/replaceable FROM ...; /synopsis where replaceabletarget/replaceable can be a record variable, a row *** *** 1108,1126 /para para ! If the query returns zero rows, null values are assigned to the ! target(s). If the query returns multiple rows, the first ! row is assigned to the target(s) and the rest are discarded. ! (Note that quotethe first row/ is not well-defined unless you've ! used literalORDER BY/.) /para para ! You can check the special literalFOUND/literal variable (see ! xref linkend=plpgsql-statements-diagnostics) after a ! commandSELECT INTO/command statement to determine whether the ! assignment was successful, that is, at least one row was was returned by ! the query. For example: programlisting SELECT INTO myrec * FROM emp WHERE empname = myname; --- 1108,1130 /para para ! If the literalEXACT/literal option is specified, then ! replaceabletarget/replaceable will not be set unless the query ! returns exactly one row. If literalEXACT/literal is not ! specified then replaceabletarget/replaceable will be set ! regardless of the number of rows returned by the query. In the ! non-literalEXACT/literal case, null values are assigned if the ! query returns zero rows, and the first row is assigned if the query ! returns more than 1 row. (Note that quotethe first row/ is not ! well-defined unless you've used literalORDER BY/.) /para para ! You can check the special literalFOUND/literal variable after a ! commandSELECT INTO/command to determine whether the statement was ! successful. A non-literalEXACT/literal query is considered successful ! if any rows are returned, and an literalEXACT/literal query is ! successful only if exactly 1 row is returned. For example: programlisting SELECT INTO myrec * FROM emp WHERE empname = myname; *** *** 1128,1141 RAISE EXCEPTION 'employee % not found', myname; END IF; /programlisting /para para ! To test for whether a record/row result is null, you can use the ! literalIS NULL/literal conditional. There is, however, no ! way to tell whether any additional rows might have been ! discarded. Here is an example that handles the case where no ! rows have been returned: programlisting DECLARE users_rec RECORD; --- 1132,1196 RAISE EXCEPTION 'employee % not found', myname; END IF; /programlisting + + programlisting + SELECT INTO EXACT myrec * FROM emp WHERE empname = myname; + IF NOT FOUND THEN + RAISE EXCEPTION 'employee % not found or not unique', myname; + END IF; + /programlisting + /para + + para + When using the literalEXACT/literal option you can distinguish the + not-found case from the not-unique case by using + commandGET DIAGNOSTICS/command (see + xref
Re: [PATCHES] PL/pgSQL: SELECT INTO EXACT
On Fri, 2005-07-29 at 17:52 -0400, Tom Lane wrote: Matt Miller [EMAIL PROTECTED] writes: The motivation is to come closer to Oracle's SELECT INTO behavior: when SELECTing INTO scalar targets, raise an exception and leave the targets untouched if the query does not return exactly one row. This patch does not go so far as to raise an exception Uh, what's the point of being only sort-of compatible? Why not throw the exception? I guess my hesitation is that the PL/SQL notion of the exception as a program flow control technique is a bit at odds with the PL/pgSQL notion of the exception as a transaction control mechanism. Maybe these notions could be reconciled by a new NOSAVE option to the EXCEPTION block definition, to suppress the savepoint and the exception-induced rollback for that BEGIN ... END block. Then an automatically-thrown exception would not be so expensive. I dislike the choice of EXACT, too, as it (a) adds a new reserved word and (b) doesn't seem to convey quite what is happening anyway The motivation is that EXACTly one row must be returned. Maybe UNIQUE instead of EXACT? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] PL/pgSQL: SELECT INTO EXACT
The motivation is to come closer to Oracle's SELECT INTO behavior: when SELECTing INTO scalar targets, raise an exception and leave the targets untouched if the query does not return exactly one row. why that is not the default behavior of the SELECT INTO? ... i mean, when you do that code you are expecting just one row from your query I agree. I suppose I was fearful of breaking existing stuff, so I added a new keyword. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
