Re: Updateable cursors patch - Mailing list pgsql-patches
| From | FAST PostgreSQL |
|---|---|
| Subject | Re: Updateable cursors patch |
| Date | |
| Msg-id | 464C15CA.7010900@fast.fujitsu.com.au Whole thread Raw |
| In response to | Re: Updateable cursors patch ("FAST PostgreSQL" <fastpgs@fast.fujitsu.com.au>) |
| Responses |
Re: Updateable cursors patch
(Bruce Momjian <bruce@momjian.us>)
Re: Updateable cursors patch ("Pavel Stehule" <pavel.stehule@gmail.com>) |
| List | pgsql-patches |
Attached is an updated version of the updateable cursors patch against the latest cvs head. Most of the changes in the patch are to make it sync with the changes in CVS recently, for DECLARE CURSOR and EXPLAIN, as mentioned by Jaime in his mail below. Rgds, Arul Shaji FAST PostgreSQL wrote: > Right. I will send an updated patch against the CVS head in the next > couple of days. > > Jaime Casanova wrote: >> On 4/4/07, FAST PostgreSQL <fastpgs@fast.fujitsu.com.au> wrote: >>> Attached is a working updateable cursors patch. The core >>> functionality has >>> been implemented and the patch also contains the regression tests and >>> documentation. >>> >> >> this one doesn't apply cleanly to HEAD because of the changes in >> http://archives.postgresql.org/pgsql-committers/2007-04/msg00447.php >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > > *** /home/arul/ospg/pgsql/src/backend/parser/gram.y 2007-05-03 18:59:44.000000000 +1000 --- pgbase1705/src/backend/parser/gram.y 2007-05-17 14:41:13.000000000 +1000 *************** *** 297,303 **** %type <node> columnDef %type <defelt> def_elem old_aggr_elem %type <node> def_arg columnElem where_clause ! a_expr b_expr c_expr func_expr AexprConst indirection_el columnref in_expr having_clause func_table array_expr %type <list> row type_list array_expr_list %type <node> case_expr case_arg when_clause case_default --- 297,303 ---- %type <node> columnDef %type <defelt> def_elem old_aggr_elem %type <node> def_arg columnElem where_clause ! a_expr b_expr c_expr func_expr AexprConst indirection_el where_current_clause columnref in_expr having_clause func_table array_expr %type <list> row type_list array_expr_list %type <node> case_expr case_arg when_clause case_default *************** *** 377,383 **** CLUSTER COALESCE COLLATE COLUMN COMMENT COMMIT COMMITTED CONCURRENTLY CONNECTION CONSTRAINT CONSTRAINTS CONTENT_P CONVERSION_P CONVERT COPY COST CREATE CREATEDB ! CREATEROLE CREATEUSER CROSS CSV CURRENT_DATE CURRENT_ROLE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS --- 377,383 ---- CLUSTER COALESCE COLLATE COLUMN COMMENT COMMIT COMMITTED CONCURRENTLY CONNECTION CONSTRAINT CONSTRAINTS CONTENT_P CONVERSION_P CONVERT COPY COST CREATE CREATEDB ! CREATEROLE CREATEUSER CROSS CSV CURRENT CURRENT_DATE CURRENT_ROLE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS *************** *** 5715,5721 **** *****************************************************************************/ DeleteStmt: DELETE_P FROM relation_expr_opt_alias ! using_clause where_clause returning_clause { DeleteStmt *n = makeNode(DeleteStmt); n->relation = $3; --- 5715,5721 ---- *****************************************************************************/ DeleteStmt: DELETE_P FROM relation_expr_opt_alias ! using_clause where_current_clause returning_clause { DeleteStmt *n = makeNode(DeleteStmt); n->relation = $3; *************** *** 5771,5777 **** UpdateStmt: UPDATE relation_expr_opt_alias SET set_clause_list from_clause ! where_clause returning_clause { UpdateStmt *n = makeNode(UpdateStmt); --- 5771,5777 ---- UpdateStmt: UPDATE relation_expr_opt_alias SET set_clause_list from_clause ! where_current_clause returning_clause { UpdateStmt *n = makeNode(UpdateStmt); *************** *** 5859,5864 **** --- 5859,5867 ---- /* currently we always set FAST_PLAN option */ n->options = $3 | $5 | CURSOR_OPT_FAST_PLAN; n->query = $7; + if($7 && ((SelectStmt *)$7)->lockingClause) + if (((LockingClause *)((SelectStmt *)$7)->lockingClause)->forUpdate) + n->options |= CURSOR_OPT_FORUPDATE; $$ = (Node *)n; } ; *************** *** 6563,6568 **** --- 6566,6581 ---- ; + where_current_clause: + WHERE a_expr { $$ = $2; } + | WHERE CURRENT OF name + { + $$ = (Node *) makeString($4); + } + | /*EMPTY*/ { $$ = NULL; } + ; + + TableFuncElementList: TableFuncElement { *************** *** 9120,9125 **** --- 9133,9139 ---- | COLUMN | CONSTRAINT | CREATE + | CURRENT | CURRENT_DATE | CURRENT_ROLE | CURRENT_TIME *** /home/arul/ospg/pgsql/src/backend/parser/keywords.c 2007-05-03 18:59:44.000000000 +1000 --- pgbase1705/src/backend/parser/keywords.c 2007-05-17 14:44:05.000000000 +1000 *************** *** 101,106 **** --- 101,107 ---- {"createuser", CREATEUSER}, {"cross", CROSS}, {"csv", CSV}, + {"current", CURRENT}, {"current_date", CURRENT_DATE}, {"current_role", CURRENT_ROLE}, {"current_time", CURRENT_TIME}, *** /home/arul/ospg/pgsql/src/backend/parser/analyze.c 2007-05-03 18:59:42.000000000 +1000 --- pgbase1705/src/backend/parser/analyze.c 2007-05-17 16:39:20.000000000 +1000 *************** *** 126,131 **** --- 126,132 ---- List **extras_before, List **extras_after); static Query *transformAlterTableStmt(ParseState *pstate, AlterTableStmt *stmt, List **extras_before, List **extras_after); + static Node *transformWhereCurrentOfClause(); static void transformColumnDefinition(ParseState *pstate, CreateStmtContext *cxt, ColumnDef *column); *************** *** 305,310 **** --- 306,343 ---- } /* + * transformWhereCurrentOfClause + * + * Eventhough the user gives 'Where current of cursor' it essentially + * is 'where ctid = const'. So we create such a where clause and return + * it. This will then be transformed like any other where clause. In our + * case this will help force a tidscan. + * + * To make this work with prepared statements, We only set a dummy ctid + * now. Later in the executor, this will be replaced by the correct value + * from the appropriate portal. + */ + static Node * + transformWhereCurrentOfClause() + { + ColumnRef *cref; + A_Const *cons; + Node *whereclause; + + cref = makeNode(ColumnRef); + cref->location = -1; + cref->fields = lcons(makeString("ctid"), NIL); + + cons = makeNode(A_Const); + cons->val.type = T_String; + cons->val.val.str = "(0,0)"; + + whereclause = (Node *) makeSimpleA_Expr(AEXPR_OP, "=", (Node *)cref, (Node *)cons, -1); + + return whereclause; + } + + /* * transformStmt - * transform a Parse tree into a Query tree. */ *************** *** 410,416 **** transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt) { Query *qry = makeNode(Query); ! Node *qual; qry->commandType = CMD_DELETE; --- 443,449 ---- transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt) { Query *qry = makeNode(Query); ! Node *qual = NULL; qry->commandType = CMD_DELETE; *************** *** 430,436 **** */ transformFromClause(pstate, stmt->usingClause); ! qual = transformWhereClause(pstate, stmt->whereClause, "WHERE"); qry->returningList = transformReturningList(pstate, stmt->returningList); --- 463,479 ---- */ transformFromClause(pstate, stmt->usingClause); ! if (stmt->whereClause != NULL) ! { ! /* WHERE CURRENT OF cursorname clause */ ! if (IsA(stmt->whereClause, String)) ! { ! qry->cursorName = pstrdup(strVal(stmt->whereClause)); ! stmt->whereClause = transformWhereCurrentOfClause(); ! } ! ! qual = transformWhereClause(pstate, stmt->whereClause, "WHERE"); ! } qry->returningList = transformReturningList(pstate, stmt->returningList); *************** *** 2802,2808 **** transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt) { Query *qry = makeNode(Query); ! Node *qual; ListCell *origTargetList; ListCell *tl; --- 2845,2851 ---- transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt) { Query *qry = makeNode(Query); ! Node *qual = NULL; ListCell *origTargetList; ListCell *tl; *************** *** 2822,2828 **** qry->targetList = transformTargetList(pstate, stmt->targetList); ! qual = transformWhereClause(pstate, stmt->whereClause, "WHERE"); qry->returningList = transformReturningList(pstate, stmt->returningList); --- 2865,2880 ---- qry->targetList = transformTargetList(pstate, stmt->targetList); ! if (stmt->whereClause != NULL) ! { ! /* WHERE CURRENT OF cursorname clause */ ! if (IsA(stmt->whereClause, String)) ! { ! qry->cursorName = pstrdup(strVal(stmt->whereClause)); ! stmt->whereClause = transformWhereCurrentOfClause(); ! } ! qual = transformWhereClause(pstate, stmt->whereClause, "WHERE"); ! } qry->returningList = transformReturningList(pstate, stmt->returningList); *************** *** 3151,3156 **** --- 3203,3218 ---- Query *result; List *extras_before = NIL, *extras_after = NIL; + ListCell *rm; + + /* + * We don't allow an updateable WITH HOLD cursor. We don't support + * this feature yet. + */ + if ( (stmt->options & CURSOR_OPT_HOLD) && (stmt->options & CURSOR_OPT_FORUPDATE) ) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("Holdable cursors cannot be updated"))); /* * Don't allow both SCROLL and NO SCROLL to be specified *************** *** 3178,3189 **** (errcode(ERRCODE_INVALID_CURSOR_DEFINITION), errmsg("DECLARE CURSOR cannot specify INTO"))); /* Implementation restriction (might go away someday) */ if (result->rowMarks != NIL) ! ereport(ERROR, ! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("DECLARE CURSOR ... FOR UPDATE/SHARE is not supported"), ! errdetail("Cursors must be READ ONLY."))); /* We won't need the raw querytree any more */ stmt->query = NULL; --- 3240,3280 ---- (errcode(ERRCODE_INVALID_CURSOR_DEFINITION), errmsg("DECLARE CURSOR cannot specify INTO"))); + /* Disallow implicit/explicit joins, and ORDER BY on FOR UPDATE cursors */ + if (stmt->options & CURSOR_OPT_FORUPDATE) + { + if (list_length(result->rtable) != 1) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("Only one target table can be specified for updateable cursors"))); + else if (result->hasSubLinks) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("Sub-queries cannot be specified for updateable cursors"), + errdetail("SELECT that results in an implicit join is not yet supported"))); + + if (list_length(result->sortClause) != 0) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_CURSOR_DEFINITION), + errmsg("ORDER BY is not allowed on FOR UPDATE cursors"))); + } + } + /* Implementation restriction (might go away someday) */ if (result->rowMarks != NIL) ! { ! foreach(rm, result->rowMarks) ! { ! RowMarkClause *rmc = (RowMarkClause *) lfirst(rm); ! ! if (rmc->forUpdate == FALSE) ! ereport(ERROR, ! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("DECLARE CURSOR ... FOR SHARE is not supported"), ! errdetail("Cursors must not be FOR SHARE."))); ! } ! } /* We won't need the raw querytree any more */ stmt->query = NULL; *** /home/arul/ospg/pgsql/src/backend/optimizer/path/allpaths.c 2007-05-03 18:59:35.000000000 +1000 --- pgbase1705/src/backend/optimizer/path/allpaths.c 2007-05-17 14:58:52.000000000 +1000 *************** *** 218,223 **** --- 218,244 ---- return; } + /* + * For positioned update or delete, we need to force a tid scan. This + * is because we already know which row to delete and also the ctid of + * the row to be deleted. + */ + if ((root->parse->commandType == CMD_DELETE || + root->parse->commandType == CMD_UPDATE) && + root->parse->cursorName != NULL) + { + /* Set output-rows estimate to 1 */ + rel->rows = 1; + + /* Create a tid scan path */ + create_tidscan_paths(root, rel); + + /* Select cheapest path (pretty easy in this case...) */ + set_cheapest(rel); + + return; + } + /* Mark rel with estimated output rows, width, etc */ set_baserel_size_estimates(root, rel); *** /home/arul/ospg/pgsql/src/backend/optimizer/plan/planner.c 2007-05-17 13:01:44.000000000 +1000 --- pgbase1705/src/backend/optimizer/plan/planner.c 2007-05-17 15:02:14.000000000 +1000 *************** *** 170,175 **** --- 170,185 ---- result->rowMarks = parse->rowMarks; result->nParamExec = list_length(glob->paramlist); + /* + * If the command is an update or delete on cursor, we need + * the cursor name. + */ + if ((parse->commandType == CMD_DELETE || + parse->commandType == CMD_UPDATE) && + parse->cursorName != NULL) + /* pass the portal name into the PlannedStmt structure */ + result->cursorName = pstrdup(parse->cursorName); + return result; } *** /home/arul/ospg/pgsql/src/backend/tcop/pquery.c 2007-05-03 18:59:49.000000000 +1000 --- pgbase1705/src/backend/tcop/pquery.c 2007-05-17 15:04:08.000000000 +1000 *************** *** 940,945 **** --- 940,957 ---- else { ActiveSnapshot = queryDesc->snapshot; + + /* + * If this is a SELECT for updateable cursor, store the + * resulting ctid. + */ + if (portal->cursorOptions & CURSOR_OPT_FORUPDATE) + { + if (ItemPointerIsValid(&queryDesc->estate->es_uc_ctid)) + ItemPointerSetInvalid(&queryDesc->estate->es_uc_ctid); + queryDesc->estate->es_store_uctuple = true; + } + ExecutorRun(queryDesc, direction, count); nprocessed = queryDesc->estate->es_processed; } *************** *** 982,987 **** --- 994,1011 ---- else { ActiveSnapshot = queryDesc->snapshot; + + /* + * If this is a SELECT for updateable cursor, store the + * resulting ctid. + */ + if (portal->cursorOptions & CURSOR_OPT_FORUPDATE) + { + queryDesc->estate->es_store_uctuple = true; + if (ItemPointerIsValid(&queryDesc->estate->es_uc_ctid)) + ItemPointerSetInvalid(&queryDesc->estate->es_uc_ctid); + } + ExecutorRun(queryDesc, direction, count); nprocessed = queryDesc->estate->es_processed; } *** /home/arul/ospg/pgsql/src/backend/executor/execMain.c 2007-05-03 18:59:32.000000000 +1000 --- pgbase1705/src/backend/executor/execMain.c 2007-05-17 15:26:42.000000000 +1000 *************** *** 41,46 **** --- 41,47 ---- #include "catalog/toasting.h" #include "commands/tablespace.h" #include "commands/trigger.h" + #include "commands/portalcmds.h" #include "executor/execdebug.h" #include "executor/instrument.h" #include "executor/nodeSubplan.h" *************** *** 167,172 **** --- 168,182 ---- estate->es_crosscheck_snapshot = queryDesc->crosscheck_snapshot; estate->es_instrument = queryDesc->doInstrument; + /* Determine if this is a positioned delete/update */ + if ( ((queryDesc->operation == CMD_DELETE) || + (queryDesc->operation == CMD_UPDATE)) && + queryDesc->plannedstmt->cursorName ) + { + estate->es_use_ucplan = true; + estate->cursorName = pstrdup(queryDesc->plannedstmt->cursorName); + } + /* * Initialize the plan state tree */ *************** *** 627,632 **** --- 637,652 ---- } /* + * If this is a 'delete/update where current of', we need to update + * the plan with the correct ctid. We do it here so that, it will work + * with the prepared statements as well. + */ + if (estate->es_use_ucplan) + { + updateCursorPlan(estate, plan); + } + + /* * Initialize the private state information for all the nodes in the query * tree. This opens files, allocates storage and leaves us ready to start * processing tuples. *** /home/arul/ospg/pgsql/src/backend/executor/execScan.c 2007-02-02 11:07:03.000000000 +1100 --- pgbase1705/src/backend/executor/execScan.c 2007-05-17 15:11:45.000000000 +1000 *************** *** 65,71 **** * all the overhead and return the raw scan tuple. */ if (!qual && !projInfo) ! return (*accessMtd) (node); /* * Check to see if we're still projecting out tuples from a previous scan --- 65,84 ---- * all the overhead and return the raw scan tuple. */ if (!qual && !projInfo) ! { ! resultSlot = (*accessMtd) (node); ! ! /* ! * Place the current tuple's ctid in estate if we are scanning ! * for updateable cursors ! */ ! if (node->ps.state->es_store_uctuple) ! if(PointerIsValid(resultSlot) && ! PointerIsValid(resultSlot->tts_tuple) ) ! ItemPointerCopy(&resultSlot->tts_tuple->t_self, ! &node->ps.state->es_uc_ctid); ! return resultSlot; ! } /* * Check to see if we're still projecting out tuples from a previous scan *************** *** 130,135 **** --- 143,156 ---- */ if (!qual || ExecQual(qual, econtext, false)) { + /* + * Place the current tuple's ctid in estate if we are scanning + * for updateable cursors + */ + if (node->ps.state->es_store_uctuple) + if(PointerIsValid(slot) && PointerIsValid(slot->tts_tuple) ) + ItemPointerCopy(&slot->tts_tuple->t_self, + &node->ps.state->es_uc_ctid); /* * Found a satisfactory scan tuple. */ *** /home/arul/ospg/pgsql/src/backend/utils/mmgr/portalmem.c 2007-05-03 18:59:57.000000000 +1000 --- pgbase1705/src/backend/utils/mmgr/portalmem.c 2007-05-17 15:12:32.000000000 +1000 *************** *** 886,892 **** * build tupdesc for result tuples. This must match the definition of * the pg_cursors view in system_views.sql */ ! tupdesc = CreateTemplateTupleDesc(6, false); TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name", TEXTOID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 2, "statement", --- 886,892 ---- * build tupdesc for result tuples. This must match the definition of * the pg_cursors view in system_views.sql */ ! tupdesc = CreateTemplateTupleDesc(7, false); TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name", TEXTOID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 2, "statement", *************** *** 897,903 **** BOOLOID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 5, "is_scrollable", BOOLOID, -1, 0); ! TupleDescInitEntry(tupdesc, (AttrNumber) 6, "creation_time", TIMESTAMPTZOID, -1, 0); /* --- 897,905 ---- BOOLOID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 5, "is_scrollable", BOOLOID, -1, 0); ! TupleDescInitEntry(tupdesc, (AttrNumber) 6, "is_updateable", ! BOOLOID, -1, 0); ! TupleDescInitEntry(tupdesc, (AttrNumber) 7, "creation_time", TIMESTAMPTZOID, -1, 0); /* *************** *** 911,918 **** { Portal portal = hentry->portal; HeapTuple tuple; ! Datum values[6]; ! bool nulls[6]; /* report only "visible" entries */ if (!portal->visible) --- 913,920 ---- { Portal portal = hentry->portal; HeapTuple tuple; ! Datum values[7]; ! bool nulls[7]; /* report only "visible" entries */ if (!portal->visible) *************** *** 932,938 **** values[2] = BoolGetDatum(portal->cursorOptions & CURSOR_OPT_HOLD); values[3] = BoolGetDatum(portal->cursorOptions & CURSOR_OPT_BINARY); values[4] = BoolGetDatum(portal->cursorOptions & CURSOR_OPT_SCROLL); ! values[5] = TimestampTzGetDatum(portal->creation_time); tuple = heap_form_tuple(tupdesc, values, nulls); --- 934,941 ---- values[2] = BoolGetDatum(portal->cursorOptions & CURSOR_OPT_HOLD); values[3] = BoolGetDatum(portal->cursorOptions & CURSOR_OPT_BINARY); values[4] = BoolGetDatum(portal->cursorOptions & CURSOR_OPT_SCROLL); ! values[5] = BoolGetDatum(portal->cursorOptions & CURSOR_OPT_FORUPDATE); ! values[6] = TimestampTzGetDatum(portal->creation_time); tuple = heap_form_tuple(tupdesc, values, nulls); *** /home/arul/ospg/pgsql/src/backend/commands/portalcmds.c 2007-05-03 18:59:30.000000000 +1000 --- pgbase1705/src/backend/commands/portalcmds.c 2007-05-17 16:07:11.000000000 +1000 *************** *** 430,432 **** --- 430,546 ---- */ MemoryContextDeleteChildren(PortalGetHeapMemory(portal)); } + + /* + * updateCursorPlan + * Updates the plan with the correct ctid. ie the ctid that was stored + * in the portal as a result of the previous fetch command. This method + * is called only during 'UPDATE/DELETE WHERE CURRENT OF'. + * + * estate: estate of the current command + * plan: The current scan plan. Must be a tidscan plan. + */ + void + updateCursorPlan(EState *estate, Plan *plan) + { + QueryDesc *queryDesc; + Portal portal; + Oid target_relid; + ListCell *l; + bool found = false; + + List *quals; + TidScan *tidscannode; + Node *node; + + /* + * Disallow empty-string cursor name (conflicts with protocol-level + * unnamed portal). + */ + if (!estate->cursorName || estate->cursorName[0] == '\0') + ereport(ERROR, + (errcode(ERRCODE_INVALID_CURSOR_NAME), + errmsg("invalid cursor name: must not be empty"))); + + /* Has to be a tidscan. If it isn't we cannot update it */ + if (! IsA(plan, TidScan)) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("invalid planner state"))); + + /* get the portal from the portal name */ + portal = GetPortalByName(estate->cursorName); + + if (!PortalIsValid(portal)) + { + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_CURSOR), + errmsg("cursor \"%s\" does not exist", estate->cursorName))); + } + else if (!(portal->cursorOptions & CURSOR_OPT_FORUPDATE)) + { + /* Positioned updates only allowed for updateable cursors. */ + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("cursor is not updateable"))); + } + + queryDesc = PortalGetQueryDesc(portal); + + /* + * Search the RTEs for a matching table. We search the whole list eventhough + * it will only have one entry now. When we support joins later this should + * hold. + */ + target_relid = RelationGetRelid(estate->es_result_relation_info->ri_RelationDesc); + foreach(l, queryDesc->plannedstmt->rtable) + { + RangeTblEntry *rte = (RangeTblEntry *) lfirst(l); + if (rte->relid == target_relid) + { + found = true; + break; + } + } + if (!found) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_CURSOR_NAME), + errmsg("cursor \"%s\" was not declared for table \"%s\"", + estate->cursorName, + RelationGetRelationName(estate->es_result_relation_info->ri_RelationDesc)))); + } + + if (ItemPointerIsValid(&queryDesc->estate->es_uc_ctid) ) + { + /* + * Retrieve the qual list from the scan plan and update it + * with the correct tid. + */ + tidscannode = (TidScan *) plan; + quals = (List *)tidscannode->tidquals; + + /* There will be only one entry in the qual list */ + foreach(l, quals) + { + node = (Node *) lfirst(l); + if (IsA(node, OpExpr)) + { + List *argslist; + ListCell *argcell; + + argslist = ((OpExpr *) node)->args; + foreach(argcell, argslist) + { + if (IsA (((Node *)lfirst(argcell)), Const)) + { + Const *cons; + cons = (Const *)lfirst(argcell); + /* update the ctid const */ + cons->constvalue = PointerGetDatum(&queryDesc->estate->es_uc_ctid); + } + } + } + } + } + } *** /home/arul/ospg/pgsql/src/backend/nodes/copyfuncs.c 2007-05-03 18:59:33.000000000 +1000 --- pgbase1705/src/backend/nodes/copyfuncs.c 2007-05-17 15:21:59.000000000 +1000 *************** *** 84,89 **** --- 84,90 ---- COPY_NODE_FIELD(returningLists); COPY_NODE_FIELD(rowMarks); COPY_SCALAR_FIELD(nParamExec); + COPY_STRING_FIELD(cursorName); return newnode; } *** /home/arul/ospg/pgsql/src/include/nodes/plannodes.h 2007-05-03 19:00:03.000000000 +1000 --- pgbase1705/src/include/nodes/plannodes.h 2007-05-17 15:21:59.000000000 +1000 *************** *** 69,74 **** --- 69,77 ---- List *rowMarks; /* a list of RowMarkClause's */ int nParamExec; /* number of PARAM_EXEC Params used */ + + char *cursorName; /* The updateable cursor name for the current + * UPDATE/DELETE stmt */ } PlannedStmt; /* macro for fetching the Plan associated with a SubPlan node */ *** /home/arul/ospg/pgsql/src/include/nodes/parsenodes.h 2007-05-03 19:00:03.000000000 +1000 --- pgbase1705/src/include/nodes/parsenodes.h 2007-05-17 15:21:59.000000000 +1000 *************** *** 131,136 **** --- 131,139 ---- Node *setOperations; /* set-operation tree if this is top level of * a UNION/INTERSECT/EXCEPT query */ + + char *cursorName; /* The updateable cursor name for the current + * UPDATE/DELETE query */ } Query; *************** *** 1441,1446 **** --- 1444,1450 ---- #define CURSOR_OPT_INSENSITIVE 0x0008 /* INSENSITIVE (unimplemented) */ #define CURSOR_OPT_HOLD 0x0010 /* WITH HOLD */ #define CURSOR_OPT_FAST_PLAN 0x0020 /* prefer fast-start plan */ + #define CURSOR_OPT_FORUPDATE 0x0040 /* Updateable cursor */ typedef struct DeclareCursorStmt { *** /home/arul/ospg/pgsql/src/include/commands/portalcmds.h 2007-05-03 19:00:01.000000000 +1000 --- pgbase1705/src/include/commands/portalcmds.h 2007-05-17 15:21:59.000000000 +1000 *************** *** 31,34 **** --- 31,36 ---- extern void PersistHoldablePortal(Portal portal); + extern void updateCursorPlan(EState *estate, Plan *plan); + #endif /* PORTALCMDS_H */ *** /home/arul/ospg/pgsql/src/include/nodes/execnodes.h 2007-05-17 13:02:10.000000000 +1000 --- pgbase1705/src/include/nodes/execnodes.h 2007-05-17 15:21:59.000000000 +1000 *************** *** 349,354 **** --- 349,360 ---- bool *es_evTupleNull; /* local array of EPQ status */ HeapTuple *es_evTuple; /* shared array of EPQ substitute tuples */ bool es_useEvalPlan; /* evaluating EPQ tuples? */ + + /* Info for storing updateable cursor details */ + bool es_store_uctuple; + bool es_use_ucplan; + char *cursorName; + ItemPointerData es_uc_ctid; /* ctid for updateable cursors. */ } EState; *** /home/arul/ospg/pgsql/src/backend/catalog/system_views.sql 2007-04-12 17:02:11.000000000 +1000 --- pgbase1705/src/backend/catalog/system_views.sql 2007-05-17 15:21:59.000000000 +1000 *************** *** 150,159 **** CREATE VIEW pg_cursors AS SELECT C.name, C.statement, C.is_holdable, C.is_binary, ! C.is_scrollable, C.creation_time FROM pg_cursor() AS C (name text, statement text, is_holdable boolean, is_binary boolean, ! is_scrollable boolean, creation_time timestamptz); CREATE VIEW pg_prepared_xacts AS SELECT P.transaction, P.gid, P.prepared, --- 150,159 ---- CREATE VIEW pg_cursors AS SELECT C.name, C.statement, C.is_holdable, C.is_binary, ! C.is_scrollable, C.is_updateable, C.creation_time FROM pg_cursor() AS C (name text, statement text, is_holdable boolean, is_binary boolean, ! is_scrollable boolean, is_updateable boolean, creation_time timestamptz); CREATE VIEW pg_prepared_xacts AS SELECT P.transaction, P.gid, P.prepared, *** /home/arul/ospg/pgsql/src/test/regress/expected/rules.out 2007-04-12 17:02:49.000000000 +1000 --- pgbase1705/src/test/regress/expected/rules.out 2007-05-17 15:21:59.000000000 +1000 *************** *** 1279,1285 **** viewname | definition --------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- iexit | SELECT ih."name", ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, rampr WHERE (ih.thepath ## r.thepath); ! pg_cursors | SELECT c."name", c."statement", c.is_holdable, c.is_binary, c.is_scrollable, c.creation_timeFROM pg_cursor() c("name" text, "statement" text, is_holdable boolean, is_binary boolean, is_scrollable boolean,creation_time timestamp with time zone); pg_group | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT pg_auth_members.memberFROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid WHERE(NOT pg_authid.rolcanlogin); pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS"tablespace", pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_classi ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace tON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char")); pg_locks | SELECT l.locktype, l."database", l.relation, l.page, l.tuple, l.transactionid, l.classid, l.objid,l.objsubid, l."transaction", l.pid, l."mode", l."granted" FROM pg_lock_status() l(locktype text, "database" oid,relation oid, page integer, tuple smallint, transactionid xid, classid oid, objid oid, objsubid smallint, "transaction"xid, pid integer, "mode" text, "granted" boolean); --- 1279,1285 ---- viewname | definition --------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- iexit | SELECT ih."name", ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, rampr WHERE (ih.thepath ## r.thepath); ! pg_cursors | SELECT c."name", c."statement", c.is_holdable, c.is_binary, c.is_scrollable, c.is_updateable,c.creation_time FROM pg_cursor() c("name" text, "statement" text, is_holdable boolean, is_binary boolean,is_scrollable boolean, is_updateable boolean, creation_time timestamp with time zone); pg_group | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT pg_auth_members.memberFROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid WHERE(NOT pg_authid.rolcanlogin); pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS"tablespace", pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_classi ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace tON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char")); pg_locks | SELECT l.locktype, l."database", l.relation, l.page, l.tuple, l.transactionid, l.classid, l.objid,l.objsubid, l."transaction", l.pid, l."mode", l."granted" FROM pg_lock_status() l(locktype text, "database" oid,relation oid, page integer, tuple smallint, transactionid xid, classid oid, objid oid, objsubid smallint, "transaction"xid, pid integer, "mode" text, "granted" boolean); *** /home/arul/ospg/pgsql/src/test/regress/expected/portals.out 2007-05-03 19:00:09.000000000 +1000 --- pgbase1705/src/test/regress/expected/portals.out 2007-05-17 18:10:05.000000000 +1000 *************** *** 869,874 **** --- 869,1011 ---- c2 | declare c2 cursor with hold for select count_tt1_v(), count_tt1_s(); | t | f | f (1 row) + -- Create an updatable cursor and show that delete at position works + CREATE TABLE t4(i int, j text); + INSERT INTO t4 VALUES(1, 'one'); + INSERT INTO t4 VALUES(2, 'two'); + INSERT INTO t4 VALUES(3, 'three'); + INSERT INTO t4 VALUES(4, 'four'); + INSERT INTO t4 VALUES(5, 'five'); + INSERT INTO t4 VALUES(6, 'six'); + INSERT INTO t4 VALUES(7, 'seven'); + INSERT INTO t4 VALUES(8, 'eight'); + INSERT INTO t4 VALUES(9, 'nine'); + SELECT * FROM t4; + i | j + ---+------- + 1 | one + 2 | two + 3 | three + 4 | four + 5 | five + 6 | six + 7 | seven + 8 | eight + 9 | nine + (9 rows) + + BEGIN; + DECLARE c1 CURSOR FOR SELECT * FROM t4 FOR UPDATE; + FETCH c1; + i | j + ---+----- + 1 | one + (1 row) + + DELETE FROM t4 WHERE CURRENT OF c1; + FETCH ALL FROM c1; + i | j + ---+------- + 2 | two + 3 | three + 4 | four + 5 | five + 6 | six + 7 | seven + 8 | eight + 9 | nine + (8 rows) + + SELECT * FROM t4; + i | j + ---+------- + 2 | two + 3 | three + 4 | four + 5 | five + 6 | six + 7 | seven + 8 | eight + 9 | nine + (8 rows) + + CLOSE c1; + COMMIT; + -- Create an updatable cursor and show that update at position works + SELECT * FROM t4; + i | j + ---+------- + 2 | two + 3 | three + 4 | four + 5 | five + 6 | six + 7 | seven + 8 | eight + 9 | nine + (8 rows) + + BEGIN; + DECLARE c1 CURSOR FOR SELECT * FROM t4 FOR UPDATE; + FETCH c1; + i | j + ---+----- + 2 | two + (1 row) + + UPDATE t4 SET i = 8 WHERE CURRENT OF c1; + SELECT * FROM t4; + i | j + ---+------- + 3 | three + 4 | four + 5 | five + 6 | six + 7 | seven + 8 | eight + 9 | nine + 8 | two + (8 rows) + + CLOSE c1; + COMMIT; + -- Updatable cursor combined with MOVE + BEGIN; + DECLARE c1 CURSOR FOR SELECT * FROM t4 FOR UPDATE; + FETCH c1; + i | j + ---+------- + 3 | three + (1 row) + + UPDATE t4 SET i = 10 WHERE CURRENT OF c1; + MOVE 5 IN c1; + FETCH c1; + i | j + ---+------ + 9 | nine + (1 row) + + UPDATE t4 SET i = 12 WHERE CURRENT OF c1; + CLOSE c1; + COMMIT; + -- Update on j, ordering on i + BEGIN; + DECLARE c1 CURSOR FOR SELECT j FROM t4 ORDER BY i FOR UPDATE OF i; + ERROR: relation "i" in FOR UPDATE/SHARE clause not found in FROM clause + ROLLBACK; + -- Attempt a DELETE WHERE CURRENT OF prior to FETCHing a row, should fail + BEGIN; + DECLARE c1 CURSOR FOR SELECT * FROM t4 FOR UPDATE; + -- should fail + DELETE FROM t4 WHERE CURRENT OF c1; + ROLLBACK; + --Attempt to use a WITH HOLD updatable cursor, should fail + BEGIN; + DECLARE c1 CURSOR WITH HOLD FOR SELECT * FROM t4 FOR UPDATE; + ERROR: Holdable cursors cannot be updated + ROLLBACK; + DROP TABLE t4; -- test CLOSE ALL; SELECT name FROM pg_cursors ORDER BY 1; name *** /home/arul/ospg/pgsql/src/test/regress/sql/portals.sql 2007-05-03 19:00:10.000000000 +1000 --- pgbase1705/src/test/regress/sql/portals.sql 2007-05-17 17:58:41.000000000 +1000 *************** *** 304,309 **** --- 304,370 ---- SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors; EXECUTE cprep; + + -- Create an updatable cursor and show that delete at position works + CREATE TABLE t4(i int, j text); + INSERT INTO t4 VALUES(1, 'one'); + INSERT INTO t4 VALUES(2, 'two'); + INSERT INTO t4 VALUES(3, 'three'); + INSERT INTO t4 VALUES(4, 'four'); + INSERT INTO t4 VALUES(5, 'five'); + INSERT INTO t4 VALUES(6, 'six'); + INSERT INTO t4 VALUES(7, 'seven'); + INSERT INTO t4 VALUES(8, 'eight'); + INSERT INTO t4 VALUES(9, 'nine'); + + SELECT * FROM t4; + BEGIN; + DECLARE c1 CURSOR FOR SELECT * FROM t4 FOR UPDATE; + FETCH c1; + DELETE FROM t4 WHERE CURRENT OF c1; + FETCH ALL FROM c1; + SELECT * FROM t4; + CLOSE c1; + COMMIT; + + -- Create an updatable cursor and show that update at position works + SELECT * FROM t4; + BEGIN; + DECLARE c1 CURSOR FOR SELECT * FROM t4 FOR UPDATE; + FETCH c1; + UPDATE t4 SET i = 8 WHERE CURRENT OF c1; + SELECT * FROM t4; + CLOSE c1; + COMMIT; + + -- Updatable cursor combined with MOVE + BEGIN; + DECLARE c1 CURSOR FOR SELECT * FROM t4 FOR UPDATE; + FETCH c1; + UPDATE t4 SET i = 10 WHERE CURRENT OF c1; + MOVE 5 IN c1; + FETCH c1; + UPDATE t4 SET i = 12 WHERE CURRENT OF c1; + CLOSE c1; + COMMIT; + -- Update on j, ordering on i + BEGIN; + DECLARE c1 CURSOR FOR SELECT j FROM t4 ORDER BY i FOR UPDATE OF i; + ROLLBACK; + + -- Attempt a DELETE WHERE CURRENT OF prior to FETCHing a row, should fail + BEGIN; + DECLARE c1 CURSOR FOR SELECT * FROM t4 FOR UPDATE; + -- should fail + DELETE FROM t4 WHERE CURRENT OF c1; + ROLLBACK; + + --Attempt to use a WITH HOLD updatable cursor, should fail + BEGIN; + DECLARE c1 CURSOR WITH HOLD FOR SELECT * FROM t4 FOR UPDATE; + ROLLBACK; + DROP TABLE t4; + -- test CLOSE ALL; SELECT name FROM pg_cursors ORDER BY 1; CLOSE ALL; *** /home/arul/ospg/pgsql/doc/src/sgml/ref/delete.sgml 2007-02-01 11:28:19.000000000 +1100 --- pgbase1705/doc/src/sgml/ref/delete.sgml 2007-05-17 17:58:41.000000000 +1000 *************** *** 22,28 **** <synopsis> DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable>] [ USING <replaceable class="PARAMETER">usinglist</replaceable> ] ! [ WHERE <replaceable class="PARAMETER">condition</replaceable> ] [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ AS <replaceable class="parameter">output_name</replaceable>] [, ...] ] </synopsis> </refsynopsisdiv> --- 22,28 ---- <synopsis> DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable>] [ USING <replaceable class="PARAMETER">usinglist</replaceable> ] ! [ [ WHERE <replaceable class="PARAMETER">condition</replaceable> ] | [ WHERE CURRENT OF <replaceable class="PARAMETER">cursor_name</replaceable>] ] [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ AS <replaceable class="parameter">output_name</replaceable>] [, ...] ] </synopsis> </refsynopsisdiv> *************** *** 162,167 **** --- 162,176 ---- </para> </listitem> </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">cursor_name</replaceable></term> + <listitem> + <para> + The name of the cursor to use with <command>WHERE CURRENT OF</> condition. + </para> + </listitem> + </varlistentry> </variablelist> </refsect1> *************** *** 238,243 **** --- 247,259 ---- DELETE FROM tasks WHERE status = 'DONE' RETURNING *; </programlisting> </para> + + <para> + Delete the task, on which the cursor c_tasks is pointing currently: + <programlisting> + DELETE FROM tasks WHERE CURRENT OF c_tasks; + </programlisting> + </para> </refsect1> <refsect1> *** /home/arul/ospg/pgsql/doc/src/sgml/ref/declare.sgml 2007-02-01 10:26:03.000000000 +1100 --- pgbase1705/doc/src/sgml/ref/declare.sgml 2007-05-17 17:58:41.000000000 +1000 *************** *** 171,181 **** <para> <literal>FOR READ ONLY</literal> indicates that the cursor will be used in a read-only mode. <literal>FOR UPDATE</literal> ! indicates that the cursor will be used to update tables. Since ! cursor updates are not currently supported in ! <productname>PostgreSQL</productname>, specifying <literal>FOR ! UPDATE</literal> will cause an error message and specifying ! <literal>FOR READ ONLY</literal> has no effect. </para> </listitem> </varlistentry> --- 171,177 ---- <para> <literal>FOR READ ONLY</literal> indicates that the cursor will be used in a read-only mode. <literal>FOR UPDATE</literal> ! indicates that the cursor will be used to update tables. </para> </listitem> </varlistentry> *************** *** 184,191 **** <term><replaceable class="parameter">column</replaceable></term> <listitem> <para> ! Column(s) to be updated by the cursor. Since cursor updates are ! not currently supported in <productname>PostgreSQL</productname>, the <literal>FOR UPDATE</literal> clause provokes an error message. </para> --- 180,187 ---- <term><replaceable class="parameter">column</replaceable></term> <listitem> <para> ! Column(s) to be updated by the cursor. Since cursor updates on column(s) ! are not currently supported in <productname>PostgreSQL</productname>, the <literal>FOR UPDATE</literal> clause provokes an error message. </para> *************** *** 286,296 **** </para> <para> - The SQL standard allows cursors to update table data. All - <productname>PostgreSQL</> cursors are read only. - </para> - - <para> Binary cursors are a <productname>PostgreSQL</productname> extension. </para> --- 282,287 ---- *** /home/arul/ospg/pgsql/doc/src/sgml/ref/update.sgml 2007-02-01 11:28:19.000000000 +1100 --- pgbase1705/doc/src/sgml/ref/update.sgml 2007-05-17 17:58:41.000000000 +1000 *************** *** 24,30 **** SET { <replaceable class="PARAMETER">column</replaceable> = { <replaceable class="PARAMETER">expression</replaceable>| DEFAULT } | ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable>| DEFAULT } [, ...] ) } [, ...] [ FROM <replaceable class="PARAMETER">fromlist</replaceable> ] ! [ WHERE <replaceable class="PARAMETER">condition</replaceable> ] [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ AS <replaceable class="parameter">output_name</replaceable>] [, ...] ] </synopsis> </refsynopsisdiv> --- 24,30 ---- SET { <replaceable class="PARAMETER">column</replaceable> = { <replaceable class="PARAMETER">expression</replaceable>| DEFAULT } | ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable>| DEFAULT } [, ...] ) } [, ...] [ FROM <replaceable class="PARAMETER">fromlist</replaceable> ] ! [ [ WHERE <replaceable class="PARAMETER">condition</replaceable> ] | [ WHERE CURRENT OF <replaceable class="PARAMETER">cursor_name</replaceable>] ] [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ AS <replaceable class="parameter">output_name</replaceable>] [, ...] ] </synopsis> </refsynopsisdiv> *************** *** 181,186 **** --- 181,195 ---- </para> </listitem> </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">cursor_name</replaceable></term> + <listitem> + <para> + The name of the cursor to use with <command>WHERE CURRENT OF</> condition. + </para> + </listitem> + </varlistentry> </variablelist> </refsect1> *************** *** 244,249 **** --- 253,269 ---- </programlisting> </para> + <para> + Change the word to <literal>Dramatic</> in the + column <structfield>kind</> of the table <structname>films</structname>, + on the row on which the cursor c_tasks is positioned currently: + + <programlisting> + UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_tasks; + </programlisting> + </para> + + <para> Adjust temperature entries and reset precipitation to its default value in one row of the table <structname>weather</structname>:
pgsql-patches by date: