*** /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 columnDef %type def_elem old_aggr_elem %type 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 row type_list array_expr_list %type case_expr case_arg when_clause case_default --- 297,303 ---- %type columnDef %type def_elem old_aggr_elem %type 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 row type_list array_expr_list %type 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, ramp r WHERE (ih.thepath ## r.thepath); ! pg_cursors | SELECT c."name", c."statement", c.is_holdable, c.is_binary, c.is_scrollable, c.creation_time FROM 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.member FROM 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_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((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, ramp r 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.member FROM 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_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((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 **** DELETE FROM [ ONLY ] table [ [ AS ] alias ] [ USING usinglist ] ! [ WHERE condition ] [ RETURNING * | output_expression [ AS output_name ] [, ...] ] --- 22,28 ---- DELETE FROM [ ONLY ] table [ [ AS ] alias ] [ USING usinglist ] ! [ [ WHERE condition ] | [ WHERE CURRENT OF cursor_name ] ] [ RETURNING * | output_expression [ AS output_name ] [, ...] ] *************** *** 162,167 **** --- 162,176 ---- + + + cursor_name + + + The name of the cursor to use with WHERE CURRENT OF condition. + + + *************** *** 238,243 **** --- 247,259 ---- DELETE FROM tasks WHERE status = 'DONE' RETURNING *; + + + Delete the task, on which the cursor c_tasks is pointing currently: + + DELETE FROM tasks WHERE CURRENT OF c_tasks; + + *** /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 **** FOR READ ONLY indicates that the cursor will be used in a read-only mode. FOR UPDATE ! indicates that the cursor will be used to update tables. Since ! cursor updates are not currently supported in ! PostgreSQL, specifying FOR ! UPDATE will cause an error message and specifying ! FOR READ ONLY has no effect. --- 171,177 ---- FOR READ ONLY indicates that the cursor will be used in a read-only mode. FOR UPDATE ! indicates that the cursor will be used to update tables. *************** *** 184,191 **** column ! Column(s) to be updated by the cursor. Since cursor updates are ! not currently supported in PostgreSQL, the FOR UPDATE clause provokes an error message. --- 180,187 ---- column ! Column(s) to be updated by the cursor. Since cursor updates on column(s) ! are not currently supported in PostgreSQL, the FOR UPDATE clause provokes an error message. *************** *** 286,296 **** - The SQL standard allows cursors to update table data. All - PostgreSQL cursors are read only. - - - Binary cursors are a PostgreSQL extension. --- 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 { column = { expression | DEFAULT } | ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...] [ FROM fromlist ] ! [ WHERE condition ] [ RETURNING * | output_expression [ AS output_name ] [, ...] ] --- 24,30 ---- SET { column = { expression | DEFAULT } | ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...] [ FROM fromlist ] ! [ [ WHERE condition ] | [ WHERE CURRENT OF cursor_name ] ] [ RETURNING * | output_expression [ AS output_name ] [, ...] ] *************** *** 181,186 **** --- 181,195 ---- + + + cursor_name + + + The name of the cursor to use with WHERE CURRENT OF condition. + + + *************** *** 244,249 **** --- 253,269 ---- + + Change the word to Dramatic in the + column kind of the table films, + on the row on which the cursor c_tasks is positioned currently: + + + UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_tasks; + + + + Adjust temperature entries and reset precipitation to its default value in one row of the table weather: