From 54f7338fa119802cabb12f7fc0020a167d9690c0 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Thu, 27 Mar 2003 16:51:29 +0000 Subject: This patch implements holdable cursors, following the proposal (materialization into a tuple store) discussed on pgsql-hackers earlier. I've updated the documentation and the regression tests. Notes on the implementation: - I needed to change the tuple store API slightly -- it assumes that it won't be used to hold data across transaction boundaries, so the temp files that it uses for on-disk storage are automatically reclaimed at end-of-transaction. I added a flag to tuplestore_begin_heap() to control this behavior. Is changing the tuple store API in this fashion OK? - in order to store executor results in a tuple store, I added a new CommandDest. This works well for the most part, with one exception: the current DestFunction API doesn't provide enough information to allow the Executor to store results into an arbitrary tuple store (where the particular tuple store to use is chosen by the call site of ExecutorRun). To workaround this, I've temporarily hacked up a solution that works, but is not ideal: since the receiveTuple DestFunction is passed the portal name, we can use that to lookup the Portal data structure for the cursor and then use that to get at the tuple store the Portal is using. This unnecessarily ties the Portal code with the tupleReceiver code, but it works... The proper fix for this is probably to change the DestFunction API -- Tom suggested passing the full QueryDesc to the receiveTuple function. In that case, callers of ExecutorRun could "subclass" QueryDesc to add any additional fields that their particular CommandDest needed to get access to. This approach would work, but I'd like to think about it for a little bit longer before deciding which route to go. In the mean time, the code works fine, so I don't think a fix is urgent. - (semi-related) I added a NO SCROLL keyword to DECLARE CURSOR, and adjusted the behavior of SCROLL in accordance with the discussion on -hackers. - (unrelated) Cleaned up some SGML markup in sql.sgml, copy.sgml Neil Conway --- doc/src/sgml/ref/copy.sgml | 16 ++--- doc/src/sgml/ref/declare.sgml | 137 ++++++++++++++++++++++++++++++------------ doc/src/sgml/ref/fetch.sgml | 53 +++++++--------- doc/src/sgml/ref/move.sgml | 10 +-- doc/src/sgml/sql.sgml | 106 +++++++++++++++++--------------- 5 files changed, 190 insertions(+), 132 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml index 8aa5b90a9ed..389b455fde7 100644 --- a/doc/src/sgml/ref/copy.sgml +++ b/doc/src/sgml/ref/copy.sgml @@ -1,5 +1,5 @@ @@ -133,9 +133,10 @@ COPY table [ ( cursorname [ BINARY ] [ INSENSITIVE ] [ SCROLL ] - CURSOR FOR query +DECLARE cursorname [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] + CURSOR [ { WITH | WITHOUT } HOLD ] FOR query [ FOR { READ ONLY | UPDATE [ OF column [, ...] ] ] @@ -38,7 +38,8 @@ DECLARE cursorname [ BINARY ] [ INS cursorname - The name of the cursor to be used in subsequent FETCH operations. + The name of the cursor to be used in subsequent + FETCH operations. @@ -57,8 +58,20 @@ DECLARE cursorname [ BINARY ] [ INS 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. + 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,8 +80,33 @@ DECLARE cursorname [ BINARY ] [ INS SCROLL - Specifies that the cursor may be used to retrieve rows - in a nonsequential fashion (e.g., backwards). + 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,7 +162,8 @@ DECLARE cursorname [ BINARY ] [ INS - The BINARY, INSENSITIVE, and SCROLL keywords may appear in any order. + The BINARY, INSENSITIVE, + SCROLL keywords may appear in any order. @@ -144,7 +183,7 @@ DECLARE CURSOR - The message returned if the SELECT is run successfully. + The message returned if the SELECT is run successfully. @@ -155,9 +194,8 @@ WARNING: Closing pre-existing portal "cursorname - This message is reported if the same cursor name was already declared - in the current transaction block. The previous definition is - discarded. + This message is reported if a cursor with the same name already + exists. The previous definition is discarded. @@ -168,7 +206,9 @@ ERROR: DECLARE CURSOR may only be used in begin/end transaction blocks - This error occurs if the cursor is not declared within a transaction block. + This error occurs if the cursor is not declared within a + transaction block, and WITH HOLD is not + specified. @@ -193,16 +233,14 @@ ERROR: DECLARE CURSOR may only be used in begin/end transaction blocks - 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. + 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,7 +283,9 @@ ERROR: DECLARE CURSOR may only be used in begin/end transaction blocks - Cursors are only available within transactions. Use + If WITH HOLD is not specified, the cursor + created by this command can only be used within the current + transaction. Use , and @@ -254,12 +294,25 @@ ERROR: DECLARE CURSOR may only be used in begin/end transaction blocks - 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. + 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,7 +324,7 @@ ERROR: DECLARE CURSOR may only be used in begin/end transaction blocks However, ecpg, the embedded SQL preprocessor for PostgreSQL, supports the SQL92 cursor conventions, including those - involving DECLARE and OPEN statements. + involving DECLARE and OPEN statements. @@ -303,13 +356,21 @@ DECLARE liahona CURSOR SQL92 - SQL92 allows cursors only in embedded SQL - and in modules. PostgreSQL permits cursors to be used - interactively. + + 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. + update database information. All PostgreSQL + cursors are read only. + + + + The BINARY keyword is a + PostgreSQL extension. diff --git a/doc/src/sgml/ref/fetch.sgml b/doc/src/sgml/ref/fetch.sgml index 8f3244eb39f..a1f3b13719f 100644 --- a/doc/src/sgml/ref/fetch.sgml +++ b/doc/src/sgml/ref/fetch.sgml @@ -1,5 +1,5 @@ @@ -251,8 +251,7 @@ WARNING: PerformPortalFetch: portal "cursor - If cursor is not known. - The cursor must have been declared within the current transaction block. + There is no cursor with the specified name. @@ -326,7 +325,9 @@ WARNING: PerformPortalFetch: portal "cursorFETCH 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. + 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,16 +340,11 @@ WARNING: PerformPortalFetch: portal "cursor - 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. + 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,12 +353,6 @@ WARNING: PerformPortalFetch: portal "cursor to change cursor position without retrieving data. - Refer to - , - , - and - - for further information about transactions. @@ -379,7 +369,7 @@ WARNING: PerformPortalFetch: portal "cursor - SQL92 defines FETCH for use in embedded contexts only. - Therefore, it describes placing the results into explicit variables using - an INTO clause, for example: + 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,16 +426,18 @@ FETCH ABSOLUTE n 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 + 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 + The FETCH forms involving FORWARD and BACKWARD + (including the forms FETCH count and FETCH ALL, in which + FORWARD is implicit) are PostgreSQL extensions. diff --git a/doc/src/sgml/ref/move.sgml b/doc/src/sgml/ref/move.sgml index f01ee9d8a58..cd6d6aca0fd 100644 --- a/doc/src/sgml/ref/move.sgml +++ b/doc/src/sgml/ref/move.sgml @@ -1,5 +1,5 @@ @@ -64,12 +64,6 @@ MOVE [ direction { FROM | IN } ] to define a cursor. - Refer to - , - , - and - - for further information about transactions. @@ -83,7 +77,7 @@ MOVE [ direction { FROM | IN } ] BEGIN WORK; -DECLARE liahona CURSOR FOR SELECT * FROM films; +DECLARE liahona CURSOR FOR SELECT * FROM films; -- Skip first 5 rows: MOVE FORWARD 5 IN liahona; diff --git a/doc/src/sgml/sql.sgml b/doc/src/sgml/sql.sgml index 9e788ff0616..89de6668028 100644 --- a/doc/src/sgml/sql.sgml +++ b/doc/src/sgml/sql.sgml @@ -1,5 +1,5 @@ @@ -851,7 +851,7 @@ A < B + 3. The most often used command in SQL is the - SELECT statement, + SELECT statement, used to retrieve data. The syntax is: @@ -881,7 +881,7 @@ SELECT [ ALL | DISTINCT [ ON ( expressionSimple Selects - Here are some simple examples using a SELECT statement: + Here are some simple examples using a SELECT statement: Simple Query with Qualification @@ -905,9 +905,10 @@ SELECT * FROM PART - 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: + 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,9 +925,9 @@ SELECT PNAME, PRICE Cam | 25 - Note that the SQL SELECT corresponds to the - projection in relational algebra not to the - selection (see SQL SELECT + corresponds to the projection in relational algebra + not to the selection (see for more details). @@ -1252,15 +1253,15 @@ select sname, pname from supplier 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). + 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,11 +1414,12 @@ SELECT S.SNO, S.SNAME, COUNT(SE.PNO) - 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. + 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,16 +1504,18 @@ SELECT * - 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.) + 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,11 +1532,13 @@ SELECT * - 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. + 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,7 +1676,7 @@ EXCEPT 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 command is: CREATE TABLE table_name @@ -1786,7 +1792,7 @@ CREATE TABLE SELLS To create an index in SQL - the CREATE INDEX command is used. The syntax is: + the CREATE INDEX command is used. The syntax is: CREATE INDEX index_name @@ -1808,10 +1814,11 @@ CREATE INDEX I ON SUPPLIER (SNAME); - 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. + 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,7 +1923,7 @@ SELECT * FROM London_Suppliers To destroy a table (including all tuples stored in that table) the - DROP TABLE command is used: + DROP TABLE command is used: DROP TABLE table_name; @@ -1932,7 +1939,7 @@ DROP TABLE SUPPLIER; - The DROP INDEX command is used to destroy an index: + The DROP INDEX command is used to destroy an index: DROP INDEX index_name; @@ -1940,7 +1947,8 @@ DROP INDEX index_name; - Finally to destroy a given view use the command DROP VIEW: + Finally to destroy a given view use the command DROP + VIEW: DROP VIEW view_name; @@ -1994,7 +2002,7 @@ INSERT INTO SELLS (SNO, PNO) To change one or more attribute values of tuples in a relation the - UPDATE command is used. The syntax is: + UPDATE command is used. The syntax is: UPDATE table_name @@ -2126,7 +2134,7 @@ DELETE FROM SUPPLIER 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 + After that we can use the FETCH command to retrieve a tuple and set the cursor to the next tuple. -- cgit v1.2.3