Index: contrib/tablefunc/tablefunc.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/contrib/tablefunc/tablefunc.c,v retrieving revision 1.14 diff -c -r1.14 tablefunc.c *** contrib/tablefunc/tablefunc.c 20 Mar 2003 06:46:30 -0000 1.14 --- contrib/tablefunc/tablefunc.c 24 Mar 2003 02:36:08 -0000 *************** *** 853,859 **** MemoryContext SPIcontext; /* initialize our tuplestore */ ! tupstore = tuplestore_begin_heap(true, SortMem); /* Connect to SPI manager */ if ((ret = SPI_connect()) < 0) --- 853,859 ---- MemoryContext SPIcontext; /* initialize our tuplestore */ ! tupstore = tuplestore_begin_heap(true, false, SortMem); /* Connect to SPI manager */ if ((ret = SPI_connect()) < 0) *************** *** 1124,1130 **** oldcontext = MemoryContextSwitchTo(per_query_ctx); /* initialize our tuplestore */ ! tupstore = tuplestore_begin_heap(true, SortMem); MemoryContextSwitchTo(oldcontext); --- 1124,1130 ---- oldcontext = MemoryContextSwitchTo(per_query_ctx); /* initialize our tuplestore */ ! tupstore = tuplestore_begin_heap(true, false, SortMem); MemoryContextSwitchTo(oldcontext); Index: doc/src/sgml/sql.sgml =================================================================== RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/sql.sgml,v retrieving revision 1.29 diff -c -r1.29 sql.sgml *** doc/src/sgml/sql.sgml 19 Feb 2003 04:06:28 -0000 1.29 --- doc/src/sgml/sql.sgml 23 Mar 2003 21:32:10 -0000 *************** *** 851,857 **** The most often used command in SQL is the ! SELECT statement, used to retrieve data. The syntax is: --- 851,857 ---- The most often used command in SQL is the ! SELECT statement, used to retrieve data. The syntax is: *************** *** 881,887 **** Simple Selects ! Here are some simple examples using a SELECT statement: Simple Query with Qualification --- 881,887 ---- Simple Selects ! Here are some simple examples using a SELECT statement: Simple Query with Qualification *************** *** 905,913 **** ! Using * in the SELECT statement will deliver all attributes from ! the table. If we want to retrieve only the attributes PNAME and PRICE ! from table PART we use the statement: SELECT PNAME, PRICE --- 905,914 ---- ! Using * in the SELECT statement ! will deliver all attributes from the table. If we want to retrieve ! only the attributes PNAME and PRICE from table PART we use the ! statement: SELECT PNAME, PRICE *************** *** 924,932 **** Cam | 25 ! Note that the SQL SELECT corresponds to the ! projection in relational algebra not to the ! selection (see for more details). --- 925,933 ---- Cam | 25 ! Note that the SQL SELECT ! corresponds to the projection in relational algebra ! not to the selection (see for more details). *************** *** 1252,1266 **** Aggregate Operators ! SQL provides aggregate operators ! (e.g. AVG, COUNT, SUM, MIN, MAX) that ! take an expression as argument. The expression is evaluated at ! each row that satisfies the WHERE clause, and the aggregate operator ! is calculated over this set of input values. Normally, an aggregate ! delivers a single result for a whole SELECT statement. But if ! grouping is specified in the query, then a separate calculation is done ! over the rows of each group, and an aggregate result is delivered per ! group (see next section). Aggregates --- 1253,1267 ---- Aggregate Operators ! SQL provides aggregate operators (e.g. AVG, ! COUNT, SUM, MIN, MAX) that take an expression as argument. The ! expression is evaluated at each row that satisfies the WHERE ! clause, and the aggregate operator is calculated over this set ! of input values. Normally, an aggregate delivers a single ! result for a whole SELECT statement. But if ! grouping is specified in the query, then a separate calculation ! is done over the rows of each group, and an aggregate result is ! delivered per group (see next section). Aggregates *************** *** 1413,1423 **** ! Also observe that it makes no sense to ask for an aggregate of an ! aggregate, e.g., AVG(MAX(sno)), because a SELECT only does one pass ! of grouping and aggregation. You can get a result of this kind by ! using a temporary table or a sub-SELECT in the FROM clause to ! do the first level of aggregation. --- 1414,1425 ---- ! Also observe that it makes no sense to ask for an aggregate of ! an aggregate, e.g., AVG(MAX(sno)), because a ! SELECT only does one pass of grouping and ! aggregation. You can get a result of this kind by using a ! temporary table or a sub-SELECT in the FROM clause to do the ! first level of aggregation. *************** *** 1502,1517 **** ! When we look at the above query we can see ! the keyword SELECT two times. The first one at the beginning of the ! query - we will refer to it as outer SELECT - and the one in the WHERE ! clause which begins a nested query - we will refer to it as inner ! SELECT. For every tuple of the outer SELECT the inner SELECT has to be ! evaluated. After every evaluation we know the price of the tuple named ! 'Screw' and we can check if the price of the actual tuple is ! greater. (Actually, in this example the inner query need only be ! evaluated once, since it does not depend on the state of the outer ! query.) --- 1504,1521 ---- ! When we look at the above query we can see the keyword ! SELECT two times. The first one at the ! beginning of the query - we will refer to it as outer ! SELECT - and the one in the WHERE clause which ! begins a nested query - we will refer to it as inner ! SELECT. For every tuple of the outer ! SELECT the inner SELECT has ! to be evaluated. After every evaluation we know the price of the ! tuple named 'Screw' and we can check if the price of the actual ! tuple is greater. (Actually, in this example the inner query need ! only be evaluated once, since it does not depend on the state of ! the outer query.) *************** *** 1528,1538 **** ! In our example the result will be empty because every supplier sells ! at least one part. Note that we use S.SNO from the outer SELECT within ! the WHERE clause of the inner SELECT. Here the subquery must be ! evaluated afresh for each tuple from the outer query, i.e. the value for ! S.SNO is always taken from the current tuple of the outer SELECT. --- 1532,1544 ---- ! In our example the result will be empty because every supplier ! sells at least one part. Note that we use S.SNO from the outer ! SELECT within the WHERE clause of the inner ! SELECT. Here the subquery must be evaluated ! afresh for each tuple from the outer query, i.e. the value for ! S.SNO is always taken from the current tuple of the outer ! SELECT. *************** *** 1670,1676 **** The most fundamental command for data definition is the one that creates a new relation (a new table). The syntax of the ! CREATE TABLE command is: CREATE TABLE table_name --- 1676,1682 ---- The most fundamental command for data definition is the one that creates a new relation (a new table). The syntax of the ! CREATE TABLE command is: CREATE TABLE table_name *************** *** 1786,1792 **** To create an index in SQL ! the CREATE INDEX command is used. The syntax is: CREATE INDEX index_name --- 1792,1798 ---- To create an index in SQL ! the CREATE INDEX command is used. The syntax is: CREATE INDEX index_name *************** *** 1808,1817 **** ! The created index is maintained automatically, i.e. whenever a new tuple ! is inserted into the relation SUPPLIER the index I is adapted. Note ! that the only changes a user can perceive when an index is present ! are increased speed for SELECT and decreases in speed of updates. --- 1814,1824 ---- ! The created index is maintained automatically, i.e. whenever a new ! tuple is inserted into the relation SUPPLIER the index I is ! adapted. Note that the only changes a user can perceive when an ! index is present are increased speed for SELECT ! and decreases in speed of updates. *************** *** 1916,1922 **** To destroy a table (including all tuples stored in that table) the ! DROP TABLE command is used: DROP TABLE table_name; --- 1923,1929 ---- To destroy a table (including all tuples stored in that table) the ! DROP TABLE command is used: DROP TABLE table_name; *************** *** 1932,1938 **** ! The DROP INDEX command is used to destroy an index: DROP INDEX index_name; --- 1939,1945 ---- ! The DROP INDEX command is used to destroy an index: DROP INDEX index_name; *************** *** 1940,1946 **** ! Finally to destroy a given view use the command DROP VIEW: DROP VIEW view_name; --- 1947,1954 ---- ! Finally to destroy a given view use the command DROP ! VIEW: DROP VIEW view_name; *************** *** 1994,2000 **** To change one or more attribute values of tuples in a relation the ! UPDATE command is used. The syntax is: UPDATE table_name --- 2002,2008 ---- To change one or more attribute values of tuples in a relation the ! UPDATE command is used. The syntax is: UPDATE table_name *************** *** 2126,2132 **** need a mechanism to access every single tuple of the set of tuples returned by a SELECT statement. This mechanism can be provided by declaring a cursor. ! After that we can use the FETCH command to retrieve a tuple and set the cursor to the next tuple. --- 2134,2140 ---- need a mechanism to access every single tuple of the set of tuples returned by a SELECT statement. This mechanism can be provided by declaring a cursor. ! After that we can use the FETCH command to retrieve a tuple and set the cursor to the next tuple. Index: doc/src/sgml/ref/copy.sgml =================================================================== RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/ref/copy.sgml,v retrieving revision 1.40 diff -c -r1.40 copy.sgml *** doc/src/sgml/ref/copy.sgml 21 Sep 2002 18:32:54 -0000 1.40 --- doc/src/sgml/ref/copy.sgml 17 Mar 2003 06:40:44 -0000 *************** *** 133,141 **** ! On a copy in, any data item that matches this string will be stored as ! a NULL value, so you should make sure that you use the same string ! as you used on copy out. --- 133,142 ---- ! On a COPY FROM, any data item that matches ! this string will be stored as a NULL value, so you should ! make sure that you use the same string as you used with ! COPY TO. *************** *** 210,216 **** or write to a file. The file must be accessible to the backend and the name must be specified from the viewpoint of the backend. When stdin or stdout is ! specified, data flows through the client frontend to the backend. --- 211,218 ---- or write to a file. The file must be accessible to the backend and the name must be specified from the viewpoint of the backend. When stdin or stdout is ! specified, data is transmitted via the connection between the ! client frontend and the backend. *************** *** 234,241 **** Notes ! COPY can only be used with plain tables, not with ! views. --- 236,243 ---- Notes ! COPY can only be used with plain tables, not ! with views. Index: doc/src/sgml/ref/declare.sgml =================================================================== RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/ref/declare.sgml,v retrieving revision 1.20 diff -c -r1.20 declare.sgml *** doc/src/sgml/ref/declare.sgml 21 Mar 2003 17:11:46 -0000 1.20 --- doc/src/sgml/ref/declare.sgml 23 Mar 2003 21:09:09 -0000 *************** *** 21,28 **** 1999-07-20 ! DECLARE cursorname [ BINARY ] [ INSENSITIVE ] [ SCROLL ] ! CURSOR FOR query [ FOR { READ ONLY | UPDATE [ OF column [, ...] ] ] --- 21,28 ---- 1999-07-20 ! DECLARE cursorname [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] ! CURSOR [ { WITH | WITHOUT } HOLD ] FOR query [ FOR { READ ONLY | UPDATE [ OF column [, ...] ] ] *************** *** 38,44 **** cursorname ! The name of the cursor to be used in subsequent FETCH operations. --- 38,45 ---- cursorname ! The name of the cursor to be used in subsequent ! FETCH operations. *************** *** 57,64 **** SQL92 keyword indicating that data retrieved ! from the cursor should be unaffected by updates from other processes or cursors. ! By default, all cursors are insensitive. This keyword has no effect. --- 58,77 ---- SQL92 keyword indicating that data retrieved ! from the cursor should be unaffected by updates from other ! processes or cursors. By default, all cursors are insensitive. ! This keyword currently has no effect and is present for ! compatibility with the SQL standard. ! ! ! ! ! ! NO SCROLL ! ! ! Specifies that the cursor cannot be used to retrieve rows in a ! nonsequential fashion (e.g., backward). *************** *** 67,74 **** SCROLL ! Specifies that the cursor may be used to retrieve rows ! in a nonsequential fashion (e.g., backwards). --- 80,112 ---- SCROLL ! Specifies that the cursor may be used to retrieve rows in a ! nonsequential fashion (e.g., backward). Depending upon the ! complexity of the query's execution plan, specifying ! SCROLL may impose a slight performance penalty ! on the query's execution time. ! ! ! ! ! ! WITHOUT HOLD ! ! ! Specifies that the cursor cannot be used outside of the ! transaction that created it. If neither WITHOUT ! HOLD nor WITH HOLD is specified, ! WITH HOLD is the default. ! ! ! ! ! ! WITH HOLD ! ! ! Specifies that the cursor may be used after the transaction ! that creates it successfully commits. *************** *** 124,130 **** ! The BINARY, INSENSITIVE, and SCROLL keywords may appear in any order. --- 162,169 ---- ! The BINARY, INSENSITIVE, ! SCROLL keywords may appear in any order. *************** *** 144,150 **** ! The message returned if the SELECT is run successfully. --- 183,189 ---- ! The message returned if the SELECT is run successfully. *************** *** 155,163 **** ! This message is reported if the same cursor name was already declared ! in the current transaction block. The previous definition is ! discarded. --- 194,201 ---- ! This message is reported if a cursor with the same name already ! exists. The previous definition is discarded. *************** *** 168,174 **** ! This error occurs if the cursor is not declared within a transaction block. --- 206,214 ---- ! This error occurs if the cursor is not declared within a ! transaction block, and WITH HOLD is not ! specified. *************** *** 193,208 **** ! Normal cursors return data in text format, the same as a SELECT ! would produce. Since ! data is stored natively in binary format, the system must ! do a conversion to produce the text format. In addition, ! text formats are often larger in size than the corresponding binary format. ! Once the information comes back in text form, the client ! application may need to convert it to a binary format to ! manipulate it. ! BINARY cursors give you back the data in the native binary ! representation. --- 233,246 ---- ! Normal cursors return data in text format, the same as a ! SELECT would produce. Since data is stored natively in ! binary format, the system must do a conversion to produce the text ! format. In addition, text formats are often larger in size than the ! corresponding binary format. Once the information comes back in ! text form, the client application may need to convert it to a ! binary format to manipulate it. BINARY cursors give you back the ! data in the native binary representation. *************** *** 245,251 **** ! Cursors are only available within transactions. Use , and --- 283,291 ---- ! If WITH HOLD is not specified, the cursor ! created by this command can only be used within the current ! transaction. Use , and *************** *** 254,265 **** ! The SCROLL option should be specified when defining a cursor ! that will be used to fetch backwards. This is required by ! SQL92. However, for compatibility with ! earlier versions, PostgreSQL will allow ! backward fetches without SCROLL, if the cursor's query plan ! is simple enough that no extra overhead is needed to support it. --- 294,318 ---- ! If WITH HOLD is specified and the transaction ! that created the cursor successfully commits, the cursor can be ! accessed outside the creating transaction. If the creating ! transaction is aborted, the cursor is removed. A cursor created ! with WITH HOLD is closed when an explicit ! CLOSE command is issued on it, or the client ! connection is terminated. ! ! ! ! The SCROLL option should be specified when defining a ! cursor that will be used to fetch backwards. This is required by ! SQL92. However, for compatibility with earlier ! versions, PostgreSQL will allow ! backward fetches without SCROLL, if the cursor's query ! plan is simple enough that no extra overhead is needed to support ! it. However, application developers are advised not to rely on ! using backward fetches from a cursor that has not been created ! with SCROLL. *************** *** 271,277 **** However, ecpg, the embedded SQL preprocessor for PostgreSQL, supports the SQL92 cursor conventions, including those ! involving DECLARE and OPEN statements. --- 324,330 ---- However, ecpg, the embedded SQL preprocessor for PostgreSQL, supports the SQL92 cursor conventions, including those ! involving DECLARE and OPEN statements. *************** *** 303,315 **** SQL92 ! SQL92 allows cursors only in embedded SQL ! and in modules. PostgreSQL permits cursors to be used ! interactively. SQL92 allows embedded or modular cursors to ! update database information. ! All PostgreSQL cursors are read only. ! The BINARY keyword is a PostgreSQL extension. --- 356,376 ---- SQL92 ! ! SQL92 allows cursors only in embedded ! SQL and in modules. PostgreSQL ! permits cursors to be used interactively. ! ! ! SQL92 allows embedded or modular cursors to ! update database information. All PostgreSQL ! cursors are read only. ! ! ! ! The BINARY keyword is a ! PostgreSQL extension. Index: doc/src/sgml/ref/fetch.sgml =================================================================== RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/ref/fetch.sgml,v retrieving revision 1.27 diff -c -r1.27 fetch.sgml *** doc/src/sgml/ref/fetch.sgml 11 Mar 2003 19:40:22 -0000 1.27 --- doc/src/sgml/ref/fetch.sgml 23 Mar 2003 20:57:51 -0000 *************** *** 251,258 **** ! If cursor is not known. ! The cursor must have been declared within the current transaction block. --- 251,257 ---- ! There is no cursor with the specified name. *************** *** 326,332 **** use any variants of FETCH other than FETCH NEXT or FETCH FORWARD with a positive count. For simple queries PostgreSQL will allow backwards fetch from ! cursors not declared with SCROLL, but this behavior is best not relied on. --- 325,333 ---- use any variants of FETCH other than FETCH NEXT or FETCH FORWARD with a positive count. For simple queries PostgreSQL will allow backwards fetch from ! cursors not declared with SCROLL, but this behavior is best not ! relied on. If the cursor is declared with NO SCROLL, no backward ! fetches are allowed. *************** *** 339,354 **** ! Updating data via a cursor is not supported by ! PostgreSQL, ! because mapping cursor updates back to base tables is ! not generally possible, as is also the case with VIEW updates. ! Consequently, ! users must issue explicit UPDATE commands to replace data. ! ! ! ! Cursors may only be used inside transaction blocks. --- 340,350 ---- ! Updating data via a cursor is not supported by ! PostgreSQL, because mapping cursor ! updates back to base tables is not generally possible, as is also ! the case with view updates. Consequently, users must issue ! explicit UPDATE commands to replace data. *************** *** 357,368 **** Use to change cursor position without retrieving data. - Refer to - , - , - and - - for further information about transactions. --- 353,358 ---- *************** *** 379,385 **** -- Set up and use a cursor: BEGIN WORK; ! DECLARE liahona CURSOR FOR SELECT * FROM films; -- Fetch first 5 rows in the cursor liahona: FETCH FORWARD 5 IN liahona; --- 369,375 ---- -- Set up and use a cursor: BEGIN WORK; ! DECLARE liahona SCROLL CURSOR FOR SELECT * FROM films; -- Fetch first 5 rows in the cursor liahona: FETCH FORWARD 5 IN liahona; *************** *** 425,433 **** ! SQL92 defines FETCH for use in embedded contexts only. ! Therefore, it describes placing the results into explicit variables using ! an INTO clause, for example: FETCH ABSOLUTE n --- 415,424 ---- ! SQL92 defines FETCH for use ! in embedded contexts only. Therefore, it describes placing the ! results into explicit variables using an INTO clause, ! for example: FETCH ABSOLUTE n *************** *** 435,450 **** INTO :variable [, ...] ! PostgreSQL's use of non-embedded cursors ! is non-standard, and so is its practice of returning the result data ! as if it were a SELECT result. Other than this point, FETCH is fully upward-compatible with SQL92. ! The FETCH forms involving FORWARD and BACKWARD (including the forms ! FETCH count and FETCH ALL, ! in which FORWARD is implicit) are PostgreSQL extensions. --- 426,443 ---- INTO :variable [, ...] ! PostgreSQL's use of non-embedded ! cursors is non-standard, and so is its practice of returning the ! result data as if it were a SELECT result. ! Other than this point, FETCH is fully upward-compatible with SQL92. ! The FETCH forms involving FORWARD and BACKWARD ! (including the forms FETCH count and FETCH ALL, in which ! FORWARD is implicit) are PostgreSQL extensions. Index: doc/src/sgml/ref/move.sgml =================================================================== RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/ref/move.sgml,v retrieving revision 1.20 diff -c -r1.20 move.sgml *** doc/src/sgml/ref/move.sgml 11 Mar 2003 19:40:22 -0000 1.20 --- doc/src/sgml/ref/move.sgml 23 Mar 2003 20:58:12 -0000 *************** *** 64,75 **** Refer to to define a cursor. - Refer to - , - , - and - - for further information about transactions. --- 64,69 ---- *************** *** 83,89 **** BEGIN WORK; ! DECLARE liahona CURSOR FOR SELECT * FROM films; -- Skip first 5 rows: MOVE FORWARD 5 IN liahona; --- 77,83 ---- BEGIN WORK; ! DECLARE liahona CURSOR FOR SELECT * FROM films; -- Skip first 5 rows: MOVE FORWARD 5 IN liahona; Index: src/backend/access/transam/xact.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/access/transam/xact.c,v retrieving revision 1.144 diff -c -r1.144 xact.c *** src/backend/access/transam/xact.c 21 Mar 2003 04:33:15 -0000 1.144 --- src/backend/access/transam/xact.c 21 Mar 2003 16:40:40 -0000 *************** *** 926,932 **** * access, and in fact could still cause an error...) */ ! AtEOXact_portals(); /* handle commit for large objects [ PA, 7/17/98 ] */ /* XXX probably this does not belong here */ --- 926,932 ---- * access, and in fact could still cause an error...) */ ! AtEOXact_portals(true); /* handle commit for large objects [ PA, 7/17/98 ] */ /* XXX probably this does not belong here */ *************** *** 1057,1063 **** * do abort processing */ DeferredTriggerAbortXact(); ! AtEOXact_portals(); lo_commit(false); /* 'false' means it's abort */ AtAbort_Notify(); AtEOXact_UpdatePasswordFile(false); --- 1057,1063 ---- * do abort processing */ DeferredTriggerAbortXact(); ! AtEOXact_portals(false); lo_commit(false); /* 'false' means it's abort */ AtAbort_Notify(); AtEOXact_UpdatePasswordFile(false); Index: src/backend/commands/copy.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/commands/copy.c,v retrieving revision 1.189 diff -c -r1.189 copy.c *** src/backend/commands/copy.c 3 Feb 2003 21:15:43 -0000 1.189 --- src/backend/commands/copy.c 14 Mar 2003 22:00:03 -0000 *************** *** 1,8 **** /*------------------------------------------------------------------------- * * copy.c ! * COPY command. ! * * * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California --- 1,7 ---- /*------------------------------------------------------------------------- * * copy.c ! * Implements the COPY utility command. * * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California *************** *** 50,55 **** --- 49,57 ---- #define ISOCTAL(c) (((c) >= '0') && ((c) <= '7')) #define OCTVALUE(c) ((c) - '0') + /* + * Represents the type of data returned by CopyReadAttribute() + */ typedef enum CopyReadResult { NORMAL_ATTR, *************** *** 1311,1317 **** * *result is set to indicate what terminated the read: * NORMAL_ATTR: column delimiter * END_OF_LINE: newline ! * END_OF_FILE: EOF indication * In all cases, the string read up to the terminator is returned. * * Note: This function does not care about SQL NULL values -- it --- 1313,1319 ---- * *result is set to indicate what terminated the read: * NORMAL_ATTR: column delimiter * END_OF_LINE: newline ! * END_OF_FILE: EOF indicator * In all cases, the string read up to the terminator is returned. * * Note: This function does not care about SQL NULL values -- it Index: src/backend/commands/portalcmds.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/commands/portalcmds.c,v retrieving revision 1.10 diff -c -r1.10 portalcmds.c *** src/backend/commands/portalcmds.c 11 Mar 2003 19:40:22 -0000 1.10 --- src/backend/commands/portalcmds.c 24 Mar 2003 02:00:29 -0000 *************** *** 17,34 **** #include #include "commands/portalcmds.h" #include "executor/executor.h" #include "optimizer/planner.h" #include "rewrite/rewriteHandler.h" ! static long DoRelativeFetch(Portal portal, bool forward, long count, CommandDest dest); static void DoPortalRewind(Portal portal); ! static Portal PreparePortal(char *portalName); /* --- 17,39 ---- #include + #include "miscadmin.h" #include "commands/portalcmds.h" #include "executor/executor.h" #include "optimizer/planner.h" #include "rewrite/rewriteHandler.h" ! #include "utils/memutils.h" static long DoRelativeFetch(Portal portal, bool forward, long count, CommandDest dest); + static long DoRelativeStoreFetch(Portal portal, + bool forward, + long count, + CommandDest dest); static void DoPortalRewind(Portal portal); ! static Portal PreparePortal(DeclareCursorStmt *stmt); /* *************** *** 46,53 **** char *cursorName; QueryDesc *queryDesc; ! /* Check for invalid context (must be in transaction block) */ ! RequireTransactionChain((void *) stmt, "DECLARE CURSOR"); /* * The query has been through parse analysis, but not rewriting or --- 51,65 ---- char *cursorName; QueryDesc *queryDesc; ! /* ! * If this is a non-holdable cursor, we ensure that this statement ! * has been executed inside a transaction block (or else, it would ! * have no user-visible effect). ! * ! * XXX: surely there is a better way to check this? ! */ ! if (!(stmt->options & CURSOR_OPT_HOLD)) ! RequireTransactionChain((void *) stmt, "DECLARE CURSOR"); /* * The query has been through parse analysis, but not rewriting or *************** *** 76,82 **** /* * Create a portal and copy the query and plan into its memory context. */ ! portal = PreparePortal(stmt->portalname); oldContext = MemoryContextSwitchTo(PortalGetHeapMemory(portal)); query = copyObject(query); --- 88,94 ---- /* * Create a portal and copy the query and plan into its memory context. */ ! portal = PreparePortal(stmt); oldContext = MemoryContextSwitchTo(PortalGetHeapMemory(portal)); query = copyObject(query); *************** *** 130,135 **** --- 142,148 ---- portal = GetPortalByName(stmt->portalname); if (!PortalIsValid(portal)) { + /* FIXME: shouldn't this be an ERROR? */ elog(WARNING, "PerformPortalFetch: portal \"%s\" not found", stmt->portalname); return; *************** *** 343,348 **** --- 356,364 ---- ScanDirection direction; QueryDesc temp_queryDesc; + if (portal->holdStore) + return DoRelativeStoreFetch(portal, forward, count, dest); + queryDesc = PortalGetQueryDesc(portal); estate = queryDesc->estate; *************** *** 407,413 **** } else { ! if (!portal->backwardOK) elog(ERROR, "Cursor can only scan forward" "\n\tDeclare it with SCROLL option to enable backward scan"); --- 423,429 ---- } else { ! if (portal->scrollType == DISABLE_SCROLL) elog(ERROR, "Cursor can only scan forward" "\n\tDeclare it with SCROLL option to enable backward scan"); *************** *** 453,468 **** } /* * DoPortalRewind - rewind a Portal to starting point */ static void DoPortalRewind(Portal portal) { ! QueryDesc *queryDesc; ! ! queryDesc = PortalGetQueryDesc(portal); ! ! ExecutorRewind(queryDesc); portal->atStart = true; portal->atEnd = false; --- 469,552 ---- } /* + * DoRelativeStoreFetch + * Do fetch for a simple N-rows-forward-or-backward case, getting + * the results from the portal's tuple store. + */ + static long + DoRelativeStoreFetch(Portal portal, + bool forward, + long count, + CommandDest dest) + { + DestReceiver *destfunc; + QueryDesc *queryDesc = portal->queryDesc; + long rows_fetched = 0; + + if (!forward && portal->scrollType == DISABLE_SCROLL) + elog(ERROR, "Cursor can only scan forward" + "\n\tDeclare it with SCROLL option to enable backward scan"); + + destfunc = DestToFunction(dest); + (*destfunc->setup) (destfunc, queryDesc->operation, + portal->name, queryDesc->tupDesc); + + for (;;) + { + HeapTuple tup; + bool should_free; + + if (rows_fetched >= count) + break; + if (portal->atEnd && forward) + break; + if (portal->atStart && !forward) + break; + + tup = tuplestore_getheaptuple(portal->holdStore, forward, &should_free); + + if (tup == NULL) + { + if (forward) + portal->atEnd = true; + else + portal->atStart = true; + + break; + } + + (*destfunc->receiveTuple) (tup, queryDesc->tupDesc, destfunc); + + rows_fetched++; + if (forward) + portal->portalPos++; + else + portal->portalPos--; + + if (forward && portal->atStart) + portal->atStart = false; + if (!forward && portal->atEnd) + portal->atEnd = false; + + if (should_free) + pfree(tup); + } + + (*destfunc->cleanup) (destfunc); + + return rows_fetched; + } + + /* * DoPortalRewind - rewind a Portal to starting point */ static void DoPortalRewind(Portal portal) { ! if (portal->holdStore) ! tuplestore_rescan(portal->holdStore); ! else ! ExecutorRewind(PortalGetQueryDesc(portal)); portal->atStart = true; portal->atEnd = false; *************** *** 493,514 **** /* * Note: PortalCleanup is called as a side-effect */ ! PortalDrop(portal); } - /* * PreparePortal */ static Portal ! PreparePortal(char *portalName) { Portal portal; /* * Check for already-in-use portal name. */ ! portal = GetPortalByName(portalName); if (PortalIsValid(portal)) { /* --- 577,601 ---- /* * Note: PortalCleanup is called as a side-effect */ ! PortalDrop(portal, false); } /* * PreparePortal + * Given a DECLARE CURSOR statement, returns the Portal data + * structure based on that statement that is used to manage the + * Portal internally. If a portal with specified name already + * exists, it is replaced. */ static Portal ! PreparePortal(DeclareCursorStmt *stmt) { Portal portal; /* * Check for already-in-use portal name. */ ! portal = GetPortalByName(stmt->portalname); if (PortalIsValid(portal)) { /* *************** *** 516,534 **** * portal? */ elog(WARNING, "Closing pre-existing portal \"%s\"", ! portalName); ! PortalDrop(portal); } /* * Create the new portal. */ ! portal = CreatePortal(portalName); return portal; } - /* * PortalCleanup * --- 603,632 ---- * portal? */ elog(WARNING, "Closing pre-existing portal \"%s\"", ! stmt->portalname); ! PortalDrop(portal, false); } /* * Create the new portal. */ ! portal = CreatePortal(stmt->portalname); ! ! /* ! * Modify the newly created portal based on the options specified in ! * the DECLARE CURSOR statement. ! */ ! if (stmt->options & CURSOR_OPT_SCROLL) ! portal->scrollType = ENABLE_SCROLL; ! else if (stmt->options & CURSOR_OPT_NO_SCROLL) ! portal->scrollType = DISABLE_SCROLL; ! ! if (stmt->options & CURSOR_OPT_HOLD) ! portal->holdOpen = true; return portal; } /* * PortalCleanup * *************** *** 545,558 **** AssertArg(PortalIsValid(portal)); AssertArg(portal->cleanup == PortalCleanup); /* ! * tell the executor to shutdown the query */ ! ExecutorEnd(PortalGetQueryDesc(portal)); /* ! * This should be unnecessary since the querydesc should be in the ! * portal's memory context, but do it anyway for symmetry. */ ! FreeQueryDesc(PortalGetQueryDesc(portal)); } --- 643,770 ---- AssertArg(PortalIsValid(portal)); AssertArg(portal->cleanup == PortalCleanup); + if (portal->holdStore) + tuplestore_end(portal->holdStore); + else + ExecutorEnd(PortalGetQueryDesc(portal)); + + } + + /* + * PersistHoldablePortal + * + * Prepare the specified Portal for access outside of the current + * transaction. When this function returns, all future accesses to the + * portal must be done via the Tuplestore (not by invoking the + * executor). + */ + void + PersistHoldablePortal(Portal portal) + { + MemoryContext oldcxt; + QueryDesc *queryDesc = PortalGetQueryDesc(portal); + + /* + * If we're preserving a holdable portal, we had better be + * inside the transaction that originally created it. + */ + Assert(portal->createXact == GetCurrentTransactionId()); + Assert(portal->holdStore == NULL); + + /* + * This context is used to store portal data that needs to persist + * between transactions. + */ + oldcxt = MemoryContextSwitchTo(portal->holdContext); + + /* XXX: Should SortMem be used for this? */ + portal->holdStore = tuplestore_begin_heap(true, true, SortMem); + + /* Set the destination to output to the tuplestore */ + queryDesc->dest = Tuplestore; + + /* + * Rewind the executor: we need to store the entire result set in + * the tuplestore, so that subsequent backward FETCHs can be + * processed. + */ + ExecutorRewind(queryDesc); + + /* Fetch the result set into the tuplestore */ + ExecutorRun(queryDesc, ForwardScanDirection, 0); + + /* + * Reset the position in the result set: ideally, this could be + * implemented by just skipping straight to the tuple # that we need + * to be at, but the tuplestore API doesn't support that. So we + * start at the beginning of the tuplestore and iterate through it + * until we reach where we need to be. + */ + if (!portal->atEnd) + { + int store_pos = 0; + bool should_free; + + tuplestore_rescan(portal->holdStore); + + while (store_pos < portal->portalPos) + { + HeapTuple tmp = tuplestore_gettuple(portal->holdStore, + true, &should_free); + + if (tmp == NULL) + elog(ERROR, + "PersistHoldablePortal: unexpected end of tuple stream"); + + store_pos++; + + /* + * This could probably be optimized by creating and then + * deleting a separate memory context for this series of + * operations. + */ + if (should_free) + pfree(tmp); + } + } + /* ! * The current Portal structure contains some data that will be ! * needed by the holdable cursor, but it has been allocated in a ! * memory context that is not sufficiently long-lived: we need to ! * copy it into the portal's long-term memory context. */ ! { ! TupleDesc tupDescCopy; ! QueryDesc *queryDescCopy; ! ! /* ! * We need to use this order as ExecutorEnd invalidates the ! * queryDesc's tuple descriptor ! */ ! tupDescCopy = CreateTupleDescCopy(queryDesc->tupDesc); ! ! ExecutorEnd(queryDesc); ! ! queryDescCopy = palloc(sizeof(*queryDescCopy)); ! ! /* ! * This doesn't copy all the dependant data in the QueryDesc, ! * but that's okay -- the only complex field we need to keep is ! * the query's tupledesc, which we've copied ourselves. ! */ ! memcpy(queryDescCopy, queryDesc, sizeof(*queryDesc)); ! ! FreeQueryDesc(queryDesc); ! ! queryDescCopy->tupDesc = tupDescCopy; ! portal->queryDesc = queryDescCopy; ! } /* ! * We no longer need the portal's short-term memory context. */ ! MemoryContextDelete(PortalGetHeapMemory(portal)); ! ! PortalGetHeapMemory(portal) = NULL; } Index: src/backend/executor/Makefile =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/executor/Makefile,v retrieving revision 1.20 diff -c -r1.20 Makefile *** src/backend/executor/Makefile 10 Jan 2003 23:54:24 -0000 1.20 --- src/backend/executor/Makefile 21 Mar 2003 17:10:33 -0000 *************** *** 18,24 **** nodeHashjoin.o nodeIndexscan.o nodeMaterial.o nodeMergejoin.o \ nodeNestloop.o nodeFunctionscan.o nodeResult.o nodeSeqscan.o \ nodeSetOp.o nodeSort.o nodeUnique.o nodeLimit.o nodeGroup.o \ ! nodeSubplan.o nodeSubqueryscan.o nodeTidscan.o spi.o all: SUBSYS.o --- 18,24 ---- nodeHashjoin.o nodeIndexscan.o nodeMaterial.o nodeMergejoin.o \ nodeNestloop.o nodeFunctionscan.o nodeResult.o nodeSeqscan.o \ nodeSetOp.o nodeSort.o nodeUnique.o nodeLimit.o nodeGroup.o \ ! nodeSubplan.o nodeSubqueryscan.o nodeTidscan.o tstoreReceiver.o spi.o all: SUBSYS.o Index: src/backend/executor/execMain.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/executor/execMain.c,v retrieving revision 1.203 diff -c -r1.203 execMain.c *** src/backend/executor/execMain.c 20 Mar 2003 03:34:55 -0000 1.203 --- src/backend/executor/execMain.c 21 Mar 2003 16:40:40 -0000 *************** *** 217,224 **** estate->es_lastoid = InvalidOid; destfunc = DestToFunction(dest); ! (*destfunc->setup) (destfunc, (int) operation, ! queryDesc->portalName, queryDesc->tupDesc); /* * run plan --- 217,224 ---- estate->es_lastoid = InvalidOid; destfunc = DestToFunction(dest); ! (*destfunc->setup) (destfunc, operation, queryDesc->portalName, ! queryDesc->tupDesc); /* * run plan *************** *** 420,434 **** aclcheck_error(aclcheck_result, get_rel_name(relOid)); } } - - - /* =============================================================== - * =============================================================== - static routines follow - * =============================================================== - * =============================================================== - */ - static void ExecCheckXactReadOnly(Query *parsetree, CmdType operation) --- 420,425 ---- Index: src/backend/executor/execQual.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/executor/execQual.c,v retrieving revision 1.126 diff -c -r1.126 execQual.c *** src/backend/executor/execQual.c 9 Mar 2003 02:19:13 -0000 1.126 --- src/backend/executor/execQual.c 24 Mar 2003 02:01:01 -0000 *************** *** 1054,1061 **** 0, false); } ! tupstore = tuplestore_begin_heap(true, /* randomAccess */ ! SortMem); MemoryContextSwitchTo(oldcontext); rsinfo.setResult = tupstore; rsinfo.setDesc = tupdesc; --- 1054,1060 ---- 0, false); } ! tupstore = tuplestore_begin_heap(true, false, SortMem); MemoryContextSwitchTo(oldcontext); rsinfo.setResult = tupstore; rsinfo.setDesc = tupdesc; Index: src/backend/executor/nodeHash.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/executor/nodeHash.c,v retrieving revision 1.74 diff -c -r1.74 nodeHash.c *** src/backend/executor/nodeHash.c 10 Jan 2003 23:54:24 -0000 1.74 --- src/backend/executor/nodeHash.c 24 Mar 2003 01:19:31 -0000 *************** *** 64,70 **** * buffers are palloc'd in regular executor context. */ for (i = 0; i < nbatch; i++) ! hashtable->innerBatchFile[i] = BufFileCreateTemp(); } /* --- 64,70 ---- * buffers are palloc'd in regular executor context. */ for (i = 0; i < nbatch; i++) ! hashtable->innerBatchFile[i] = BufFileCreateTemp(false); } /* Index: src/backend/executor/nodeHashjoin.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/executor/nodeHashjoin.c,v retrieving revision 1.48 diff -c -r1.48 nodeHashjoin.c *** src/backend/executor/nodeHashjoin.c 27 Jan 2003 20:51:48 -0000 1.48 --- src/backend/executor/nodeHashjoin.c 24 Mar 2003 01:19:43 -0000 *************** *** 138,144 **** * buffers are palloc'd in regular executor context. */ for (i = 0; i < hashtable->nbatch; i++) ! hashtable->outerBatchFile[i] = BufFileCreateTemp(); } else if (hashtable == NULL) return NULL; --- 138,144 ---- * buffers are palloc'd in regular executor context. */ for (i = 0; i < hashtable->nbatch; i++) ! hashtable->outerBatchFile[i] = BufFileCreateTemp(false); } else if (hashtable == NULL) return NULL; Index: src/backend/executor/nodeMaterial.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/executor/nodeMaterial.c,v retrieving revision 1.41 diff -c -r1.41 nodeMaterial.c *** src/backend/executor/nodeMaterial.c 9 Mar 2003 02:19:13 -0000 1.41 --- src/backend/executor/nodeMaterial.c 24 Mar 2003 02:01:37 -0000 *************** *** 62,69 **** */ if (tuplestorestate == NULL) { ! tuplestorestate = tuplestore_begin_heap(true, /* randomAccess */ ! SortMem); node->tuplestorestate = (void *) tuplestorestate; } --- 62,68 ---- */ if (tuplestorestate == NULL) { ! tuplestorestate = tuplestore_begin_heap(true, false, SortMem); node->tuplestorestate = (void *) tuplestorestate; } Index: src/backend/executor/spi.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/executor/spi.c,v retrieving revision 1.88 diff -c -r1.88 spi.c *** src/backend/executor/spi.c 11 Mar 2003 19:40:22 -0000 1.88 --- src/backend/executor/spi.c 17 Mar 2003 23:49:00 -0000 *************** *** 866,872 **** if (!PortalIsValid(portal)) elog(ERROR, "invalid portal in SPI cursor operation"); ! PortalDrop(portal); } /* =================== private functions =================== */ --- 866,872 ---- if (!PortalIsValid(portal)) elog(ERROR, "invalid portal in SPI cursor operation"); ! PortalDrop(portal, false); } /* =================== private functions =================== */ Index: src/backend/executor/tstoreReceiver.c =================================================================== RCS file: src/backend/executor/tstoreReceiver.c diff -N src/backend/executor/tstoreReceiver.c *** /dev/null 1 Jan 1970 00:00:00 -0000 --- src/backend/executor/tstoreReceiver.c 23 Mar 2003 07:33:54 -0000 *************** *** 0 **** --- 1,89 ---- + /*------------------------------------------------------------------------- + * + * tstore_receiver.c + * an implementation of DestReceiver that stores the result tuples in + * a Tuplestore + * + * + * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * IDENTIFICATION + * $Header$ + * + *------------------------------------------------------------------------- + */ + + #include "postgres.h" + + #include "executor/tstoreReceiver.h" + #include "utils/memutils.h" + #include "utils/portal.h" + + typedef struct + { + DestReceiver pub; + Tuplestorestate *tstore; + MemoryContext cxt; + } TStoreState; + + /* + * Receive a tuple from the executor and store it in the tuplestore. + * + * XXX: As currently implemented, this routine is a hack: there should + * be no tie between this code and the portal system. Instead, the + * receiver function that is part of DestFunction should be passed a + * QueryDesc, so that the call site of ExecutorRun can "sub-class" + * QueryDesc and pass in any necessary addition information (in this + * case, the Tuplestore to use). + */ + static void + tstoreSetupReceiver(DestReceiver *self, int operation, + const char *portalname, TupleDesc typeinfo) + { + TStoreState *myState = (TStoreState *) self; + Portal portal; + + if (operation != CMD_SELECT) + elog(ERROR, "Unexpected operation type: %d", operation); + + portal = GetPortalByName(portalname); + + if (portal == NULL) + elog(ERROR, "Specified portal does not exist: %s", portalname); + + myState->tstore = portal->holdStore; + myState->cxt = portal->holdContext; + } + + static void + tstoreReceiveTuple(HeapTuple tuple, TupleDesc typeinfo, DestReceiver *self) + { + TStoreState *myState = (TStoreState *) self; + MemoryContext oldcxt = MemoryContextSwitchTo(myState->cxt); + + tuplestore_puttuple(myState->tstore, tuple); + + MemoryContextSwitchTo(oldcxt); + } + + static void + tstoreCleanupReceiver(DestReceiver *self) + { + ; /* do nothing */ + } + + DestReceiver * + tstoreReceiverCreateDR(void) + { + TStoreState *self = (TStoreState *) palloc(sizeof(TStoreState)); + + self->pub.receiveTuple = tstoreReceiveTuple; + self->pub.setup = tstoreSetupReceiver; + self->pub.cleanup = tstoreCleanupReceiver; + + self->tstore = NULL; + self->cxt = NULL; + + return (DestReceiver *) self; + } Index: src/backend/parser/analyze.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/parser/analyze.c,v retrieving revision 1.265 diff -c -r1.265 analyze.c *** src/backend/parser/analyze.c 10 Mar 2003 03:53:50 -0000 1.265 --- src/backend/parser/analyze.c 24 Mar 2003 04:02:58 -0000 *************** *** 2294,2299 **** --- 2294,2306 ---- result->commandType = CMD_UTILITY; result->utilityStmt = (Node *) stmt; + /* + * Don't allow both SCROLL and NO SCROLL to be specified + */ + if ((stmt->options & CURSOR_OPT_SCROLL) && + (stmt->options & CURSOR_OPT_NO_SCROLL)) + elog(ERROR, "Both SCROLL and NO SCROLL cannot be specified."); + stmt->query = (Node *) transformStmt(pstate, stmt->query, &extras_before, &extras_after); Index: src/backend/parser/gram.y =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/parser/gram.y,v retrieving revision 2.408 diff -c -r2.408 gram.y *** src/backend/parser/gram.y 20 Mar 2003 18:52:47 -0000 2.408 --- src/backend/parser/gram.y 23 Mar 2003 19:50:51 -0000 *************** *** 246,252 **** %type opt_freeze opt_default opt_recheck %type opt_binary opt_oids copy_delimiter ! %type copy_from %type reindex_type drop_type fetch_count opt_column event comment_type cursor_options --- 246,252 ---- %type opt_freeze opt_default opt_recheck %type opt_binary opt_oids copy_delimiter ! %type copy_from opt_hold %type reindex_type drop_type fetch_count opt_column event comment_type cursor_options *************** *** 348,354 **** GLOBAL GRANT GROUP_P ! HANDLER HAVING HOUR_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P INCREMENT INDEX INHERITS INITIALLY INNER_P INOUT INPUT --- 348,354 ---- GLOBAL GRANT GROUP_P ! HANDLER HAVING HOLD HOUR_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P INCREMENT INDEX INHERITS INITIALLY INNER_P INOUT INPUT *************** *** 4230,4250 **** * CURSOR STATEMENTS * *****************************************************************************/ ! DeclareCursorStmt: DECLARE name cursor_options CURSOR FOR SelectStmt { DeclareCursorStmt *n = makeNode(DeclareCursorStmt); n->portalname = $2; n->options = $3; ! n->query = $6; $$ = (Node *)n; } ; cursor_options: /*EMPTY*/ { $$ = 0; } ! | cursor_options BINARY { $$ = $1 | CURSOR_OPT_BINARY; } | cursor_options SCROLL { $$ = $1 | CURSOR_OPT_SCROLL; } | cursor_options INSENSITIVE { $$ = $1 | CURSOR_OPT_INSENSITIVE; } ; /***************************************************************************** * --- 4230,4259 ---- * CURSOR STATEMENTS * *****************************************************************************/ ! DeclareCursorStmt: DECLARE name cursor_options CURSOR opt_hold FOR SelectStmt { DeclareCursorStmt *n = makeNode(DeclareCursorStmt); n->portalname = $2; n->options = $3; ! n->query = $7; ! ! if ($5) ! n->options |= CURSOR_OPT_HOLD; ! $$ = (Node *)n; } ; cursor_options: /*EMPTY*/ { $$ = 0; } ! | cursor_options NO SCROLL { $$ = $1 | CURSOR_OPT_NO_SCROLL; } | cursor_options SCROLL { $$ = $1 | CURSOR_OPT_SCROLL; } + | cursor_options BINARY { $$ = $1 | CURSOR_OPT_BINARY; } | cursor_options INSENSITIVE { $$ = $1 | CURSOR_OPT_INSENSITIVE; } ; + + opt_hold: /* EMPTY */ { $$ = FALSE; } + | WITH HOLD { $$ = TRUE; } + | WITHOUT HOLD { $$ = FALSE; } /***************************************************************************** * Index: src/backend/parser/keywords.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/parser/keywords.c,v retrieving revision 1.136 diff -c -r1.136 keywords.c *** src/backend/parser/keywords.c 20 Mar 2003 07:02:10 -0000 1.136 --- src/backend/parser/keywords.c 21 Mar 2003 16:40:40 -0000 *************** *** 143,148 **** --- 143,149 ---- {"group", GROUP_P}, {"handler", HANDLER}, {"having", HAVING}, + {"hold", HOLD}, {"hour", HOUR_P}, {"ilike", ILIKE}, {"immediate", IMMEDIATE}, Index: src/backend/storage/file/buffile.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/storage/file/buffile.c,v retrieving revision 1.14 diff -c -r1.14 buffile.c *** src/backend/storage/file/buffile.c 5 Sep 2002 00:43:07 -0000 1.14 --- src/backend/storage/file/buffile.c 24 Mar 2003 01:18:29 -0000 *************** *** 64,69 **** --- 64,70 ---- */ bool isTemp; /* can only add files if this is TRUE */ + bool isInterTxn; /* keep open over transactions? */ bool dirty; /* does buffer need to be written? */ /* *************** *** 118,124 **** File pfile; Assert(file->isTemp); ! pfile = OpenTemporaryFile(); Assert(pfile >= 0); file->files = (File *) repalloc(file->files, --- 119,125 ---- File pfile; Assert(file->isTemp); ! pfile = OpenTemporaryFile(file->isInterTxn); Assert(pfile >= 0); file->files = (File *) repalloc(file->files, *************** *** 136,151 **** * written to it). */ BufFile * ! BufFileCreateTemp(void) { BufFile *file; File pfile; ! pfile = OpenTemporaryFile(); Assert(pfile >= 0); file = makeBufFile(pfile); file->isTemp = true; return file; } --- 137,153 ---- * written to it). */ BufFile * ! BufFileCreateTemp(bool interTxn) { BufFile *file; File pfile; ! pfile = OpenTemporaryFile(interTxn); Assert(pfile >= 0); file = makeBufFile(pfile); file->isTemp = true; + file->isInterTxn = interTxn; return file; } Index: src/backend/storage/file/fd.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/storage/file/fd.c,v retrieving revision 1.95 diff -c -r1.95 fd.c *** src/backend/storage/file/fd.c 2 Sep 2002 06:11:42 -0000 1.95 --- src/backend/storage/file/fd.c 24 Mar 2003 01:15:57 -0000 *************** *** 112,125 **** #define FileUnknownPos (-1L) typedef struct vfd { signed short fd; /* current FD, or VFD_CLOSED if none */ unsigned short fdstate; /* bitflags for VFD's state */ - - /* these are the assigned bits in fdstate: */ - #define FD_TEMPORARY (1 << 0) /* should be unlinked when closed */ - File nextFree; /* link to next free VFD, if in freelist */ File lruMoreRecently; /* doubly linked recency-of-use list */ File lruLessRecently; --- 112,125 ---- #define FileUnknownPos (-1L) + /* these are the assigned bits in fdstate below: */ + #define FD_TEMPORARY (1 << 0) + #define FD_TXN_TEMPORARY (1 << 1) + typedef struct vfd { signed short fd; /* current FD, or VFD_CLOSED if none */ unsigned short fdstate; /* bitflags for VFD's state */ File nextFree; /* link to next free VFD, if in freelist */ File lruMoreRecently; /* doubly linked recency-of-use list */ File lruLessRecently; *************** *** 750,758 **** * This routine takes care of generating an appropriate tempfile name. * There's no need to pass in fileFlags or fileMode either, since only * one setting makes any sense for a temp file. */ File ! OpenTemporaryFile(void) { char tempfilepath[128]; File file; --- 750,764 ---- * This routine takes care of generating an appropriate tempfile name. * There's no need to pass in fileFlags or fileMode either, since only * one setting makes any sense for a temp file. + * + * keepOverTxn: if true, don't close the file at end-of-transaction. In + * most cases, you don't want temporary files to outlive the transaction + * that created them, so this should be false -- but if you need + * "somewhat" temporary storage, this might be useful. In either case, + * the file is removed when the File is explicitely closed. */ File ! OpenTemporaryFile(bool keepOverTxn) { char tempfilepath[128]; File file; *************** *** 795,803 **** elog(ERROR, "Failed to create temporary file %s", tempfilepath); } ! /* Mark it for deletion at close or EOXact */ VfdCache[file].fdstate |= FD_TEMPORARY; return file; } --- 801,813 ---- elog(ERROR, "Failed to create temporary file %s", tempfilepath); } ! /* Mark it for deletion at close */ VfdCache[file].fdstate |= FD_TEMPORARY; + /* Mark it for deletion at EOXact */ + if (!keepOverTxn) + VfdCache[file].fdstate |= FD_TXN_TEMPORARY; + return file; } *************** *** 1114,1119 **** --- 1124,1130 ---- for (i = 1; i < SizeVfdCache; i++) { if ((VfdCache[i].fdstate & FD_TEMPORARY) && + (VfdCache[i].fdstate & FD_TXN_TEMPORARY) && VfdCache[i].fileName != NULL) FileClose(i); } Index: src/backend/tcop/dest.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/tcop/dest.c,v retrieving revision 1.50 diff -c -r1.50 dest.c *** src/backend/tcop/dest.c 21 Jan 2003 22:06:12 -0000 1.50 --- src/backend/tcop/dest.c 21 Mar 2003 17:10:10 -0000 *************** *** 29,34 **** --- 29,35 ---- #include "postgres.h" #include "access/printtup.h" + #include "executor/tstoreReceiver.h" #include "libpq/libpq.h" #include "libpq/pqformat.h" *************** *** 60,68 **** --- 61,71 ---- static DestReceiver donothingDR = { donothingReceive, donothingSetup, donothingCleanup }; + static DestReceiver debugtupDR = { debugtup, debugSetup, donothingCleanup }; + static DestReceiver spi_printtupDR = { spi_printtup, spi_dest_setup, donothingCleanup }; *************** *** 98,103 **** --- 101,109 ---- case SPI: return &spi_printtupDR; + case Tuplestore: + return tstoreReceiverCreateDR(); + case None: return &donothingDR; } *************** *** 122,127 **** --- 128,134 ---- case None: case Debug: + case Tuplestore: case SPI: break; } *************** *** 183,188 **** --- 190,196 ---- break; case Debug: + case Tuplestore: case None: default: break; *************** *** 213,218 **** --- 221,227 ---- break; case Debug: + case Tuplestore: case None: default: break; Index: src/backend/utils/mmgr/mcxt.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/utils/mmgr/mcxt.c,v retrieving revision 1.38 diff -c -r1.38 mcxt.c *** src/backend/utils/mmgr/mcxt.c 16 Dec 2002 16:22:46 -0000 1.38 --- src/backend/utils/mmgr/mcxt.c 23 Mar 2003 05:53:07 -0000 *************** *** 36,42 **** MemoryContext CurrentMemoryContext = NULL; /* ! * Standard top-level contexts */ MemoryContext TopMemoryContext = NULL; MemoryContext ErrorContext = NULL; --- 36,43 ---- MemoryContext CurrentMemoryContext = NULL; /* ! * Standard top-level contexts. For a description of the purpose of each ! * of these contexts, refer to src/backend/utils/mmgr/README */ MemoryContext TopMemoryContext = NULL; MemoryContext ErrorContext = NULL; Index: src/backend/utils/mmgr/portalmem.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/utils/mmgr/portalmem.c,v retrieving revision 1.53 diff -c -r1.53 portalmem.c *** src/backend/utils/mmgr/portalmem.c 11 Mar 2003 19:40:23 -0000 1.53 --- src/backend/utils/mmgr/portalmem.c 23 Mar 2003 20:35:45 -0000 *************** *** 20,27 **** * "PortalData" structure, plans the query and then stores the query * in the portal without executing it. Later, when the backend * sees a ! * fetch 1 from FOO ! * the system looks up the portal named "FOO" in the portal table, * gets the planned query and then calls the executor with a count * of 1. The executor then runs the query and returns a single * tuple. The problem is that we have to hold onto the state of the --- 20,27 ---- * "PortalData" structure, plans the query and then stores the query * in the portal without executing it. Later, when the backend * sees a ! * fetch 1 from foo ! * the system looks up the portal named "foo" in the portal table, * gets the planned query and then calls the executor with a count * of 1. The executor then runs the query and returns a single * tuple. The problem is that we have to hold onto the state of the *************** *** 38,44 **** #include "utils/memutils.h" #include "utils/portal.h" - /* * estimate of the maximum number of open portals a user would have, * used in initially sizing the PortalHashTable in EnablePortalManager() --- 38,43 ---- *************** *** 131,138 **** ctl.entrysize = sizeof(PortalHashEnt); /* ! * use PORTALS_PER_USER, defined in utils/portal.h as a guess of how ! * many hash table entries to create, initially */ PortalHashTable = hash_create("Portal hash", PORTALS_PER_USER, &ctl, HASH_ELEM); --- 130,137 ---- ctl.entrysize = sizeof(PortalHashEnt); /* ! * use PORTALS_PER_USER as a guess of how many hash table entries to ! * create, initially */ PortalHashTable = hash_create("Portal hash", PORTALS_PER_USER, &ctl, HASH_ELEM); *************** *** 157,163 **** /* * PortalSetQuery ! * Attaches a "query" to portal. */ void PortalSetQuery(Portal portal, --- 156,164 ---- /* * PortalSetQuery ! * Attaches a "query" to the specified portal. Note that in the ! * case of DECLARE CURSOR, some Portal options have already been ! * set based upon the parsetree of the original DECLARE statement. */ void PortalSetQuery(Portal portal, *************** *** 166,174 **** { AssertArg(PortalIsValid(portal)); portal->queryDesc = queryDesc; portal->cleanup = cleanup; - portal->backwardOK = ExecSupportsBackwardScan(queryDesc->plantree); portal->atStart = true; portal->atEnd = false; /* allow fetches */ portal->portalPos = 0; --- 167,191 ---- { AssertArg(PortalIsValid(portal)); + /* + * If the user didn't specify a SCROLL type, allow or disallow + * scrolling based on whether it would require any additional + * runtime overhead to do so. + */ + if (portal->scrollType == DEFAULT_SCROLL) + { + bool backwardPlan; + + backwardPlan = ExecSupportsBackwardScan(queryDesc->plantree); + + if (backwardPlan) + portal->scrollType = ENABLE_SCROLL; + else + portal->scrollType = DISABLE_SCROLL; + } + portal->queryDesc = queryDesc; portal->cleanup = cleanup; portal->atStart = true; portal->atEnd = false; /* allow fetches */ portal->portalPos = 0; *************** *** 179,188 **** * CreatePortal * Returns a new portal given a name. * ! * Exceptions: ! * BadState if called when disabled. ! * BadArg if portal name is invalid. ! * "WARNING" if portal name is in use (existing portal is returned!) */ Portal CreatePortal(const char *name) --- 196,203 ---- * CreatePortal * Returns a new portal given a name. * ! * An elog(WARNING) is emitted if portal name is in use (existing ! * portal is returned!) */ Portal CreatePortal(const char *name) *************** *** 214,220 **** /* initialize portal query */ portal->queryDesc = NULL; portal->cleanup = NULL; ! portal->backwardOK = false; portal->atStart = true; portal->atEnd = true; /* disallow fetches until query is set */ portal->portalPos = 0; --- 229,239 ---- /* initialize portal query */ portal->queryDesc = NULL; portal->cleanup = NULL; ! portal->scrollType = DEFAULT_SCROLL; ! portal->holdOpen = false; ! portal->holdStore = NULL; ! portal->holdContext = NULL; ! portal->createXact = GetCurrentTransactionId(); portal->atStart = true; portal->atEnd = true; /* disallow fetches until query is set */ portal->portalPos = 0; *************** *** 228,244 **** /* * PortalDrop ! * Destroys portal. * ! * Exceptions: ! * BadState if called when disabled. ! * BadArg if portal is invalid. */ void ! PortalDrop(Portal portal) { AssertArg(PortalIsValid(portal)); /* remove portal from hash table */ PortalHashTableDelete(portal); --- 247,293 ---- /* * PortalDrop ! * Destroy the portal. * ! * keepHoldable: if true, holdable portals should not be removed by ! * this function. More specifically, invoking this function with ! * keepHoldable = true on a holdable portal prepares the portal for ! * access outside of its creating transaction. */ void ! PortalDrop(Portal portal, bool persistHoldable) { AssertArg(PortalIsValid(portal)); + if (portal->holdOpen && persistHoldable) + { + /* + * We're "dropping" a holdable portal, but what we really need + * to do is prepare the portal for access outside of its + * creating transaction. + */ + + /* + * Create the memory context that is used for storage of + * long-term (cross transaction) data needed by the holdable + * portal. + */ + portal->holdContext = + AllocSetContextCreate(PortalMemory, + "PortalHeapMemory", + ALLOCSET_DEFAULT_MINSIZE, + ALLOCSET_DEFAULT_INITSIZE, + ALLOCSET_DEFAULT_MAXSIZE); + + /* + * Note that PersistHoldablePortal() releases any resources used + * by the portal that are local to the creating txn. + */ + PersistHoldablePortal(portal); + + return; + } + /* remove portal from hash table */ PortalHashTableDelete(portal); *************** *** 246,253 **** if (PointerIsValid(portal->cleanup)) (*portal->cleanup) (portal); ! /* release subsidiary storage */ ! MemoryContextDelete(PortalGetHeapMemory(portal)); /* release name and portal data (both are in PortalMemory) */ pfree(portal->name); --- 295,314 ---- if (PointerIsValid(portal->cleanup)) (*portal->cleanup) (portal); ! /* ! * delete short-term memory context; in the case of a holdable ! * portal, this has already been done ! */ ! if (PortalGetHeapMemory(portal)) ! MemoryContextDelete(PortalGetHeapMemory(portal)); ! ! /* ! * delete long-term memory context; in the case of a non-holdable ! * portal, this context has never been created, so we don't need to ! * do anything ! */ ! if (portal->holdContext) ! MemoryContextDelete(portal->holdContext); /* release name and portal data (both are in PortalMemory) */ pfree(portal->name); *************** *** 255,261 **** } /* ! * Destroy all portals created in the current transaction (ie, all of them). * * XXX This assumes that portals can be deleted in a random order, ie, * no portal has a reference to any other (at least not one that will be --- 316,327 ---- } /* ! * Cleanup the portals created in the current transaction. If the ! * transaction was aborted, all the portals created in this transaction ! * should be removed. If the transaction was successfully committed, any ! * holdable cursors created in this transaction need to be kept ! * open. Only cursors created in the current transaction should be ! * removed in this fashion. * * XXX This assumes that portals can be deleted in a random order, ie, * no portal has a reference to any other (at least not one that will be *************** *** 264,276 **** * references... */ void ! AtEOXact_portals(void) { HASH_SEQ_STATUS status; PortalHashEnt *hentry; hash_seq_init(&status, PortalHashTable); while ((hentry = (PortalHashEnt *) hash_seq_search(&status)) != NULL) ! PortalDrop(hentry->portal); } --- 330,346 ---- * references... */ void ! AtEOXact_portals(bool isCommit) { HASH_SEQ_STATUS status; PortalHashEnt *hentry; + TransactionId xact = GetCurrentTransactionId(); hash_seq_init(&status, PortalHashTable); while ((hentry = (PortalHashEnt *) hash_seq_search(&status)) != NULL) ! { ! if (hentry->portal->createXact == xact) ! PortalDrop(hentry->portal, isCommit); ! } } Index: src/backend/utils/sort/logtape.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/utils/sort/logtape.c,v retrieving revision 1.8 diff -c -r1.8 logtape.c *** src/backend/utils/sort/logtape.c 20 Jun 2002 20:29:40 -0000 1.8 --- src/backend/utils/sort/logtape.c 24 Mar 2003 01:19:57 -0000 *************** *** 470,476 **** Assert(ntapes > 0); lts = (LogicalTapeSet *) palloc(sizeof(LogicalTapeSet) + (ntapes - 1) *sizeof(LogicalTape *)); ! lts->pfile = BufFileCreateTemp(); lts->nFileBlocks = 0L; lts->freeBlocksLen = 32; /* reasonable initial guess */ lts->freeBlocks = (long *) palloc(lts->freeBlocksLen * sizeof(long)); --- 470,476 ---- Assert(ntapes > 0); lts = (LogicalTapeSet *) palloc(sizeof(LogicalTapeSet) + (ntapes - 1) *sizeof(LogicalTape *)); ! lts->pfile = BufFileCreateTemp(false); lts->nFileBlocks = 0L; lts->freeBlocksLen = 32; /* reasonable initial guess */ lts->freeBlocks = (long *) palloc(lts->freeBlocksLen * sizeof(long)); Index: src/backend/utils/sort/tuplestore.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/utils/sort/tuplestore.c,v retrieving revision 1.11 diff -c -r1.11 tuplestore.c *** src/backend/utils/sort/tuplestore.c 9 Mar 2003 02:19:13 -0000 1.11 --- src/backend/utils/sort/tuplestore.c 24 Mar 2003 02:33:39 -0000 *************** *** 65,70 **** --- 65,71 ---- { TupStoreStatus status; /* enumerated value as shown above */ bool randomAccess; /* did caller request random access? */ + bool interTxn; /* keep open through transactions? */ long availMem; /* remaining memory available, in bytes */ BufFile *myfile; /* underlying file, or NULL if none */ *************** *** 190,196 **** static Tuplestorestate *tuplestore_begin_common(bool randomAccess, ! int maxKBytes); static void dumptuples(Tuplestorestate *state); static unsigned int getlen(Tuplestorestate *state, bool eofOK); static void *copytup_heap(Tuplestorestate *state, void *tup); --- 191,198 ---- static Tuplestorestate *tuplestore_begin_common(bool randomAccess, ! bool interTxn, ! int maxKBytes); static void dumptuples(Tuplestorestate *state); static unsigned int getlen(Tuplestorestate *state, bool eofOK); static void *copytup_heap(Tuplestorestate *state, void *tup); *************** *** 205,211 **** */ static Tuplestorestate * ! tuplestore_begin_common(bool randomAccess, int maxKBytes) { Tuplestorestate *state; --- 207,213 ---- */ static Tuplestorestate * ! tuplestore_begin_common(bool randomAccess, bool interTxn, int maxKBytes) { Tuplestorestate *state; *************** *** 213,218 **** --- 215,221 ---- state->status = TSS_INMEM; state->randomAccess = randomAccess; + state->interTxn = interTxn; state->availMem = maxKBytes * 1024L; state->myfile = NULL; *************** *** 231,240 **** return state; } Tuplestorestate * ! tuplestore_begin_heap(bool randomAccess, int maxKBytes) { ! Tuplestorestate *state = tuplestore_begin_common(randomAccess, maxKBytes); state->copytup = copytup_heap; state->writetup = writetup_heap; --- 234,260 ---- return state; } + /* + * tuplestore_begin_heap + * + * Create a new tuplestore; other types of tuple stores (other than + * "heap" tuple stores, for heap tuples) are possible, but not presently + * implemented. + * + * randomAccess: if true, both forward and backward accesses to the + * tuple store are allowed. + * + * interTxn: if true, the files used by on-disk storage persist beyond + * the end of the current transaction. + * + * maxKBytes: how much data to store in memory (any data beyond this + * amount is paged to disk). + */ Tuplestorestate * ! tuplestore_begin_heap(bool randomAccess, bool interTxn, int maxKBytes) { ! Tuplestorestate *state = tuplestore_begin_common(randomAccess, ! interTxn, maxKBytes); state->copytup = copytup_heap; state->writetup = writetup_heap; *************** *** 321,327 **** /* * Nope; time to switch to tape-based operation. */ ! state->myfile = BufFileCreateTemp(); state->status = TSS_WRITEFILE; dumptuples(state); break; --- 341,347 ---- /* * Nope; time to switch to tape-based operation. */ ! state->myfile = BufFileCreateTemp(state->interTxn); state->status = TSS_WRITEFILE; dumptuples(state); break; Index: src/include/executor/tstoreReceiver.h =================================================================== RCS file: src/include/executor/tstoreReceiver.h diff -N src/include/executor/tstoreReceiver.h *** /dev/null 1 Jan 1970 00:00:00 -0000 --- src/include/executor/tstoreReceiver.h 21 Mar 2003 17:08:15 -0000 *************** *** 0 **** --- 1,22 ---- + /*------------------------------------------------------------------------- + * + * tstoreReceiver.h + * prototypes for tstoreReceiver.c + * + * + * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * $Id$ + * + *------------------------------------------------------------------------- + */ + + #ifndef TSTORE_RECEIVER_H + #define TSTORE_RECEIVER_H + + #include "tcop/dest.h" + + extern DestReceiver *tstoreReceiverCreateDR(void); + + #endif /* TSTORE_RECEIVER_H */ Index: src/include/nodes/parsenodes.h =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/include/nodes/parsenodes.h,v retrieving revision 1.235 diff -c -r1.235 parsenodes.h *** src/include/nodes/parsenodes.h 20 Mar 2003 18:52:48 -0000 1.235 --- src/include/nodes/parsenodes.h 23 Mar 2003 19:49:50 -0000 *************** *** 1210,1216 **** */ #define CURSOR_OPT_BINARY 0x0001 #define CURSOR_OPT_SCROLL 0x0002 ! #define CURSOR_OPT_INSENSITIVE 0x0004 typedef struct DeclareCursorStmt { --- 1210,1218 ---- */ #define CURSOR_OPT_BINARY 0x0001 #define CURSOR_OPT_SCROLL 0x0002 ! #define CURSOR_OPT_NO_SCROLL 0x0004 ! #define CURSOR_OPT_INSENSITIVE 0x0008 ! #define CURSOR_OPT_HOLD 0x0010 typedef struct DeclareCursorStmt { Index: src/include/storage/buffile.h =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/include/storage/buffile.h,v retrieving revision 1.12 diff -c -r1.12 buffile.h *** src/include/storage/buffile.h 20 Jun 2002 20:29:52 -0000 1.12 --- src/include/storage/buffile.h 24 Mar 2003 01:19:10 -0000 *************** *** 34,40 **** * prototypes for functions in buffile.c */ ! extern BufFile *BufFileCreateTemp(void); extern void BufFileClose(BufFile *file); extern size_t BufFileRead(BufFile *file, void *ptr, size_t size); extern size_t BufFileWrite(BufFile *file, void *ptr, size_t size); --- 34,40 ---- * prototypes for functions in buffile.c */ ! extern BufFile *BufFileCreateTemp(bool interTxn); extern void BufFileClose(BufFile *file); extern size_t BufFileRead(BufFile *file, void *ptr, size_t size); extern size_t BufFileWrite(BufFile *file, void *ptr, size_t size); Index: src/include/storage/fd.h =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/include/storage/fd.h,v retrieving revision 1.36 diff -c -r1.36 fd.h *** src/include/storage/fd.h 6 Aug 2002 02:36:35 -0000 1.36 --- src/include/storage/fd.h 24 Mar 2003 01:13:09 -0000 *************** *** 55,61 **** /* Operations on virtual Files --- equivalent to Unix kernel file ops */ extern File FileNameOpenFile(FileName fileName, int fileFlags, int fileMode); extern File PathNameOpenFile(FileName fileName, int fileFlags, int fileMode); ! extern File OpenTemporaryFile(void); extern void FileClose(File file); extern void FileUnlink(File file); extern int FileRead(File file, char *buffer, int amount); --- 55,61 ---- /* Operations on virtual Files --- equivalent to Unix kernel file ops */ extern File FileNameOpenFile(FileName fileName, int fileFlags, int fileMode); extern File PathNameOpenFile(FileName fileName, int fileFlags, int fileMode); ! extern File OpenTemporaryFile(bool keepOverTxn); extern void FileClose(File file); extern void FileUnlink(File file); extern int FileRead(File file, char *buffer, int amount); Index: src/include/tcop/dest.h =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/include/tcop/dest.h,v retrieving revision 1.32 diff -c -r1.32 dest.h *** src/include/tcop/dest.h 4 Sep 2002 20:31:45 -0000 1.32 --- src/include/tcop/dest.h 18 Mar 2003 23:46:25 -0000 *************** *** 70,76 **** Remote, /* results sent to frontend process */ RemoteInternal, /* results sent to frontend process in * internal (binary) form */ ! SPI /* results sent to SPI manager */ } CommandDest; /* ---------------- --- 70,77 ---- Remote, /* results sent to frontend process */ RemoteInternal, /* results sent to frontend process in * internal (binary) form */ ! SPI, /* results sent to SPI manager */ ! Tuplestore /* results sent to Tuplestore */ } CommandDest; /* ---------------- Index: src/include/utils/portal.h =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/include/utils/portal.h,v retrieving revision 1.39 diff -c -r1.39 portal.h *** src/include/utils/portal.h 11 Mar 2003 19:40:24 -0000 1.39 --- src/include/utils/portal.h 23 Mar 2003 20:10:46 -0000 *************** *** 18,34 **** #include "executor/execdesc.h" #include "nodes/memnodes.h" typedef struct PortalData *Portal; typedef struct PortalData { char *name; /* Portal's name */ ! MemoryContext heap; /* subsidiary memory */ QueryDesc *queryDesc; /* Info about query associated with portal */ void (*cleanup) (Portal); /* Cleanup routine (optional) */ ! bool backwardOK; /* is fetch backwards allowed? */ /* * atStart, atEnd and portalPos indicate the current cursor position. * portalPos is zero before the first row, N after fetching N'th row of --- 18,62 ---- #include "executor/execdesc.h" #include "nodes/memnodes.h" + #include "utils/tuplestore.h" + /* + * We support three kinds of scroll behavior: + * + * (1) Neither NO SCROLL nor SCROLL was specified: to remain backward + * compatible, we allow backward fetches here, unless it would + * impose additional runtime overhead to do so. + * + * (2) NO SCROLL was specified: don't allow any backward fetches. + * + * (3) SCROLL was specified: allow all kinds of backward fetches, even + * if we need to take a slight performance hit to do so. + * + * Case #1 is converted to #2 or #3 by looking at the query itself and + * determining if scrollability can be supported without additional + * overhead. + */ + typedef enum + { + DEFAULT_SCROLL, + DISABLE_SCROLL, + ENABLE_SCROLL + } ScrollType; typedef struct PortalData *Portal; typedef struct PortalData { char *name; /* Portal's name */ ! MemoryContext heap; /* memory for storing short-term data */ QueryDesc *queryDesc; /* Info about query associated with portal */ void (*cleanup) (Portal); /* Cleanup routine (optional) */ ! ScrollType scrollType; /* Allow backward fetches? */ ! bool holdOpen; /* hold open after txn ends? */ ! TransactionId createXact; /* the xid of the creating txn */ ! Tuplestorestate *holdStore; /* store for holdable cursors */ ! MemoryContext holdContext; /* memory for long-term data */ ! /* * atStart, atEnd and portalPos indicate the current cursor position. * portalPos is zero before the first row, N after fetching N'th row of *************** *** 58,68 **** extern void EnablePortalManager(void); ! extern void AtEOXact_portals(void); extern Portal CreatePortal(const char *name); ! extern void PortalDrop(Portal portal); extern Portal GetPortalByName(const char *name); extern void PortalSetQuery(Portal portal, QueryDesc *queryDesc, void (*cleanup) (Portal portal)); #endif /* PORTAL_H */ --- 86,97 ---- extern void EnablePortalManager(void); ! extern void AtEOXact_portals(bool isCommit); extern Portal CreatePortal(const char *name); ! extern void PortalDrop(Portal portal, bool persistHoldable); extern Portal GetPortalByName(const char *name); extern void PortalSetQuery(Portal portal, QueryDesc *queryDesc, void (*cleanup) (Portal portal)); + extern void PersistHoldablePortal(Portal portal); #endif /* PORTAL_H */ Index: src/include/utils/tuplestore.h =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/include/utils/tuplestore.h,v retrieving revision 1.9 diff -c -r1.9 tuplestore.h *** src/include/utils/tuplestore.h 9 Mar 2003 03:34:10 -0000 1.9 --- src/include/utils/tuplestore.h 24 Mar 2003 02:02:44 -0000 *************** *** 37,43 **** */ extern Tuplestorestate *tuplestore_begin_heap(bool randomAccess, ! int maxKBytes); extern void tuplestore_puttuple(Tuplestorestate *state, void *tuple); --- 37,44 ---- */ extern Tuplestorestate *tuplestore_begin_heap(bool randomAccess, ! bool interTxn, ! int maxKBytes); extern void tuplestore_puttuple(Tuplestorestate *state, void *tuple); Index: src/pl/plpgsql/src/pl_exec.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/pl/plpgsql/src/pl_exec.c,v retrieving revision 1.81 diff -c -r1.81 pl_exec.c *** src/pl/plpgsql/src/pl_exec.c 9 Mar 2003 02:19:13 -0000 1.81 --- src/pl/plpgsql/src/pl_exec.c 24 Mar 2003 02:01:31 -0000 *************** *** 1752,1758 **** estate->tuple_store_cxt = rsi->econtext->ecxt_per_query_memory; oldcxt = MemoryContextSwitchTo(estate->tuple_store_cxt); ! estate->tuple_store = tuplestore_begin_heap(true, SortMem); MemoryContextSwitchTo(oldcxt); estate->rettupdesc = rsi->expectedDesc; --- 1752,1758 ---- estate->tuple_store_cxt = rsi->econtext->ecxt_per_query_memory; oldcxt = MemoryContextSwitchTo(estate->tuple_store_cxt); ! estate->tuple_store = tuplestore_begin_heap(true, false, SortMem); MemoryContextSwitchTo(oldcxt); estate->rettupdesc = rsi->expectedDesc; Index: src/test/regress/expected/portals.out =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/test/regress/expected/portals.out,v retrieving revision 1.2 diff -c -r1.2 portals.out *** src/test/regress/expected/portals.out 9 Jan 2000 03:48:37 -0000 1.2 --- src/test/regress/expected/portals.out 24 Mar 2003 02:39:30 -0000 *************** *** 1,30 **** -- ! -- PORTALS -- BEGIN; ! DECLARE foo1 CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo2 CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo3 CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo4 CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo5 CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo6 CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo7 CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo8 CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo9 CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo10 CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo11 CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo12 CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo13 CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo14 CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo15 CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo16 CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo17 CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo18 CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo19 CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo20 CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo21 CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo22 CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo23 CURSOR FOR SELECT * FROM tenk1; FETCH 1 in foo1; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- --- 1,30 ---- -- ! -- Cursor regression tests -- BEGIN; ! DECLARE foo1 SCROLL CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo2 SCROLL CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo3 SCROLL CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo4 SCROLL CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo5 SCROLL CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo6 SCROLL CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo7 SCROLL CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo8 SCROLL CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo9 SCROLL CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo10 SCROLL CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo11 SCROLL CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo12 SCROLL CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo13 SCROLL CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo14 SCROLL CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo15 SCROLL CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo16 SCROLL CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo17 SCROLL CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo18 SCROLL CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo19 SCROLL CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo20 SCROLL CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo21 SCROLL CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo22 SCROLL CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo23 SCROLL CURSOR FOR SELECT * FROM tenk1; FETCH 1 in foo1; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- *************** *** 675,678 **** CLOSE foo10; CLOSE foo11; CLOSE foo12; ! end; --- 675,740 ---- CLOSE foo10; CLOSE foo11; CLOSE foo12; ! -- is there a reason why we don't close the rest of the open cursors? ! END; ! -- ! -- NO SCROLL disallows backward fetching ! -- ! BEGIN; ! DECLARE foo24 NO SCROLL CURSOR FOR SELECT * FROM tenk1; ! FETCH 1 FROM foo24; ! unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ! ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- ! 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx ! (1 row) ! ! FETCH BACKWARD 1 FROM foo24; -- should fail ! ERROR: Cursor can only scan forward ! Declare it with SCROLL option to enable backward scan ! END; ! -- ! -- Cursors outside transaction blocks ! -- ! BEGIN; ! DECLARE foo25 SCROLL CURSOR WITH HOLD FOR SELECT * FROM tenk2; ! FETCH FROM foo25; ! unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ! ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- ! 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx ! (1 row) ! ! FETCH FROM foo25; ! unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ! ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- ! 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx ! (1 row) ! ! COMMIT; ! FETCH FROM foo25; ! unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ! ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- ! 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx ! (1 row) ! ! FETCH BACKWARD FROM foo25; ! unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ! ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- ! 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx ! (1 row) ! ! FETCH ABSOLUTE -1 FROM foo25; ! unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ! ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- ! 2968 | 9999 | 0 | 0 | 8 | 8 | 68 | 968 | 968 | 2968 | 2968 | 136 | 137 | EKAAAA | PUOAAA | VVVVxx ! (1 row) ! ! CLOSE foo25; ! -- ! -- ROLLBACK should close holdable cursors ! -- ! BEGIN; ! DECLARE foo26 CURSOR WITH HOLD FOR SELECT * FROM tenk1; ! ROLLBACK; ! -- should fail ! FETCH FROM foo26; ! WARNING: PerformPortalFetch: portal "foo26" not found Index: src/test/regress/sql/portals.sql =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/test/regress/sql/portals.sql,v retrieving revision 1.2 diff -c -r1.2 portals.sql *** src/test/regress/sql/portals.sql 6 Jan 2000 06:41:55 -0000 1.2 --- src/test/regress/sql/portals.sql 24 Mar 2003 02:38:05 -0000 *************** *** 1,54 **** -- ! -- PORTALS -- BEGIN; ! DECLARE foo1 CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo2 CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo3 CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo4 CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo5 CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo6 CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo7 CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo8 CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo9 CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo10 CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo11 CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo12 CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo13 CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo14 CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo15 CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo16 CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo17 CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo18 CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo19 CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo20 CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo21 CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo22 CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo23 CURSOR FOR SELECT * FROM tenk1; FETCH 1 in foo1; --- 1,54 ---- -- ! -- Cursor regression tests -- BEGIN; ! DECLARE foo1 SCROLL CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo2 SCROLL CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo3 SCROLL CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo4 SCROLL CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo5 SCROLL CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo6 SCROLL CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo7 SCROLL CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo8 SCROLL CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo9 SCROLL CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo10 SCROLL CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo11 SCROLL CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo12 SCROLL CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo13 SCROLL CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo14 SCROLL CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo15 SCROLL CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo16 SCROLL CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo17 SCROLL CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo18 SCROLL CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo19 SCROLL CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo20 SCROLL CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo21 SCROLL CURSOR FOR SELECT * FROM tenk1; ! DECLARE foo22 SCROLL CURSOR FOR SELECT * FROM tenk2; ! DECLARE foo23 SCROLL CURSOR FOR SELECT * FROM tenk1; FETCH 1 in foo1; *************** *** 166,170 **** CLOSE foo12; ! end; --- 166,220 ---- CLOSE foo12; ! -- is there a reason why we don't close the rest of the open cursors? + END; + + -- + -- NO SCROLL disallows backward fetching + -- + + BEGIN; + + DECLARE foo24 NO SCROLL CURSOR FOR SELECT * FROM tenk1; + + FETCH 1 FROM foo24; + + FETCH BACKWARD 1 FROM foo24; -- should fail + + END; + + -- + -- Cursors outside transaction blocks + -- + + BEGIN; + + DECLARE foo25 SCROLL CURSOR WITH HOLD FOR SELECT * FROM tenk2; + + FETCH FROM foo25; + + FETCH FROM foo25; + + COMMIT; + + FETCH FROM foo25; + + FETCH BACKWARD FROM foo25; + + FETCH ABSOLUTE -1 FROM foo25; + + CLOSE foo25; + + -- + -- ROLLBACK should close holdable cursors + -- + + BEGIN; + + DECLARE foo26 CURSOR WITH HOLD FOR SELECT * FROM tenk1; + + ROLLBACK; + + -- should fail + FETCH FROM foo26; \ No newline at end of file