diff options
22 files changed, 2097 insertions, 718 deletions
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index ed08015d9c..00d12e05e1 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -80,6 +80,8 @@ #include "catalog/pgxc_class.h" #include "catalog/pgxc_node.h" #include "pgxc/locator.h" +#include "pgxc/pgxc.h" +#include "pgxc/postgresql_fdw.h" #endif @@ -2729,28 +2731,6 @@ cookDefault(ParseState *pstate, Assert(raw_default != NULL); -#ifdef PGXC - /* - * Block use of non-immutable functions as DEFAULT. - * - * Support of nextval(), currval(), now() as DEFAULT value in XC needs a special support - * like SERIAL, so block it for the time being - * - * PGXCTODO: As possible implementation, a constraint with non-immutable functions - * is just created on Coordinator and when an INSERT query needs a default value - * Coordinator feeds it, rewrite the query, and distributes it to Datanodes - * - * Sequence (currval, nextval) and timestamp values (now()...) have - * to be taken from GTM. - */ - if (IsA(raw_default,FuncCall)) - if (!IsFuncImmutable(pstate, (FuncCall *) raw_default)) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("Postgres-XC does not support DEFAULT with non-immutable functions yet"), - errdetail("The feature is not currently supported"))); -#endif - /* * Transform raw parsetree to executable expression. */ diff --git a/src/backend/executor/execCurrent.c b/src/backend/executor/execCurrent.c index 5312d9aeec..0f8bd18f2f 100644 --- a/src/backend/executor/execCurrent.c +++ b/src/backend/executor/execCurrent.c @@ -19,10 +19,15 @@ #include "utils/lsyscache.h" #include "utils/portal.h" +#ifdef PGXC +#include "pgxc/execRemote.h" +#endif static char *fetch_cursor_param_value(ExprContext *econtext, int paramId); -static ScanState *search_plan_tree(PlanState *node, Oid table_oid); +#ifndef PGXC +static ScanState *search_plan_tree(PlanState *node, Oid table_oid); +#endif /* * execCurrentOf @@ -249,13 +254,26 @@ fetch_cursor_param_value(ExprContext *econtext, int paramId) * Search through a PlanState tree for a scan node on the specified table. * Return NULL if not found or multiple candidates. */ +#ifdef PGXC +ScanState * +search_plan_tree(PlanState *node, Oid table_oid) +#else static ScanState * search_plan_tree(PlanState *node, Oid table_oid) +#endif { if (node == NULL) return NULL; switch (nodeTag(node)) { +#ifdef PGXC + case T_RemoteQueryState: + { + RemoteQueryState *rqs = (RemoteQueryState *) node; + ScanState *sstate = &(rqs->ss); + return sstate; + } +#endif /* * scan nodes can all be treated alike */ @@ -322,6 +340,9 @@ search_plan_tree(PlanState *node, Oid table_oid) * Result and Limit can be descended through (these are safe * because they always return their input's current row) */ +#ifdef PGXC + case T_MaterialState: +#endif case T_ResultState: case T_LimitState: return search_plan_tree(node->lefttree, table_oid); diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index bc73bc2c2b..fe66137280 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -1248,29 +1248,6 @@ transformFuncCall(ParseState *pstate, FuncCall *fn) fn->location); } -#ifdef PGXC -/* - * IsFuncImmutable - * - * Check if given function is immutable or not - * based on the function name and on its arguments - */ -bool -IsFuncImmutable(ParseState *pstate, FuncCall *fn) -{ - ListCell *args; - List *targs = NIL; - - /* Transform list of arguments */ - foreach(args, fn->args) - { - targs = lappend(targs, transformExpr(pstate, - (Node *) lfirst(args))); - } - - return IsParseFuncImmutable(pstate, targs, fn->funcname, fn->func_variadic); -} -#endif static Node * transformCaseExpr(ParseState *pstate, CaseExpr *c) @@ -2018,12 +1995,6 @@ transformCurrentOfExpr(ParseState *pstate, CurrentOfExpr *cexpr) { int sublevels_up; -#ifdef PGXC - ereport(ERROR, - (errcode(ERRCODE_STATEMENT_TOO_COMPLEX), - (errmsg("WHERE CURRENT OF clause not yet supported")))); -#endif - /* CURRENT OF can only appear at top level of UPDATE/DELETE */ Assert(pstate->p_target_rangetblentry != NULL); cexpr->cvarno = RTERangeTablePosn(pstate, diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index cfa3f5179f..75f1e20475 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -1619,100 +1619,3 @@ LookupAggNameTypeNames(List *aggname, List *argtypes, bool noError) return oid; } - - -#ifdef PGXC -/* - * IsParseFuncImmutable - * - * Check if given function is immutable or not - * based on the function name and on its arguments - * This functionnality will be extended to support functions in constraints - */ -bool -IsParseFuncImmutable(ParseState *pstate, List *targs, List *funcname, bool func_variadic) -{ - ListCell *l; - ListCell *nextl; - FuncDetailCode fdresult; - Oid actual_arg_types[FUNC_MAX_ARGS]; - List *argnames; - int nargs; - /* Return results */ - Oid funcid, rettype; - Oid *declared_arg_types; - bool retset; - int nvargs; - List *argdefaults; - - /* Get detailed argument information */ - nargs = 0; - for (l = list_head(targs); l != NULL; l = nextl) - { - Node *arg = lfirst(l); - Oid argtype = exprType(arg); - - nextl = lnext(l); - - if (argtype == VOIDOID && IsA(arg, Param)) - { - targs = list_delete_ptr(targs, arg); - continue; - } - actual_arg_types[nargs++] = argtype; - } - argnames = NIL; - - foreach(l, targs) - { - Node *arg = lfirst(l); - - if (IsA(arg, NamedArgExpr)) - { - NamedArgExpr *na = (NamedArgExpr *) arg; - ListCell *lc; - - /* Reject duplicate arg names */ - foreach(lc, argnames) - { - if (strcmp(na->name, (char *) lfirst(lc)) == 0) - ereport(ERROR, - (errcode(ERRCODE_SYNTAX_ERROR), - errmsg("argument name \"%s\" used more than once", - na->name), - parser_errposition(pstate, na->location))); - } - argnames = lappend(argnames, na->name); - } - else - { - if (argnames != NIL) - ereport(ERROR, - (errcode(ERRCODE_SYNTAX_ERROR), - errmsg("positional argument cannot follow named argument"), - parser_errposition(pstate, exprLocation(arg)))); - } - } - - fdresult = func_get_detail(funcname, - targs, - argnames, - nargs, - actual_arg_types, - !func_variadic, - true, - &funcid, &rettype, &retset, &nvargs, - &declared_arg_types, &argdefaults); - - /* - * Now only the function ID is used to check if function is immutable or not, - * but for function support in DEFAULT values, this function can be easily extended - * for other analysis purposes. - */ - if (func_volatile(funcid) == PROVOLATILE_IMMUTABLE) - return true; - else - return false; -} -#endif - diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c index 3bc4bd2f2c..bb7c3e2ded 100644 --- a/src/backend/parser/parse_relation.c +++ b/src/backend/parser/parse_relation.c @@ -46,8 +46,9 @@ static void expandTupleDesc(TupleDesc tupdesc, Alias *eref, int rtindex, int sublevels_up, int location, bool include_dropped, List **colnames, List **colvars); +#ifndef PGXC static int specialAttNum(const char *attname); - +#endif /* * refnameRangeTblEntry @@ -2363,8 +2364,13 @@ attnameAttNum(Relation rd, const char *attname, bool sysColOK) * Caller needs to verify that it really is an attribute of the rel, * at least in the case of "oid", which is now optional. */ +#ifdef PGXC +int +specialAttNum(const char *attname) +#else static int specialAttNum(const char *attname) +#endif { Form_pg_attribute sysatt; diff --git a/src/backend/pgxc/plan/planner.c b/src/backend/pgxc/plan/planner.c index 277462a253..5a21bb273d 100644 --- a/src/backend/pgxc/plan/planner.c +++ b/src/backend/pgxc/plan/planner.c @@ -20,6 +20,7 @@ #include "catalog/pg_namespace.h" #include "catalog/pg_proc.h" #include "catalog/pg_type.h" +#include "catalog/pgxc_node.h" #include "executor/executor.h" #include "lib/stringinfo.h" #include "nodes/makefuncs.h" @@ -32,6 +33,10 @@ #include "optimizer/tlist.h" #include "parser/parse_agg.h" #include "parser/parse_coerce.h" +#include "parser/parse_func.h" +#include "parser/parse_relation.h" +#include "parser/parsetree.h" +#include "parser/parse_oper.h" #include "pgxc/execRemote.h" #include "pgxc/pgxc.h" #include "pgxc/locator.h" @@ -46,6 +51,7 @@ #include "utils/portal.h" #include "utils/syscache.h" #include "utils/numeric.h" +#include "utils/memutils.h" #include "access/hash.h" #include "commands/tablecmds.h" #include "utils/timestamp.h" @@ -144,18 +150,18 @@ typedef struct ColumnBase */ typedef struct XCWalkerContext { - Query *query; - RelationAccessType accessType; - RemoteQuery *query_step; /* remote query step being analized */ - PlannerInfo *root; /* planner data for the subquery */ - Special_Conditions *conditions; - bool multilevel_join; - List *rtables; /* a pointer to a list of rtables */ - int varno; - bool within_or; - bool within_not; - bool exec_on_coord; /* fallback to standard planner to have plan executed on coordinator only */ - List *join_list; /* A list of List*'s, one for each relation. */ + Query *query; + RelationAccessType accessType; + RemoteQuery *query_step; /* remote query step being analized */ + PlannerInfo *root; /* planner data for the subquery */ + Special_Conditions *conditions; + bool multilevel_join; + List *rtables; /* a pointer to a list of rtables */ + int varno; + bool within_or; + bool within_not; + bool exec_on_coord; /* fallback to standard planner to have plan executed on coordinator only */ + List *join_list; /* A list of List*'s, one for each relation. */ } XCWalkerContext; @@ -554,6 +560,38 @@ get_plan_nodes_insert(PlannerInfo *root, RemoteQuery *step) (errcode(ERRCODE_STATEMENT_TOO_COMPLEX), (errmsg("Could not find relation for oid = %d", rte->relid)))); + /* + * Evaluate expressions in target list before trying any optimisations. + * The following flow is respected depending on table distribution, + * target column (distribution column or not) and expression shippability. + * For a hash-distributed table: + * - Non-shippable expression whatever the target column, return exec_nodes + * as NULL and go through standard planner + * - Shippable expression on a distribution column, go through optimization + * In this case if target column is distributable, node is determined + * from expression if constant can be obtained. An expression can be used + * also to determine a safe node list a execution time. + * For replicated or round robin tables (no distribution column): + * - Non-shippable expression, return exec_nodes as NULL and go through + * standard planner + * - Shippable expression, go through the optimization process + * PGXCTODO: for the time being query goes through standard planner if at least + * one non-shippable expression is found, we should be able to partially push + * down foreign expressions. + */ + foreach(lc, query->targetList) + { + TargetEntry *tle = (TargetEntry *) lfirst(lc); + Expr *expr = tle->expr; + + /* If expression is not shippable, go through standard planner */ + if (!is_foreign_expr((Node *) expr, NULL)) + { + step->exec_nodes = NULL; + return; + } + } + /* Optimization is only done for distributed tables */ if (query->jointree != NULL && query->jointree->fromlist != NULL @@ -598,10 +636,13 @@ get_plan_nodes_insert(PlannerInfo *root, RemoteQuery *step) */ } - - if ( (rel_loc_info->partAttrName != NULL) && - ( (rel_loc_info->locatorType == LOCATOR_TYPE_HASH) || - (rel_loc_info->locatorType == LOCATOR_TYPE_MODULO) )) + /* + * Search for an expression value that can be used for + * distribute table optimisation. + */ + if ((rel_loc_info->partAttrName != NULL) && + ((rel_loc_info->locatorType == LOCATOR_TYPE_HASH) || + (rel_loc_info->locatorType == LOCATOR_TYPE_MODULO))) { Expr *checkexpr; TargetEntry *tle = NULL; @@ -661,14 +702,11 @@ get_plan_nodes_insert(PlannerInfo *root, RemoteQuery *step) (errcode(ERRCODE_STATEMENT_TOO_COMPLEX), (errmsg("Could not find relation for oid = %d", rte->relid)))); - if ( col_base->colname != NULL && + if (col_base->colname != NULL && source_rel_loc_info->partAttrName != NULL && strcmp(col_base->colname, source_rel_loc_info->partAttrName) == 0 && - ( - source_rel_loc_info->locatorType == LOCATOR_TYPE_HASH || - source_rel_loc_info->locatorType == LOCATOR_TYPE_MODULO - ) - ) + (source_rel_loc_info->locatorType == LOCATOR_TYPE_HASH || + source_rel_loc_info->locatorType == LOCATOR_TYPE_MODULO)) { /* * Partition columns match, we have a "single-step INSERT SELECT". @@ -718,310 +756,398 @@ get_plan_nodes_insert(PlannerInfo *root, RemoteQuery *step) pfree(eval_expr); } - /* - * examine_conditions_walker - * - * Examine conditions and find special ones to later help us determine - * what tables can be joined together. Put findings in Special_Conditions - * struct. + * make_ctid_col_ref * - * Get list of constant comparisons conditions on partitioned column - * Get list of parent-child joins (partitioned together) - * Get list of joins with replicated tables + * creates a Var for a column referring to ctid + */ + +static Var * +make_ctid_col_ref(Query *qry) +{ + ListCell *lc1, *lc2; + RangeTblEntry *rte1, *rte2; + int tableRTEs, firstTableRTENumber; + RangeTblEntry *rte_in_query; + AttrNumber attnum; + Oid vartypeid; + int32 type_mod; + Oid varcollid; + + /* If the query has more than 1 table RTEs where both are different, we can not add ctid to the query target list + * We should in this case skip adding it to the target list and a WHERE CURRENT OF should then + * fail saying the query is not a simply update able scan of table + */ + + tableRTEs = 0; + foreach(lc1, qry->rtable) + { + rte1 = (RangeTblEntry *) lfirst(lc1); + + if (rte1->rtekind == RTE_RELATION) + { + tableRTEs++; + if (tableRTEs > 1) + { + /* See if we get two RTEs in case we have two references + * to the same table with different aliases + */ + foreach(lc2, qry->rtable) + { + rte2 = (RangeTblEntry *) lfirst(lc2); + + if (rte2->rtekind == RTE_RELATION) + { + if (rte2->relid != rte1->relid) + { + return NULL; + } + } + } + continue; + } + rte_in_query = rte1; + } + } + + if (tableRTEs > 1) + { + firstTableRTENumber = 0; + foreach(lc1, qry->rtable) + { + rte1 = (RangeTblEntry *) lfirst(lc1); + firstTableRTENumber++; + if (rte1->rtekind == RTE_RELATION) + { + break; + } + } + } + else + { + firstTableRTENumber = 1; + } + + attnum = specialAttNum("ctid"); + get_rte_attribute_type(rte_in_query, attnum, &vartypeid, &type_mod, &varcollid); + return makeVar(firstTableRTENumber, attnum, vartypeid, type_mod, varcollid, 0); +} + +/* + * make_ctid_const * - * If we encounter an expression such as a cross-node join that cannot - * be easily handled in a single step, we stop processing and return true, - * otherwise false. + * creates a Const expression representing a ctid value (?,?) + */ + +static Const * +make_ctid_const(char *ctid_string) +{ + Datum val; + Const *ctid_const; + + val = PointerGetDatum(ctid_string); + + ctid_const = makeConst(UNKNOWNOID, + -1, + InvalidOid, + -2, + val, + false, + false); + ctid_const->location = -1; + return ctid_const; +} + +/* + * IsRelSame * + * Does the two query trees have a common relation */ + static bool -examine_conditions_walker(Node *expr_node, XCWalkerContext *context) +IsRelSame(List *upd_qry_rte, List *sel_qry_rte) { - RelationLocInfo *rel_loc_info1, - *rel_loc_info2; - Const *constant; - Expr *checkexpr; - bool result = false; - bool is_and = false; + ListCell *lc1, *lc2; + RangeTblEntry *rte1, *rte2; - Assert(context); + foreach(lc1, upd_qry_rte) + { + rte1 = (RangeTblEntry *) lfirst(lc1); - if (expr_node == NULL) - return false; + if (rte1->rtekind == RTE_RELATION) + { + foreach(lc2, sel_qry_rte) + { + rte2 = (RangeTblEntry *) lfirst(lc2); + + if (rte2->rtekind == RTE_RELATION) + { + if (rte2->relid == rte1->relid) + { + return true; + } + } + } + } + } + return false; +} - if (!context->rtables) - return true; +/* + * pgxc_handle_current_of + * + * Handles UPDATE/DELETE WHERE CURRENT OF + */ - if (!context->conditions) - context->conditions = new_special_conditions(); +static bool +pgxc_handle_current_of(Node *expr_node, XCWalkerContext *context) +{ + /* Find referenced portal and figure out what was the last fetch node */ + Portal portal; + QueryDesc *queryDesc; + CurrentOfExpr *cexpr = (CurrentOfExpr *) expr_node; + char *cursor_name = cexpr->cursor_name; + PlanState *ps; + TupleTableSlot *slot; + RangeTblEntry *table = (RangeTblEntry *) linitial(context->query->rtable); + ScanState *ss; + + /* Find the cursor's portal */ + portal = GetPortalByName(cursor_name); + if (!PortalIsValid(portal)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_CURSOR), + errmsg("cursor \"%s\" does not exist", cursor_name))); - /* Handle UPDATE/DELETE ... WHERE CURRENT OF ... */ - if (IsA(expr_node, CurrentOfExpr)) - { - /* Find referenced portal and figure out what was the last fetch node */ - Portal portal; - QueryDesc *queryDesc; - CurrentOfExpr *cexpr = (CurrentOfExpr *) expr_node; - char *cursor_name = cexpr->cursor_name; - char *node_cursor; + queryDesc = PortalGetQueryDesc(portal); + if (queryDesc == NULL || queryDesc->estate == NULL) + ereport(ERROR, + (errcode(ERRCODE_INVALID_CURSOR_STATE), + errmsg("cursor \"%s\" is held from a previous transaction", + cursor_name))); - /* Find the cursor's portal */ - portal = GetPortalByName(cursor_name); - if (!PortalIsValid(portal)) - ereport(ERROR, - (errcode(ERRCODE_UNDEFINED_CURSOR), - errmsg("cursor \"%s\" does not exist", cursor_name))); + /* + * The cursor must have a current result row: per the SQL spec, it's + * an error if not. + */ + if (portal->atStart || portal->atEnd) + ereport(ERROR, + (errcode(ERRCODE_INVALID_CURSOR_STATE), + errmsg("cursor \"%s\" is not positioned on a row", + cursor_name))); - queryDesc = PortalGetQueryDesc(portal); - if (queryDesc == NULL || queryDesc->estate == NULL) - ereport(ERROR, - (errcode(ERRCODE_INVALID_CURSOR_STATE), - errmsg("cursor \"%s\" is held from a previous transaction", - cursor_name))); + ps = queryDesc->planstate; + slot = NULL; - /* - * The cursor must have a current result row: per the SQL spec, it's - * an error if not. - */ - if (portal->atStart || portal->atEnd) - ereport(ERROR, - (errcode(ERRCODE_INVALID_CURSOR_STATE), - errmsg("cursor \"%s\" is not positioned on a row", - cursor_name))); + ss = search_plan_tree(ps, table->relid); + if (ss != NULL) + { + slot = ss->ss_ScanTupleSlot; + } - if (IsA(queryDesc->planstate, RemoteQueryState)) + if (slot != NULL) + { + MemoryContext oldcontext; + MemoryContext tmpcontext; + RelationLocInfo *loc_info; + + tmpcontext = AllocSetContextCreate(CurrentMemoryContext, + "Temp Context", + ALLOCSET_DEFAULT_MINSIZE, + ALLOCSET_DEFAULT_INITSIZE, + ALLOCSET_DEFAULT_MAXSIZE); + oldcontext = MemoryContextSwitchTo(tmpcontext); + + loc_info = GetRelationLocInfo(table->relid); + if (!loc_info) { - RemoteQueryState *node = (RemoteQueryState *) queryDesc->planstate; - RemoteQuery *step = (RemoteQuery *) queryDesc->planstate->plan; - - /* - * 1. step query: SELECT * FROM <table> WHERE ctid = <cur_ctid>, - * <cur_ctid> is taken from the scantuple ot the target step - * step node list: current node of the target step. - * 2. step query: DECLARE <xxx> CURSOR FOR SELECT * FROM <table> - * WHERE <col1> = <val1> AND <col2> = <val2> ... FOR UPDATE - * <xxx> is generated from cursor name of the target step, - * <col> and <val> pairs are taken from the step 1. - * step node list: all nodes of <table> - * 3. step query: MOVE <xxx> - * step node list: all nodes of <table> - */ - RangeTblEntry *table = (RangeTblEntry *) linitial(context->query->rtable); - node_cursor = step->cursor; - rel_loc_info1 = GetRelationLocInfo(table->relid); - if (!rel_loc_info1) - return true; + MemoryContextSwitchTo(oldcontext); + MemoryContextDelete(tmpcontext); + return true; + } - context->query_step->exec_nodes = makeNode(ExecNodes); - context->query_step->exec_nodes->tableusagetype = TABLE_USAGE_TYPE_USER; - context->query_step->exec_nodes->baselocatortype = rel_loc_info1->locatorType; - if (rel_loc_info1->locatorType == LOCATOR_TYPE_REPLICATED) + switch (loc_info->locatorType) + { + case LOCATOR_TYPE_HASH: + case LOCATOR_TYPE_RROBIN: + case LOCATOR_TYPE_MODULO: { - RemoteQuery *step1, *step2, *step3; - /* - * We do not need first three steps if cursor already exists and - * positioned. - */ - if (node->update_cursor) + Query *temp_qry; + Var *ctid_expr; + bool ctid_found, node_str_found; + StringInfoData qry; + TupleDesc slot_meta = slot->tts_tupleDescriptor; + char *ctid_str = NULL; + int node_index = -1; + int i; + Const *cons_ctid; + + /* make a copy of the query so as not to touch the original query tree */ + temp_qry = copyObject(context->query); + + /* Make sure the relation referenced in cursor query and UPDATE/DELETE query is the same */ + if ( ! IsRelSame(temp_qry->rtable, queryDesc->plannedstmt->rtable ) ) { - step3 = NULL; - node_cursor = node->update_cursor; + char *tableName = get_rel_name(table->relid); + ereport(ERROR, + (errcode(ERRCODE_INVALID_CURSOR_STATE), + errmsg("cursor \"%s\" does not have a FOR UPDATE/SHARE reference to table \"%s\"", + cursor_name, tableName))); } - else + + /* Delete existing WHERE CURRENT OF qual from the query tree*/ + pfree(((CurrentOfExpr *)(temp_qry->jointree->quals))->cursor_name); + pfree((CurrentOfExpr *)temp_qry->jointree->quals); + + /* Make a ctid column ref expr for LHS of the operator */ + ctid_expr = make_ctid_col_ref(temp_qry); + if (ctid_expr == NULL) { - char *tableName = get_rel_name(table->relid); - int natts = get_relnatts(table->relid); - char *attnames[natts]; - TupleTableSlot *slot = node->ss.ss_ScanTupleSlot; - TupleDesc slot_meta = slot->tts_tupleDescriptor; - Datum ctid = 0; - char *ctid_str = NULL; - int nindex = slot->tts_dataNodeIndex; - AttrNumber att; - StringInfoData buf; - HeapTuple tp; - int i; - MemoryContext context_save; + MemoryContextSwitchTo(oldcontext); + MemoryContextDelete(tmpcontext); + return true; /* Bail out */ + } - /* - * Iterate over attributes and find CTID. This attribute is - * most likely at the end of the list, so iterate in - * reverse order to find it quickly. - * If not found, target table is not updatable through - * the cursor, report problem to client - */ - for (i = slot_meta->natts - 1; i >= 0; i--) + /* + * Iterate over attributes and find ctid and node index. + * These attributes are most likely at the end of the list, + * so iterate in reverse order to find them quickly. + * If not found target table is not updatable through + * the cursor, report problem to client + */ + ctid_found = false; + node_str_found = false; + for (i = slot_meta->natts - 1; i >= 0; i--) + { + Form_pg_attribute attr = slot_meta->attrs[i]; + + if (ctid_found == false) { - Form_pg_attribute attr = slot_meta->attrs[i]; if (strcmp(attr->attname.data, "ctid") == 0) { + Datum ctid = 0; + ctid = slot->tts_values[i]; ctid_str = (char *) DirectFunctionCall1(tidout, ctid); - break; + ctid_found = true; } } - - if (ctid_str == NULL) - ereport(ERROR, - (errcode(ERRCODE_INVALID_CURSOR_STATE), - errmsg("cursor \"%s\" is not a simply updatable scan of table \"%s\"", - cexpr->cursor_name, tableName))); - - initStringInfo(&buf); - - /* Step 1: select tuple values by ctid */ - step1 = makeRemoteQuery(); - appendStringInfoString(&buf, "SELECT "); - for (att = 1; att <= natts; att++) + if (node_str_found == false) { - TargetEntry *tle; - Var *expr; - - tp = SearchSysCache(ATTNUM, - ObjectIdGetDatum(table->relid), - Int16GetDatum(att), - 0, 0); - if (HeapTupleIsValid(tp)) + if (strcmp(attr->attname.data, "pgxc_node_str") == 0) { - Form_pg_attribute att_tup = (Form_pg_attribute) GETSTRUCT(tp); - - /* add comma before all except first attributes */ - if (att > 1) - appendStringInfoString(&buf, ", "); - attnames[att-1] = pstrdup(NameStr(att_tup->attname)); - appendStringInfoString(&buf, attnames[att - 1]); - expr = makeVar(att, att, att_tup->atttypid, - att_tup->atttypmod, InvalidOid, 0); - tle = makeTargetEntry((Expr *) expr, att, - attnames[att - 1], false); - step1->scan.plan.targetlist = lappend(step1->scan.plan.targetlist, tle); - ReleaseSysCache(tp); + Datum data_node = 0; + char *data_node_str = NULL; + + data_node = slot->tts_values[i]; + data_node_str = (char *) DirectFunctionCall1(nameout, data_node); + node_index = PGXCNodeGetNodeIdFromName(data_node_str, PGXC_NODE_DATANODE); + node_str_found = true; } - else - elog(ERROR, "cache lookup failed for attribute %d of relation %u", - att, table->relid); - } - appendStringInfo(&buf, " FROM %s WHERE ctid = '%s'", - tableName, ctid_str); - step1->sql_statement = pstrdup(buf.data); - step1->exec_nodes = makeNode(ExecNodes); - step1->exec_nodes->nodeList = list_make1_int(nindex); - - /* Step 2: declare cursor for update target table */ - step2 = makeRemoteQuery(); - resetStringInfo(&buf); - - appendStringInfoString(&buf, step->cursor); - appendStringInfoString(&buf, "upd"); - /* This need to survive while the target Portal is alive */ - context_save = MemoryContextSwitchTo(PortalGetHeapMemory(portal)); - node_cursor = pstrdup(buf.data); - node->update_cursor = node_cursor; - MemoryContextSwitchTo(context_save); - resetStringInfo(&buf); - - appendStringInfo(&buf, - "DECLARE %s CURSOR FOR SELECT * FROM %s WHERE ", - node_cursor, tableName); - for (i = 0; i < natts; i++) - { - /* add comma before all except first attributes */ - if (i) - appendStringInfoString(&buf, "AND "); - appendStringInfo(&buf, "%s = $%d ", attnames[i], i+1); } - appendStringInfoString(&buf, "FOR UPDATE"); - step2->sql_statement = pstrdup(buf.data); - step2->exec_nodes = makeNode(ExecNodes); + if (ctid_found && node_str_found) + break; + } - step2->exec_nodes->nodeList = list_copy(rel_loc_info1->nodeList); + if (ctid_str == NULL || node_index < 0) + { + char *tableName = get_rel_name(table->relid); + ereport(ERROR, + (errcode(ERRCODE_INVALID_CURSOR_STATE), + errmsg("cursor \"%s\" is not a simply updatable scan of table \"%s\"", + cursor_name, tableName))); + } - innerPlan(step2) = (Plan *) step1; - /* Step 3: move cursor to first position */ - step3 = makeRemoteQuery(); - resetStringInfo(&buf); - appendStringInfo(&buf, "MOVE %s", node_cursor); - step3->sql_statement = pstrdup(buf.data); - step3->exec_nodes = makeNode(ExecNodes); + /* Make the ctid value constant expr for RHS of the operator */ + cons_ctid = make_ctid_const(ctid_str); - step3->exec_nodes->nodeList = list_copy(rel_loc_info1->nodeList); + /* Make the new qual ctid = (?,?) */ + temp_qry->jointree->quals = (Node *)make_op(NULL, list_make1(makeString("=")), (Node *)ctid_expr, (Node *)cons_ctid, -1); - innerPlan(step3) = (Plan *) step2; + /* Now deparse the query tree */ + initStringInfo(&qry); + deparse_query(temp_qry, &qry, NIL); - innerPlan(context->query_step) = (Plan *) step3; + MemoryContextSwitchTo(oldcontext); - pfree(buf.data); - } + if ( context->query_step->sql_statement != NULL ) + pfree(context->query_step->sql_statement); + context->query_step->sql_statement = pstrdup(qry.data); - context->query_step->exec_nodes->nodeList = list_copy(rel_loc_info1->nodeList); - } - else - { - /* Take target node from last scan tuple of referenced step */ - context->query_step->exec_nodes->nodeList = lappend_int(context->query_step->exec_nodes->nodeList, - node->ss.ss_ScanTupleSlot->tts_dataNodeIndex); - } - FreeRelationLocInfo(rel_loc_info1); + MemoryContextDelete(tmpcontext); - /* - * replace cursor name in the query if differs - */ - if (strcmp(cursor_name, node_cursor)) - { - StringInfoData buf; - char *str = context->query->sql_statement; - /* - * Find last occurence of cursor_name - */ - for (;;) - { - char *next = strstr(str + 1, cursor_name); - if (next) - str = next; - else - break; - } + context->query_step->exec_nodes = makeNode(ExecNodes); + context->query_step->exec_nodes->nodeList = list_make1_int(node_index); + context->query_step->read_only = false; + context->query_step->force_autocommit = false; - /* - * now str points to cursor name truncate string here - * do not care the string is modified - we will pfree it - * soon anyway - */ - *str = '\0'; - - /* and move str at the beginning of the reminder */ - str += strlen(cursor_name); + return false; + } + case LOCATOR_TYPE_REPLICATED: + MemoryContextSwitchTo(oldcontext); + MemoryContextDelete(tmpcontext); - /* build up new statement */ - initStringInfo(&buf); - appendStringInfoString(&buf, context->query->sql_statement); - appendStringInfoString(&buf, node_cursor); - appendStringInfoString(&buf, str); + return false; - /* take the result */ - pfree(context->query->sql_statement); - context->query->sql_statement = buf.data; - } - return false; + default: + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("the distribution type is not supported"))); + return false; // or true } + } + else + { + char *tableName = get_rel_name(table->relid); + ereport(ERROR, + (errcode(ERRCODE_INVALID_CURSOR_STATE), + errmsg("cursor \"%s\" is not a simply updatable scan of table \"%s\"", + cursor_name, tableName))); + } + return false; // or true +} - /* Even with a catalog table EXECUTE direct in launched on dedicated nodes */ - if (context->query_step->exec_direct_type == EXEC_DIRECT_LOCAL - || context->query_step->exec_direct_type == EXEC_DIRECT_NONE - || context->query_step->exec_direct_type == EXEC_DIRECT_LOCAL_UTILITY) - { - context->query_step->exec_nodes = makeNode(ExecNodes); - context->query_step->exec_nodes->tableusagetype = TABLE_USAGE_TYPE_PGCATALOG; - context->exec_on_coord = true; - } - else - { - context->query_step->exec_nodes->tableusagetype = TABLE_USAGE_TYPE_USER; - context->exec_on_coord = false; - } +/* + * examine_conditions_walker + * + * Examine conditions and find special ones to later help us determine + * what tables can be joined together. Put findings in Special_Conditions + * struct. + * + * Get list of constant comparisons conditions on partitioned column + * Get list of parent-child joins (partitioned together) + * Get list of joins with replicated tables + * + * If we encounter an expression such as a cross-node join that cannot + * be easily handled in a single step, we stop processing and return true, + * otherwise false. + * + */ +static bool +examine_conditions_walker(Node *expr_node, XCWalkerContext *context) +{ + RelationLocInfo *rel_loc_info1, + *rel_loc_info2; + Const *constant; + Expr *checkexpr; + bool result = false; + bool is_and = false; + + Assert(context); + if (expr_node == NULL) return false; + + if (!context->rtables) + return true; + + if (!context->conditions) + context->conditions = new_special_conditions(); + + /* Handle UPDATE/DELETE ... WHERE CURRENT OF ... */ + if (IsA(expr_node, CurrentOfExpr)) + { + return pgxc_handle_current_of(expr_node, context); } if (IsA(expr_node, Var)) @@ -2208,6 +2334,7 @@ makeRemoteQuery(void) result->inner_statement = NULL; result->outer_statement = NULL; result->join_condition = NULL; + result->sql_statement = NULL; return result; } @@ -2519,41 +2646,134 @@ set_cursor_name(Plan *subtree, char *cursor, int step_no) return step_no; } + +/* + * get oid of the function whose name is passed as argument + */ + +static Oid +get_fn_oid(char *fn_name, Oid *p_rettype) +{ + Value *fn_nm; + List *fn_name_list; + FuncDetailCode fdc; + bool retset; + int nvargs; + Oid *true_typeids; + Oid func_oid; + + fn_nm = makeString(fn_name); + fn_name_list = list_make1(fn_nm); + + fdc = func_get_detail(fn_name_list, + NULL, /* argument expressions */ + NULL, /* argument names */ + 0, /* argument numbers */ + NULL, /* argument types */ + false, /* expand variable number or args */ + false, /* expand defaults */ + &func_oid, /* oid of the function - returned detail*/ + p_rettype, /* function return type - returned detail */ + &retset, /* - returned detail*/ + &nvargs, /* - returned detail*/ + &true_typeids, /* - returned detail */ + NULL /* arguemnt defaults returned*/ + ); + + pfree(fn_name_list); + if (fdc == FUNCDETAIL_NORMAL) + { + return func_oid; + } + return InvalidOid; +} + /* * Append ctid to the field list of step queries to support update * WHERE CURRENT OF. The ctid is not sent down to client but used as a key * to find target tuple */ static void -fetch_ctid_of(Plan *subtree, RowMarkClause *rmc) +fetch_ctid_of(Plan *subtree, Query *query) { /* recursively process subnodes */ if (innerPlan(subtree)) - fetch_ctid_of(innerPlan(subtree), rmc); + fetch_ctid_of(innerPlan(subtree), query); if (outerPlan(subtree)) - fetch_ctid_of(outerPlan(subtree), rmc); + fetch_ctid_of(outerPlan(subtree), query); /* we are only interested in RemoteQueries */ if (IsA(subtree, RemoteQuery)) { - RemoteQuery *step = (RemoteQuery *) subtree; - /* - * TODO Find if the table is referenced by the step query - */ + RemoteQuery *step = (RemoteQuery *) subtree; + TargetEntry *te1; + Query *temp_qry; + FuncExpr *func_expr; + AttrNumber resno; + Oid funcid; + Oid rettype; + Var *ctid_expr; + MemoryContext oldcontext; + MemoryContext tmpcontext; + + tmpcontext = AllocSetContextCreate(CurrentMemoryContext, + "Temp Context", + ALLOCSET_DEFAULT_MINSIZE, + ALLOCSET_DEFAULT_INITSIZE, + ALLOCSET_DEFAULT_MAXSIZE); + oldcontext = MemoryContextSwitchTo(tmpcontext); + + /* Copy the query tree to make changes to the target list */ + temp_qry = copyObject(query); + /* Get the number of entries in the target list */ + resno = list_length(temp_qry->targetList); + + /* Make a ctid column ref expr to add in target list */ + ctid_expr = make_ctid_col_ref(temp_qry); + if (ctid_expr == NULL) + { + MemoryContextSwitchTo(oldcontext); + MemoryContextDelete(tmpcontext); + return; + } + + te1 = makeTargetEntry((Expr *)ctid_expr, resno+1, NULL, false); + + /* add the target entry to the query target list */ + temp_qry->targetList = lappend(temp_qry->targetList, te1); + + /* PGXCTODO We can take this call in initialization rather than getting it always */ - char *from_sql = strpos(step->sql_statement, " FROM ", 1); - if (from_sql) + /* Get the Oid of the function */ + funcid = get_fn_oid("pgxc_node_str", &rettype); + if (OidIsValid(funcid)) { - StringInfoData buf; + StringInfoData deparsed_qry; + TargetEntry *te2; - initStringInfo(&buf); - appendBinaryStringInfo(&buf, step->sql_statement, - (int) (from_sql - step->sql_statement)); - /* TODO qualify with the table name */ - appendStringInfoString(&buf, ", ctid"); - appendStringInfoString(&buf, from_sql); - pfree(step->sql_statement); - step->sql_statement = buf.data; + /* create a function expression */ + func_expr = makeFuncExpr(funcid, rettype, NULL, InvalidOid, InvalidOid, COERCE_DONTCARE); + /* make a target entry for function call */ + te2 = makeTargetEntry((Expr *)func_expr, resno+2, NULL, false); + /* add the target entry to the query target list */ + temp_qry->targetList = lappend(temp_qry->targetList, te2); + + initStringInfo(&deparsed_qry); + deparse_query(temp_qry, &deparsed_qry, NIL); + + MemoryContextSwitchTo(oldcontext); + + if (step->sql_statement != NULL) + pfree(step->sql_statement); + + step->sql_statement = pstrdup(deparsed_qry.data); + + MemoryContextDelete(tmpcontext); + } + else + { + MemoryContextSwitchTo(oldcontext); + MemoryContextDelete(tmpcontext); } } } @@ -3067,7 +3287,6 @@ pgxc_fqs_planner(Query *query, int cursorOptions, ParamListInfo boundParams) result->planTree = (Plan *) query_step; - /* Set result relations */ if (query->commandType != CMD_SELECT) result->resultRelations = list_make1_int(query->resultRelation); @@ -3079,7 +3298,6 @@ pgxc_fqs_planner(Query *query, int cursorOptions, ParamListInfo boundParams) if (query_step->exec_nodes == NULL) get_plan_nodes_command(query_step, root); - if (query_step->exec_nodes == NULL) { /* @@ -3088,17 +3306,19 @@ pgxc_fqs_planner(Query *query, int cursorOptions, ParamListInfo boundParams) */ return NULL; } - /* Datanodes should finalise the results of this query */ query->qry_finalise_aggs = true; /* * Deparse query tree to get step query. It may be modified later on */ - initStringInfo(&buf); - deparse_query(query, &buf, NIL); - query_step->sql_statement = pstrdup(buf.data); - pfree(buf.data); + if ( query_step->sql_statement == NULL ) + { + initStringInfo(&buf); + deparse_query(query, &buf, NIL); + query_step->sql_statement = pstrdup(buf.data); + pfree(buf.data); + } /* * PGXCTODO: we may route this same Query structure through * standard_planner, where we don't want datanodes to finalise the results. @@ -3106,7 +3326,6 @@ pgxc_fqs_planner(Query *query, int cursorOptions, ParamListInfo boundParams) * structure through the standard_planner */ query->qry_finalise_aggs = false; - /* * PGXCTODO * When Postgres runs insert into t (a) values (1); against table @@ -3125,7 +3344,6 @@ pgxc_fqs_planner(Query *query, int cursorOptions, ParamListInfo boundParams) if (query_step->exec_nodes) query_step->combine_type = get_plan_combine_type( query, query_step->exec_nodes->baselocatortype); - /* * Add sorting to the step */ @@ -3160,19 +3378,13 @@ pgxc_fqs_planner(Query *query, int cursorOptions, ParamListInfo boundParams) } /* - * If query is FOR UPDATE fetch CTIDs from the remote node - * Use CTID as a key to update tuples on remote nodes when handling + * If query is DECLARE CURSOR fetch CTIDs and node names from the remote node + * Use CTID as a key to update/delete tuples on remote nodes when handling * WHERE CURRENT OF */ - if (query->rowMarks) + if ( query->utilityStmt && IsA(query->utilityStmt, DeclareCursorStmt) ) { - ListCell *lc; - foreach(lc, query->rowMarks) - { - RowMarkClause *rmc = (RowMarkClause *) lfirst(lc); - - fetch_ctid_of(result->planTree, rmc); - } + fetch_ctid_of(result->planTree, query); } return result; diff --git a/src/backend/pgxc/pool/pgxcnode.c b/src/backend/pgxc/pool/pgxcnode.c index 6cf8d91b69..423928028c 100644 --- a/src/backend/pgxc/pool/pgxcnode.c +++ b/src/backend/pgxc/pool/pgxcnode.c @@ -46,6 +46,7 @@ #include "utils/fmgroids.h" #include "utils/syscache.h" #include "utils/lsyscache.h" +#include "utils/formatting.h" #include "../interfaces/libpq/libpq-fe.h" @@ -2276,3 +2277,38 @@ PGXCNodeGetNodeOid(int nodeid, char node_type) return handles[nodeid - 1].nodeoid; } + +/* + * pgxc_node_str + * + * get the name of the node + */ +Datum +pgxc_node_str(PG_FUNCTION_ARGS) +{ + PG_RETURN_NAME(PGXCNodeName); +} + +/* + * PGXCNodeGetNodeIdFromName + * Return node position in handles array + */ +int +PGXCNodeGetNodeIdFromName(char *node_name, char node_type) +{ + char *nm; + Oid nodeoid; + + if (node_name == NULL) + return -1; + + nm = str_tolower(node_name, strlen(node_name), DEFAULT_COLLATION_OID); + + nodeoid = get_pgxc_nodeoid(nm); + pfree(nm); + if (!OidIsValid(nodeoid)) + return -1; + + return PGXCNodeGetNodeId(nodeoid, node_type); +} + diff --git a/src/backend/pgxc/pool/postgresql_fdw.c b/src/backend/pgxc/pool/postgresql_fdw.c index 0a42426ab0..2aef35632b 100644 --- a/src/backend/pgxc/pool/postgresql_fdw.c +++ b/src/backend/pgxc/pool/postgresql_fdw.c @@ -20,6 +20,7 @@ #include "nodes/nodeFuncs.h" #include "nodes/makefuncs.h" #include "optimizer/clauses.h" +#include "optimizer/planmain.h" #include "parser/scansup.h" #include "utils/builtins.h" #include "utils/lsyscache.h" @@ -143,7 +144,11 @@ foreign_qual_walker(Node *node, foreign_qual_context *context) * foreign server . It is not necessary to worry about oprrest * and oprjoin here because they are invoked by planner but not * executor. DistinctExpr is a typedef of OpExpr. + * We need also to be sure that function id is correctly set + * before evaluation. */ + set_opfuncid((OpExpr *) node); + if (!is_immutable_func(((OpExpr*) node)->opfuncid)) return true; break; diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index ee619b21dd..5194af279c 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -1258,8 +1258,7 @@ standard_ProcessUtility(Node *parsetree, /* Launch GRANT on Coordinator if object is a sequence */ if ((stmt->objtype == ACL_OBJECT_RELATION && - stmt->targtype == ACL_TARGET_OBJECT) || - stmt->objtype == ACL_OBJECT_SEQUENCE) + stmt->targtype == ACL_TARGET_OBJECT)) { /* * In case object is a relation, differenciate the case @@ -1289,7 +1288,7 @@ standard_ProcessUtility(Node *parsetree, ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("PGXC does not support GRANT on multiple object types"), - errdetail("Grant VIEW/SEQUENCE/TABLE with separate queries"))); + errdetail("Grant VIEW/TABLE with separate queries"))); } } } @@ -1510,14 +1509,14 @@ standard_ProcessUtility(Node *parsetree, #ifdef PGXC if (IS_PGXC_COORDINATOR) { - /* - * If sequence is temporary, no need to send this query to other - * remote Coordinators. - */ CreateSeqStmt *stmt = (CreateSeqStmt *) parsetree; + bool is_temp = stmt->sequence->relpersistence == RELPERSISTENCE_TEMP; - if (stmt->sequence->relpersistence != RELPERSISTENCE_TEMP) - ExecUtilityStmtOnNodes(queryString, NULL, false, EXEC_ON_COORDS, false); + /* Set temporary object flag in pooler */ + if (is_temp) + PoolManagerSetCommand(POOL_CMD_TEMP, NULL); + + ExecUtilityStmtOnNodes(queryString, NULL, false, EXEC_ON_ALL_NODES, is_temp); } #endif break; @@ -1527,15 +1526,15 @@ standard_ProcessUtility(Node *parsetree, #ifdef PGXC if (IS_PGXC_COORDINATOR) { - /* - * If sequence is temporary, no need to send this query to other - * remote Coordinators. - */ AlterSeqStmt *stmt = (AlterSeqStmt *) parsetree; - Oid relid = RangeVarGetRelid(stmt->sequence, false); + bool is_temp; + RemoteQueryExecType exec_type; - if (!IsTempSequence(relid)) - ExecUtilityStmtOnNodes(queryString, NULL, false, EXEC_ON_COORDS, false); + exec_type = ExecUtilityFindNodes(OBJECT_SEQUENCE, + RangeVarGetRelid(stmt->sequence, false), + &is_temp); + + ExecUtilityStmtOnNodes(queryString, NULL, false, exec_type, is_temp); } #endif break; @@ -2168,11 +2167,8 @@ ExecUtilityFindNodes(ObjectType object_type, switch (object_type) { case OBJECT_SEQUENCE: - /* Check if object is a temporary sequence */ - if ((*is_temp = IsTempSequence(relid))) - exec_type = EXEC_ON_NONE; - else - exec_type = EXEC_ON_COORDS; + *is_temp = IsTempTable(relid); + exec_type = EXEC_ON_ALL_NODES; break; case OBJECT_TABLE: @@ -2220,10 +2216,8 @@ ExecUtilityFindNodesRelkind(Oid relid, bool *is_temp) switch (relkind_str) { case RELKIND_SEQUENCE: - if ((*is_temp = IsTempSequence(relid))) - exec_type = EXEC_ON_NONE; - else - exec_type = EXEC_ON_COORDS; + *is_temp = IsTempTable(relid); + exec_type = EXEC_ON_ALL_NODES; break; case RELKIND_RELATION: diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 2d75af3d94..b8666c2a93 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -4346,6 +4346,8 @@ DATA(insert OID = 3200 ( pgxc_pool_check PGNSP PGUID 12 1 0 0 f f f t f v 0 0 16 DESCR("check connection information consistency in pooler"); DATA(insert OID = 3201 ( pgxc_pool_reload PGNSP PGUID 12 1 0 0 f f f t f v 0 0 16 "" _null_ _null_ _null_ _null_ pgxc_pool_reload _null_ _null_ _null_ )); DESCR("reload connection information in pooler and reload server sessions"); +DATA(insert OID = 3122 ( pgxc_node_str PGNSP PGUID 12 1 0 0 f f f t f s 0 0 19 "" _null_ _null_ _null_ _null_ pgxc_node_str _null_ _null_ _null_ )); +DESCR("get the name of the node"); #endif /* diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h index bdd499bea6..19f3c7cf90 100644 --- a/src/include/executor/executor.h +++ b/src/include/executor/executor.h @@ -107,6 +107,10 @@ extern bool execCurrentOf(CurrentOfExpr *cexpr, Oid table_oid, ItemPointer current_tid); +#ifdef PGXC +ScanState *search_plan_tree(PlanState *node, Oid table_oid); +#endif + /* * prototypes from functions in execGrouping.c */ diff --git a/src/include/parser/parse_expr.h b/src/include/parser/parse_expr.h index 56b1b4284d..f3edf702ec 100644 --- a/src/include/parser/parse_expr.h +++ b/src/include/parser/parse_expr.h @@ -19,8 +19,4 @@ extern bool Transform_null_equals; extern Node *transformExpr(ParseState *pstate, Node *expr); -#ifdef PGXC -extern bool IsFuncImmutable(ParseState *pstate, FuncCall *fn); -#endif - #endif /* PARSE_EXPR_H */ diff --git a/src/include/parser/parse_func.h b/src/include/parser/parse_func.h index 05e88968a6..d28c473ebe 100644 --- a/src/include/parser/parse_func.h +++ b/src/include/parser/parse_func.h @@ -83,7 +83,4 @@ extern Oid LookupAggNameTypeNames(List *aggname, List *argtypes, bool noError); extern void check_pg_get_expr_args(ParseState *pstate, Oid fnoid, List *args); -#ifdef PGXC -extern bool IsParseFuncImmutable(ParseState *pstate, List *fn_args, List *funcname, bool func_variadic); -#endif #endif /* PARSE_FUNC_H */ diff --git a/src/include/parser/parse_relation.h b/src/include/parser/parse_relation.h index 0158465c91..c53e34cc1f 100644 --- a/src/include/parser/parse_relation.h +++ b/src/include/parser/parse_relation.h @@ -92,4 +92,8 @@ extern Name attnumAttName(Relation rd, int attid); extern Oid attnumTypeId(Relation rd, int attid); extern Oid attnumCollationId(Relation rd, int attid); +#ifdef PGXC +extern int specialAttNum(const char *attname); +#endif + #endif /* PARSE_RELATION_H */ diff --git a/src/include/pgxc/pgxcnode.h b/src/include/pgxc/pgxcnode.h index d701e55b82..c7f1ab1e55 100644 --- a/src/include/pgxc/pgxcnode.h +++ b/src/include/pgxc/pgxcnode.h @@ -107,6 +107,7 @@ extern void PGXCNodeCleanAndRelease(int code, Datum arg); /* Look at information cached in node handles */ extern int PGXCNodeGetNodeId(Oid nodeoid, char node_type); extern Oid PGXCNodeGetNodeOid(int nodeid, char node_type); +extern int PGXCNodeGetNodeIdFromName(char *node_name, char node_type); extern PGXCNodeAllHandles *get_handles(List *datanodelist, List *coordlist, bool is_query_coord_only); extern void pfree_pgxc_all_handles(PGXCNodeAllHandles *handles); diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h index 1e07ef18cb..53e6a56fa5 100644 --- a/src/include/utils/builtins.h +++ b/src/include/utils/builtins.h @@ -515,6 +515,9 @@ extern Datum void_in(PG_FUNCTION_ARGS); extern Datum void_out(PG_FUNCTION_ARGS); extern Datum void_recv(PG_FUNCTION_ARGS); extern Datum void_send(PG_FUNCTION_ARGS); +#ifdef PGXC +extern Datum pgxc_node_str (PG_FUNCTION_ARGS); +#endif extern Datum trigger_in(PG_FUNCTION_ARGS); extern Datum trigger_out(PG_FUNCTION_ARGS); extern Datum language_handler_in(PG_FUNCTION_ARGS); diff --git a/src/test/regress/expected/dependency_1.out b/src/test/regress/expected/dependency_1.out index 827f442672..eeb2a1c110 100644 --- a/src/test/regress/expected/dependency_1.out +++ b/src/test/regress/expected/dependency_1.out @@ -102,8 +102,6 @@ CREATE TABLE deptest2 (f1 int); -- make a serial column the hard way CREATE SEQUENCE ss1; ALTER TABLE deptest2 ALTER f1 SET DEFAULT nextval('ss1'); -ERROR: Postgres-XC does not support DEFAULT with non-immutable functions yet -DETAIL: The feature is not currently supported ALTER SEQUENCE ss1 OWNED BY deptest2.f1; RESET SESSION AUTHORIZATION; REASSIGN OWNED BY regression_user1 TO regression_user2; diff --git a/src/test/regress/expected/domain_1.out b/src/test/regress/expected/domain_1.out index f2f45379d6..e3a6127396 100644 --- a/src/test/regress/expected/domain_1.out +++ b/src/test/regress/expected/domain_1.out @@ -246,8 +246,6 @@ create domain ddef2 oid DEFAULT '12'; create domain ddef3 text DEFAULT 5; create sequence ddef4_seq; create domain ddef4 int4 DEFAULT nextval('ddef4_seq'); -ERROR: Postgres-XC does not support DEFAULT with non-immutable functions yet -DETAIL: The feature is not currently supported create domain ddef5 numeric(8,2) NOT NULL DEFAULT '12.12'; create table defaulttest ( col1 ddef1 @@ -259,9 +257,7 @@ create table defaulttest , col7 ddef4 DEFAULT 8000 , col8 ddef5 ); -ERROR: type "ddef4" does not exist -LINE 5: , col4 ddef4 PRIMARY KEY - ^ +ERROR: Column col4 is not a hash distributable data type insert into defaulttest(col4) values(0); -- fails, col5 defaults to null ERROR: relation "defaulttest" does not exist LINE 1: insert into defaulttest(col4) values(0); @@ -410,7 +406,6 @@ drop domain ddef1 restrict; drop domain ddef2 restrict; drop domain ddef3 restrict; drop domain ddef4 restrict; -ERROR: type "ddef4" does not exist drop domain ddef5 restrict; drop sequence ddef4_seq; -- Test domains over domains diff --git a/src/test/regress/expected/portals_2.out b/src/test/regress/expected/portals_2.out new file mode 100644 index 0000000000..ab626eff2b --- /dev/null +++ b/src/test/regress/expected/portals_2.out @@ -0,0 +1,1288 @@ +-- +-- Cursor regression tests +-- +BEGIN; +DECLARE foo1 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; +DECLARE foo2 SCROLL CURSOR FOR SELECT * FROM tenk2 ORDER BY unique2; +DECLARE foo3 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; +DECLARE foo4 SCROLL CURSOR FOR SELECT * FROM tenk2 ORDER BY unique2; +DECLARE foo5 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; +DECLARE foo6 SCROLL CURSOR FOR SELECT * FROM tenk2 ORDER BY unique2; +DECLARE foo7 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; +DECLARE foo8 SCROLL CURSOR FOR SELECT * FROM tenk2 ORDER BY unique2; +DECLARE foo9 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; +DECLARE foo10 SCROLL CURSOR FOR SELECT * FROM tenk2 ORDER BY unique2; +DECLARE foo11 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; +DECLARE foo12 SCROLL CURSOR FOR SELECT * FROM tenk2 ORDER BY unique2; +DECLARE foo13 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; +DECLARE foo14 SCROLL CURSOR FOR SELECT * FROM tenk2 ORDER BY unique2; +DECLARE foo15 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; +DECLARE foo16 SCROLL CURSOR FOR SELECT * FROM tenk2 ORDER BY unique2; +DECLARE foo17 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; +DECLARE foo18 SCROLL CURSOR FOR SELECT * FROM tenk2 ORDER BY unique2; +DECLARE foo19 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; +DECLARE foo20 SCROLL CURSOR FOR SELECT * FROM tenk2 ORDER BY unique2; +DECLARE foo21 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; +DECLARE foo22 SCROLL CURSOR FOR SELECT * FROM tenk2 ORDER BY unique2; +DECLARE foo23 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; +FETCH 1 in foo1; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx +(1 row) + +FETCH 2 in foo2; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx +(2 rows) + +FETCH 3 in foo3; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx +(3 rows) + +FETCH 4 in foo4; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx +(4 rows) + +FETCH 5 in foo5; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx +(5 rows) + +FETCH 6 in foo6; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx +(6 rows) + +FETCH 7 in foo7; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx +(7 rows) + +FETCH 8 in foo8; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx + 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx +(8 rows) + +FETCH 9 in foo9; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx + 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx + 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx +(9 rows) + +FETCH 10 in foo10; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx + 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx + 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx + 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx +(10 rows) + +FETCH 11 in foo11; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx + 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx + 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx + 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx + 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx +(11 rows) + +FETCH 12 in foo12; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx + 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx + 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx + 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx + 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx + 1504 | 11 | 0 | 0 | 4 | 4 | 4 | 504 | 1504 | 1504 | 1504 | 8 | 9 | WFAAAA | LAAAAA | VVVVxx +(12 rows) + +FETCH 13 in foo13; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx + 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx + 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx + 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx + 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx + 1504 | 11 | 0 | 0 | 4 | 4 | 4 | 504 | 1504 | 1504 | 1504 | 8 | 9 | WFAAAA | LAAAAA | VVVVxx + 5222 | 12 | 0 | 2 | 2 | 2 | 22 | 222 | 1222 | 222 | 5222 | 44 | 45 | WSAAAA | MAAAAA | AAAAxx +(13 rows) + +FETCH 14 in foo14; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx + 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx + 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx + 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx + 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx + 1504 | 11 | 0 | 0 | 4 | 4 | 4 | 504 | 1504 | 1504 | 1504 | 8 | 9 | WFAAAA | LAAAAA | VVVVxx + 5222 | 12 | 0 | 2 | 2 | 2 | 22 | 222 | 1222 | 222 | 5222 | 44 | 45 | WSAAAA | MAAAAA | AAAAxx + 6243 | 13 | 1 | 3 | 3 | 3 | 43 | 243 | 243 | 1243 | 6243 | 86 | 87 | DGAAAA | NAAAAA | HHHHxx +(14 rows) + +FETCH 15 in foo15; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx + 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx + 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx + 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx + 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx + 1504 | 11 | 0 | 0 | 4 | 4 | 4 | 504 | 1504 | 1504 | 1504 | 8 | 9 | WFAAAA | LAAAAA | VVVVxx + 5222 | 12 | 0 | 2 | 2 | 2 | 22 | 222 | 1222 | 222 | 5222 | 44 | 45 | WSAAAA | MAAAAA | AAAAxx + 6243 | 13 | 1 | 3 | 3 | 3 | 43 | 243 | 243 | 1243 | 6243 | 86 | 87 | DGAAAA | NAAAAA | HHHHxx + 5471 | 14 | 1 | 3 | 1 | 11 | 71 | 471 | 1471 | 471 | 5471 | 142 | 143 | LCAAAA | OAAAAA | OOOOxx +(15 rows) + +FETCH 16 in foo16; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx + 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx + 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx + 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx + 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx + 1504 | 11 | 0 | 0 | 4 | 4 | 4 | 504 | 1504 | 1504 | 1504 | 8 | 9 | WFAAAA | LAAAAA | VVVVxx + 5222 | 12 | 0 | 2 | 2 | 2 | 22 | 222 | 1222 | 222 | 5222 | 44 | 45 | WSAAAA | MAAAAA | AAAAxx + 6243 | 13 | 1 | 3 | 3 | 3 | 43 | 243 | 243 | 1243 | 6243 | 86 | 87 | DGAAAA | NAAAAA | HHHHxx + 5471 | 14 | 1 | 3 | 1 | 11 | 71 | 471 | 1471 | 471 | 5471 | 142 | 143 | LCAAAA | OAAAAA | OOOOxx + 5006 | 15 | 0 | 2 | 6 | 6 | 6 | 6 | 1006 | 6 | 5006 | 12 | 13 | OKAAAA | PAAAAA | VVVVxx +(16 rows) + +FETCH 17 in foo17; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx + 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx + 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx + 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx + 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx + 1504 | 11 | 0 | 0 | 4 | 4 | 4 | 504 | 1504 | 1504 | 1504 | 8 | 9 | WFAAAA | LAAAAA | VVVVxx + 5222 | 12 | 0 | 2 | 2 | 2 | 22 | 222 | 1222 | 222 | 5222 | 44 | 45 | WSAAAA | MAAAAA | AAAAxx + 6243 | 13 | 1 | 3 | 3 | 3 | 43 | 243 | 243 | 1243 | 6243 | 86 | 87 | DGAAAA | NAAAAA | HHHHxx + 5471 | 14 | 1 | 3 | 1 | 11 | 71 | 471 | 1471 | 471 | 5471 | 142 | 143 | LCAAAA | OAAAAA | OOOOxx + 5006 | 15 | 0 | 2 | 6 | 6 | 6 | 6 | 1006 | 6 | 5006 | 12 | 13 | OKAAAA | PAAAAA | VVVVxx + 5387 | 16 | 1 | 3 | 7 | 7 | 87 | 387 | 1387 | 387 | 5387 | 174 | 175 | FZAAAA | QAAAAA | AAAAxx +(17 rows) + +FETCH 18 in foo18; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx + 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx + 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx + 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx + 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx + 1504 | 11 | 0 | 0 | 4 | 4 | 4 | 504 | 1504 | 1504 | 1504 | 8 | 9 | WFAAAA | LAAAAA | VVVVxx + 5222 | 12 | 0 | 2 | 2 | 2 | 22 | 222 | 1222 | 222 | 5222 | 44 | 45 | WSAAAA | MAAAAA | AAAAxx + 6243 | 13 | 1 | 3 | 3 | 3 | 43 | 243 | 243 | 1243 | 6243 | 86 | 87 | DGAAAA | NAAAAA | HHHHxx + 5471 | 14 | 1 | 3 | 1 | 11 | 71 | 471 | 1471 | 471 | 5471 | 142 | 143 | LCAAAA | OAAAAA | OOOOxx + 5006 | 15 | 0 | 2 | 6 | 6 | 6 | 6 | 1006 | 6 | 5006 | 12 | 13 | OKAAAA | PAAAAA | VVVVxx + 5387 | 16 | 1 | 3 | 7 | 7 | 87 | 387 | 1387 | 387 | 5387 | 174 | 175 | FZAAAA | QAAAAA | AAAAxx + 5785 | 17 | 1 | 1 | 5 | 5 | 85 | 785 | 1785 | 785 | 5785 | 170 | 171 | NOAAAA | RAAAAA | HHHHxx +(18 rows) + +FETCH 19 in foo19; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx + 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx + 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx + 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx + 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx + 1504 | 11 | 0 | 0 | 4 | 4 | 4 | 504 | 1504 | 1504 | 1504 | 8 | 9 | WFAAAA | LAAAAA | VVVVxx + 5222 | 12 | 0 | 2 | 2 | 2 | 22 | 222 | 1222 | 222 | 5222 | 44 | 45 | WSAAAA | MAAAAA | AAAAxx + 6243 | 13 | 1 | 3 | 3 | 3 | 43 | 243 | 243 | 1243 | 6243 | 86 | 87 | DGAAAA | NAAAAA | HHHHxx + 5471 | 14 | 1 | 3 | 1 | 11 | 71 | 471 | 1471 | 471 | 5471 | 142 | 143 | LCAAAA | OAAAAA | OOOOxx + 5006 | 15 | 0 | 2 | 6 | 6 | 6 | 6 | 1006 | 6 | 5006 | 12 | 13 | OKAAAA | PAAAAA | VVVVxx + 5387 | 16 | 1 | 3 | 7 | 7 | 87 | 387 | 1387 | 387 | 5387 | 174 | 175 | FZAAAA | QAAAAA | AAAAxx + 5785 | 17 | 1 | 1 | 5 | 5 | 85 | 785 | 1785 | 785 | 5785 | 170 | 171 | NOAAAA | RAAAAA | HHHHxx + 6621 | 18 | 1 | 1 | 1 | 1 | 21 | 621 | 621 | 1621 | 6621 | 42 | 43 | RUAAAA | SAAAAA | OOOOxx +(19 rows) + +FETCH 20 in foo20; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx + 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx + 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx + 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx + 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx + 1504 | 11 | 0 | 0 | 4 | 4 | 4 | 504 | 1504 | 1504 | 1504 | 8 | 9 | WFAAAA | LAAAAA | VVVVxx + 5222 | 12 | 0 | 2 | 2 | 2 | 22 | 222 | 1222 | 222 | 5222 | 44 | 45 | WSAAAA | MAAAAA | AAAAxx + 6243 | 13 | 1 | 3 | 3 | 3 | 43 | 243 | 243 | 1243 | 6243 | 86 | 87 | DGAAAA | NAAAAA | HHHHxx + 5471 | 14 | 1 | 3 | 1 | 11 | 71 | 471 | 1471 | 471 | 5471 | 142 | 143 | LCAAAA | OAAAAA | OOOOxx + 5006 | 15 | 0 | 2 | 6 | 6 | 6 | 6 | 1006 | 6 | 5006 | 12 | 13 | OKAAAA | PAAAAA | VVVVxx + 5387 | 16 | 1 | 3 | 7 | 7 | 87 | 387 | 1387 | 387 | 5387 | 174 | 175 | FZAAAA | QAAAAA | AAAAxx + 5785 | 17 | 1 | 1 | 5 | 5 | 85 | 785 | 1785 | 785 | 5785 | 170 | 171 | NOAAAA | RAAAAA | HHHHxx + 6621 | 18 | 1 | 1 | 1 | 1 | 21 | 621 | 621 | 1621 | 6621 | 42 | 43 | RUAAAA | SAAAAA | OOOOxx + 6969 | 19 | 1 | 1 | 9 | 9 | 69 | 969 | 969 | 1969 | 6969 | 138 | 139 | BIAAAA | TAAAAA | VVVVxx +(20 rows) + +FETCH 21 in foo21; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx + 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx + 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx + 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx + 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx + 1504 | 11 | 0 | 0 | 4 | 4 | 4 | 504 | 1504 | 1504 | 1504 | 8 | 9 | WFAAAA | LAAAAA | VVVVxx + 5222 | 12 | 0 | 2 | 2 | 2 | 22 | 222 | 1222 | 222 | 5222 | 44 | 45 | WSAAAA | MAAAAA | AAAAxx + 6243 | 13 | 1 | 3 | 3 | 3 | 43 | 243 | 243 | 1243 | 6243 | 86 | 87 | DGAAAA | NAAAAA | HHHHxx + 5471 | 14 | 1 | 3 | 1 | 11 | 71 | 471 | 1471 | 471 | 5471 | 142 | 143 | LCAAAA | OAAAAA | OOOOxx + 5006 | 15 | 0 | 2 | 6 | 6 | 6 | 6 | 1006 | 6 | 5006 | 12 | 13 | OKAAAA | PAAAAA | VVVVxx + 5387 | 16 | 1 | 3 | 7 | 7 | 87 | 387 | 1387 | 387 | 5387 | 174 | 175 | FZAAAA | QAAAAA | AAAAxx + 5785 | 17 | 1 | 1 | 5 | 5 | 85 | 785 | 1785 | 785 | 5785 | 170 | 171 | NOAAAA | RAAAAA | HHHHxx + 6621 | 18 | 1 | 1 | 1 | 1 | 21 | 621 | 621 | 1621 | 6621 | 42 | 43 | RUAAAA | SAAAAA | OOOOxx + 6969 | 19 | 1 | 1 | 9 | 9 | 69 | 969 | 969 | 1969 | 6969 | 138 | 139 | BIAAAA | TAAAAA | VVVVxx + 9460 | 20 | 0 | 0 | 0 | 0 | 60 | 460 | 1460 | 4460 | 9460 | 120 | 121 | WZAAAA | UAAAAA | AAAAxx +(21 rows) + +FETCH 22 in foo22; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx + 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx + 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx + 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx + 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx + 1504 | 11 | 0 | 0 | 4 | 4 | 4 | 504 | 1504 | 1504 | 1504 | 8 | 9 | WFAAAA | LAAAAA | VVVVxx + 5222 | 12 | 0 | 2 | 2 | 2 | 22 | 222 | 1222 | 222 | 5222 | 44 | 45 | WSAAAA | MAAAAA | AAAAxx + 6243 | 13 | 1 | 3 | 3 | 3 | 43 | 243 | 243 | 1243 | 6243 | 86 | 87 | DGAAAA | NAAAAA | HHHHxx + 5471 | 14 | 1 | 3 | 1 | 11 | 71 | 471 | 1471 | 471 | 5471 | 142 | 143 | LCAAAA | OAAAAA | OOOOxx + 5006 | 15 | 0 | 2 | 6 | 6 | 6 | 6 | 1006 | 6 | 5006 | 12 | 13 | OKAAAA | PAAAAA | VVVVxx + 5387 | 16 | 1 | 3 | 7 | 7 | 87 | 387 | 1387 | 387 | 5387 | 174 | 175 | FZAAAA | QAAAAA | AAAAxx + 5785 | 17 | 1 | 1 | 5 | 5 | 85 | 785 | 1785 | 785 | 5785 | 170 | 171 | NOAAAA | RAAAAA | HHHHxx + 6621 | 18 | 1 | 1 | 1 | 1 | 21 | 621 | 621 | 1621 | 6621 | 42 | 43 | RUAAAA | SAAAAA | OOOOxx + 6969 | 19 | 1 | 1 | 9 | 9 | 69 | 969 | 969 | 1969 | 6969 | 138 | 139 | BIAAAA | TAAAAA | VVVVxx + 9460 | 20 | 0 | 0 | 0 | 0 | 60 | 460 | 1460 | 4460 | 9460 | 120 | 121 | WZAAAA | UAAAAA | AAAAxx + 59 | 21 | 1 | 3 | 9 | 19 | 59 | 59 | 59 | 59 | 59 | 118 | 119 | HCAAAA | VAAAAA | HHHHxx +(22 rows) + +FETCH 23 in foo23; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx + 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx + 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx + 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx + 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx + 1504 | 11 | 0 | 0 | 4 | 4 | 4 | 504 | 1504 | 1504 | 1504 | 8 | 9 | WFAAAA | LAAAAA | VVVVxx + 5222 | 12 | 0 | 2 | 2 | 2 | 22 | 222 | 1222 | 222 | 5222 | 44 | 45 | WSAAAA | MAAAAA | AAAAxx + 6243 | 13 | 1 | 3 | 3 | 3 | 43 | 243 | 243 | 1243 | 6243 | 86 | 87 | DGAAAA | NAAAAA | HHHHxx + 5471 | 14 | 1 | 3 | 1 | 11 | 71 | 471 | 1471 | 471 | 5471 | 142 | 143 | LCAAAA | OAAAAA | OOOOxx + 5006 | 15 | 0 | 2 | 6 | 6 | 6 | 6 | 1006 | 6 | 5006 | 12 | 13 | OKAAAA | PAAAAA | VVVVxx + 5387 | 16 | 1 | 3 | 7 | 7 | 87 | 387 | 1387 | 387 | 5387 | 174 | 175 | FZAAAA | QAAAAA | AAAAxx + 5785 | 17 | 1 | 1 | 5 | 5 | 85 | 785 | 1785 | 785 | 5785 | 170 | 171 | NOAAAA | RAAAAA | HHHHxx + 6621 | 18 | 1 | 1 | 1 | 1 | 21 | 621 | 621 | 1621 | 6621 | 42 | 43 | RUAAAA | SAAAAA | OOOOxx + 6969 | 19 | 1 | 1 | 9 | 9 | 69 | 969 | 969 | 1969 | 6969 | 138 | 139 | BIAAAA | TAAAAA | VVVVxx + 9460 | 20 | 0 | 0 | 0 | 0 | 60 | 460 | 1460 | 4460 | 9460 | 120 | 121 | WZAAAA | UAAAAA | AAAAxx + 59 | 21 | 1 | 3 | 9 | 19 | 59 | 59 | 59 | 59 | 59 | 118 | 119 | HCAAAA | VAAAAA | HHHHxx + 8020 | 22 | 0 | 0 | 0 | 0 | 20 | 20 | 20 | 3020 | 8020 | 40 | 41 | MWAAAA | WAAAAA | OOOOxx +(23 rows) + +FETCH backward 1 in foo23; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 59 | 21 | 1 | 3 | 9 | 19 | 59 | 59 | 59 | 59 | 59 | 118 | 119 | HCAAAA | VAAAAA | HHHHxx +(1 row) + +FETCH backward 2 in foo22; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 9460 | 20 | 0 | 0 | 0 | 0 | 60 | 460 | 1460 | 4460 | 9460 | 120 | 121 | WZAAAA | UAAAAA | AAAAxx + 6969 | 19 | 1 | 1 | 9 | 9 | 69 | 969 | 969 | 1969 | 6969 | 138 | 139 | BIAAAA | TAAAAA | VVVVxx +(2 rows) + +FETCH backward 3 in foo21; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 6969 | 19 | 1 | 1 | 9 | 9 | 69 | 969 | 969 | 1969 | 6969 | 138 | 139 | BIAAAA | TAAAAA | VVVVxx + 6621 | 18 | 1 | 1 | 1 | 1 | 21 | 621 | 621 | 1621 | 6621 | 42 | 43 | RUAAAA | SAAAAA | OOOOxx + 5785 | 17 | 1 | 1 | 5 | 5 | 85 | 785 | 1785 | 785 | 5785 | 170 | 171 | NOAAAA | RAAAAA | HHHHxx +(3 rows) + +FETCH backward 4 in foo20; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 6621 | 18 | 1 | 1 | 1 | 1 | 21 | 621 | 621 | 1621 | 6621 | 42 | 43 | RUAAAA | SAAAAA | OOOOxx + 5785 | 17 | 1 | 1 | 5 | 5 | 85 | 785 | 1785 | 785 | 5785 | 170 | 171 | NOAAAA | RAAAAA | HHHHxx + 5387 | 16 | 1 | 3 | 7 | 7 | 87 | 387 | 1387 | 387 | 5387 | 174 | 175 | FZAAAA | QAAAAA | AAAAxx + 5006 | 15 | 0 | 2 | 6 | 6 | 6 | 6 | 1006 | 6 | 5006 | 12 | 13 | OKAAAA | PAAAAA | VVVVxx +(4 rows) + +FETCH backward 5 in foo19; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 5785 | 17 | 1 | 1 | 5 | 5 | 85 | 785 | 1785 | 785 | 5785 | 170 | 171 | NOAAAA | RAAAAA | HHHHxx + 5387 | 16 | 1 | 3 | 7 | 7 | 87 | 387 | 1387 | 387 | 5387 | 174 | 175 | FZAAAA | QAAAAA | AAAAxx + 5006 | 15 | 0 | 2 | 6 | 6 | 6 | 6 | 1006 | 6 | 5006 | 12 | 13 | OKAAAA | PAAAAA | VVVVxx + 5471 | 14 | 1 | 3 | 1 | 11 | 71 | 471 | 1471 | 471 | 5471 | 142 | 143 | LCAAAA | OAAAAA | OOOOxx + 6243 | 13 | 1 | 3 | 3 | 3 | 43 | 243 | 243 | 1243 | 6243 | 86 | 87 | DGAAAA | NAAAAA | HHHHxx +(5 rows) + +FETCH backward 6 in foo18; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 5387 | 16 | 1 | 3 | 7 | 7 | 87 | 387 | 1387 | 387 | 5387 | 174 | 175 | FZAAAA | QAAAAA | AAAAxx + 5006 | 15 | 0 | 2 | 6 | 6 | 6 | 6 | 1006 | 6 | 5006 | 12 | 13 | OKAAAA | PAAAAA | VVVVxx + 5471 | 14 | 1 | 3 | 1 | 11 | 71 | 471 | 1471 | 471 | 5471 | 142 | 143 | LCAAAA | OAAAAA | OOOOxx + 6243 | 13 | 1 | 3 | 3 | 3 | 43 | 243 | 243 | 1243 | 6243 | 86 | 87 | DGAAAA | NAAAAA | HHHHxx + 5222 | 12 | 0 | 2 | 2 | 2 | 22 | 222 | 1222 | 222 | 5222 | 44 | 45 | WSAAAA | MAAAAA | AAAAxx + 1504 | 11 | 0 | 0 | 4 | 4 | 4 | 504 | 1504 | 1504 | 1504 | 8 | 9 | WFAAAA | LAAAAA | VVVVxx +(6 rows) + +FETCH backward 7 in foo17; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 5006 | 15 | 0 | 2 | 6 | 6 | 6 | 6 | 1006 | 6 | 5006 | 12 | 13 | OKAAAA | PAAAAA | VVVVxx + 5471 | 14 | 1 | 3 | 1 | 11 | 71 | 471 | 1471 | 471 | 5471 | 142 | 143 | LCAAAA | OAAAAA | OOOOxx + 6243 | 13 | 1 | 3 | 3 | 3 | 43 | 243 | 243 | 1243 | 6243 | 86 | 87 | DGAAAA | NAAAAA | HHHHxx + 5222 | 12 | 0 | 2 | 2 | 2 | 22 | 222 | 1222 | 222 | 5222 | 44 | 45 | WSAAAA | MAAAAA | AAAAxx + 1504 | 11 | 0 | 0 | 4 | 4 | 4 | 504 | 1504 | 1504 | 1504 | 8 | 9 | WFAAAA | LAAAAA | VVVVxx + 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx + 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx +(7 rows) + +FETCH backward 8 in foo16; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 5471 | 14 | 1 | 3 | 1 | 11 | 71 | 471 | 1471 | 471 | 5471 | 142 | 143 | LCAAAA | OAAAAA | OOOOxx + 6243 | 13 | 1 | 3 | 3 | 3 | 43 | 243 | 243 | 1243 | 6243 | 86 | 87 | DGAAAA | NAAAAA | HHHHxx + 5222 | 12 | 0 | 2 | 2 | 2 | 22 | 222 | 1222 | 222 | 5222 | 44 | 45 | WSAAAA | MAAAAA | AAAAxx + 1504 | 11 | 0 | 0 | 4 | 4 | 4 | 504 | 1504 | 1504 | 1504 | 8 | 9 | WFAAAA | LAAAAA | VVVVxx + 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx + 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx + 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx + 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx +(8 rows) + +FETCH backward 9 in foo15; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 6243 | 13 | 1 | 3 | 3 | 3 | 43 | 243 | 243 | 1243 | 6243 | 86 | 87 | DGAAAA | NAAAAA | HHHHxx + 5222 | 12 | 0 | 2 | 2 | 2 | 22 | 222 | 1222 | 222 | 5222 | 44 | 45 | WSAAAA | MAAAAA | AAAAxx + 1504 | 11 | 0 | 0 | 4 | 4 | 4 | 504 | 1504 | 1504 | 1504 | 8 | 9 | WFAAAA | LAAAAA | VVVVxx + 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx + 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx + 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx + 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx +(9 rows) + +FETCH backward 10 in foo14; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 5222 | 12 | 0 | 2 | 2 | 2 | 22 | 222 | 1222 | 222 | 5222 | 44 | 45 | WSAAAA | MAAAAA | AAAAxx + 1504 | 11 | 0 | 0 | 4 | 4 | 4 | 504 | 1504 | 1504 | 1504 | 8 | 9 | WFAAAA | LAAAAA | VVVVxx + 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx + 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx + 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx + 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx +(10 rows) + +FETCH backward 11 in foo13; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 1504 | 11 | 0 | 0 | 4 | 4 | 4 | 504 | 1504 | 1504 | 1504 | 8 | 9 | WFAAAA | LAAAAA | VVVVxx + 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx + 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx + 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx + 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx +(11 rows) + +FETCH backward 12 in foo12; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx + 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx + 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx + 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx +(11 rows) + +FETCH backward 13 in foo11; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx + 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx + 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx +(10 rows) + +FETCH backward 14 in foo10; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx + 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx +(9 rows) + +FETCH backward 15 in foo9; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx +(8 rows) + +FETCH backward 16 in foo8; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx +(7 rows) + +FETCH backward 17 in foo7; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx +(6 rows) + +FETCH backward 18 in foo6; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx +(5 rows) + +FETCH backward 19 in foo5; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx +(4 rows) + +FETCH backward 20 in foo4; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx +(3 rows) + +FETCH backward 21 in foo3; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx +(2 rows) + +FETCH backward 22 in foo2; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx +(1 row) + +FETCH backward 23 in foo1; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- +(0 rows) + +CLOSE foo1; +CLOSE foo2; +CLOSE foo3; +CLOSE foo4; +CLOSE foo5; +CLOSE foo6; +CLOSE foo7; +CLOSE foo8; +CLOSE foo9; +CLOSE foo10; +CLOSE foo11; +CLOSE foo12; +-- leave some cursors open, to test that auto-close works. +-- record this in the system view as well (don't query the time field there +-- however) +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY 1; + name | statement | is_holdable | is_binary | is_scrollable +-------+-----------------------------------------------------------------------+-------------+-----------+--------------- + foo13 | DECLARE foo13 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; | f | f | t + foo14 | DECLARE foo14 SCROLL CURSOR FOR SELECT * FROM tenk2 ORDER BY unique2; | f | f | t + foo15 | DECLARE foo15 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; | f | f | t + foo16 | DECLARE foo16 SCROLL CURSOR FOR SELECT * FROM tenk2 ORDER BY unique2; | f | f | t + foo17 | DECLARE foo17 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; | f | f | t + foo18 | DECLARE foo18 SCROLL CURSOR FOR SELECT * FROM tenk2 ORDER BY unique2; | f | f | t + foo19 | DECLARE foo19 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; | f | f | t + foo20 | DECLARE foo20 SCROLL CURSOR FOR SELECT * FROM tenk2 ORDER BY unique2; | f | f | t + foo21 | DECLARE foo21 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; | f | f | t + foo22 | DECLARE foo22 SCROLL CURSOR FOR SELECT * FROM tenk2 ORDER BY unique2; | f | f | t + foo23 | DECLARE foo23 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; | f | f | t +(11 rows) + +END; +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors; + name | statement | is_holdable | is_binary | is_scrollable +------+-----------+-------------+-----------+--------------- +(0 rows) + +-- +-- NO SCROLL disallows backward fetching +-- +BEGIN; +DECLARE foo24 NO SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; +FETCH 1 FROM foo24; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx +(1 row) + +FETCH BACKWARD 1 FROM foo24; -- should fail +ERROR: cursor can only scan forward +HINT: Declare it with SCROLL option to enable backward scan. +END; +-- +-- Cursors outside transaction blocks +-- +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors; + name | statement | is_holdable | is_binary | is_scrollable +------+-----------+-------------+-----------+--------------- +(0 rows) + +BEGIN; +DECLARE foo25 SCROLL CURSOR WITH HOLD FOR SELECT * FROM tenk2 ORDER BY unique2; +FETCH FROM foo25; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx +(1 row) + +FETCH FROM foo25; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx +(1 row) + +COMMIT; +FETCH FROM foo25; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx +(1 row) + +FETCH BACKWARD FROM foo25; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx +(1 row) + +FETCH ABSOLUTE -1 FROM foo25; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 2968 | 9999 | 0 | 0 | 8 | 8 | 68 | 968 | 968 | 2968 | 2968 | 136 | 137 | EKAAAA | PUOAAA | VVVVxx +(1 row) + +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors; + name | statement | is_holdable | is_binary | is_scrollable +-------+---------------------------------------------------------------------------------+-------------+-----------+--------------- + foo25 | DECLARE foo25 SCROLL CURSOR WITH HOLD FOR SELECT * FROM tenk2 ORDER BY unique2; | t | f | t +(1 row) + +CLOSE foo25; +-- +-- ROLLBACK should close holdable cursors +-- +BEGIN; +DECLARE foo26 CURSOR WITH HOLD FOR SELECT * FROM tenk1 ORDER BY unique2; +ROLLBACK; +-- should fail +FETCH FROM foo26; +ERROR: cursor "foo26" does not exist +-- +-- Parameterized DECLARE needs to insert param values into the cursor portal +-- +BEGIN; +CREATE FUNCTION declares_cursor(text) + RETURNS void + AS 'DECLARE c CURSOR FOR SELECT stringu1 FROM tenk1 WHERE stringu1 LIKE $1 ORDER BY stringu1;' + LANGUAGE SQL; +SELECT declares_cursor('AB%'); + declares_cursor +----------------- + +(1 row) + +FETCH ALL FROM c; + stringu1 +---------- + ABAAAA + ABAAAA + ABAAAA + ABAAAA + ABAAAA + ABAAAA + ABAAAA + ABAAAA + ABAAAA + ABAAAA + ABAAAA + ABAAAA + ABAAAA + ABAAAA + ABAAAA +(15 rows) + +ROLLBACK; +-- +-- Test behavior of both volatile and stable functions inside a cursor; +-- in particular we want to see what happens during commit of a holdable +-- cursor +-- +create table tt1(f1 int); +create function count_tt1_v() returns int8 as +'select count(*) from tt1' language sql volatile; +create function count_tt1_s() returns int8 as +'select count(*) from tt1' language sql stable; +begin; +insert into tt1 values(1); +declare c1 cursor for select count_tt1_v(), count_tt1_s(); +insert into tt1 values(2); +fetch all from c1; + count_tt1_v | count_tt1_s +-------------+------------- + 2 | 2 +(1 row) + +rollback; +begin; +insert into tt1 values(1); +declare c2 cursor with hold for select count_tt1_v(), count_tt1_s(); +insert into tt1 values(2); +commit; +ERROR: Could not commit prepared transaction implicitly +delete from tt1; +fetch all from c2; + count_tt1_v | count_tt1_s +-------------+------------- + 0 | 0 +(1 row) + +drop function count_tt1_v(); +drop function count_tt1_s(); +drop table tt1; +-- Create a cursor with the BINARY option and check the pg_cursors view +BEGIN; +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors; + name | statement | is_holdable | is_binary | is_scrollable +------+----------------------------------------------------------------------+-------------+-----------+--------------- + c2 | declare c2 cursor with hold for select count_tt1_v(), count_tt1_s(); | t | f | f +(1 row) + +DECLARE bc BINARY CURSOR FOR SELECT * FROM tenk1; +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY 1; + name | statement | is_holdable | is_binary | is_scrollable +------+----------------------------------------------------------------------+-------------+-----------+--------------- + bc | DECLARE bc BINARY CURSOR FOR SELECT * FROM tenk1; | f | t | f + c2 | declare c2 cursor with hold for select count_tt1_v(), count_tt1_s(); | t | f | f +(2 rows) + +ROLLBACK; +-- We should not see the portal that is created internally to +-- implement EXECUTE in pg_cursors +PREPARE cprep AS + SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors; +EXECUTE cprep; + name | statement | is_holdable | is_binary | is_scrollable +------+----------------------------------------------------------------------+-------------+-----------+--------------- + c2 | declare c2 cursor with hold for select count_tt1_v(), count_tt1_s(); | t | f | f +(1 row) + +-- test CLOSE ALL; +SELECT name FROM pg_cursors ORDER BY 1; + name +------ + c2 +(1 row) + +CLOSE ALL; +SELECT name FROM pg_cursors ORDER BY 1; + name +------ +(0 rows) + +BEGIN; +DECLARE foo1 CURSOR WITH HOLD FOR SELECT 1; +DECLARE foo2 CURSOR WITHOUT HOLD FOR SELECT 1; +SELECT name FROM pg_cursors ORDER BY 1; + name +------ + foo1 + foo2 +(2 rows) + +CLOSE ALL; +SELECT name FROM pg_cursors ORDER BY 1; + name +------ +(0 rows) + +COMMIT; +-- +-- Tests for updatable cursors +-- +CREATE TABLE uctest(a int, f1 int, f2 text); +INSERT INTO uctest VALUES (11, 1, 'one'), (22, 2, 'two'), (33, 3, 'three'); +SELECT f1,f2 FROM uctest ORDER BY f1; + f1 | f2 +----+------- + 1 | one + 2 | two + 3 | three +(3 rows) + +-- Check DELETE WHERE CURRENT +BEGIN; +DECLARE c1 SCROLL CURSOR FOR SELECT f1,f2 FROM uctest ORDER BY f1; +FETCH 2 FROM c1; + f1 | f2 +----+----- + 1 | one + 2 | two +(2 rows) + +DELETE FROM uctest WHERE CURRENT OF c1; +-- should show deletion +SELECT f1,f2 FROM uctest ORDER BY f1; + f1 | f2 +----+------- + 1 | one + 3 | three +(2 rows) + +-- cursor did not move +FETCH ALL FROM c1; + f1 | f2 +----+------- + 3 | three +(1 row) + +-- cursor is insensitive +MOVE BACKWARD ALL IN c1; +FETCH ALL FROM c1; + f1 | f2 +----+------- + 1 | one + 2 | two + 3 | three +(3 rows) + +COMMIT; +-- should still see deletion +SELECT f1,f2 FROM uctest ORDER BY f1; + f1 | f2 +----+------- + 1 | one + 3 | three +(2 rows) + +-- Check UPDATE WHERE CURRENT; this time use FOR UPDATE +BEGIN; +DECLARE c1 CURSOR FOR SELECT f1,f2 FROM uctest FOR UPDATE; +FETCH c1; + f1 | f2 +----+----- + 1 | one +(1 row) + +UPDATE uctest SET f1 = 8 WHERE CURRENT OF c1; +SELECT f1,f2 FROM uctest ORDER BY f1; + f1 | f2 +----+------- + 3 | three + 8 | one +(2 rows) + +COMMIT; +SELECT f1,f2 FROM uctest ORDER BY f1; + f1 | f2 +----+------- + 3 | three + 8 | one +(2 rows) + +-- Check repeated-update and update-then-delete cases +BEGIN; +DECLARE c1 SCROLL CURSOR FOR SELECT f1,f2 FROM uctest; +FETCH c1; + f1 | f2 +----+------- + 3 | three +(1 row) + +UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; +SELECT f1,f2 FROM uctest ORDER BY 1; + f1 | f2 +----+------- + 8 | one + 13 | three +(2 rows) + +UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; +SELECT f1,f2 FROM uctest ORDER BY 1; + f1 | f2 +----+------- + 8 | one + 23 | three +(2 rows) + +-- insensitive cursor should not show effects of updates or deletes +FETCH RELATIVE 0 FROM c1; + f1 | f2 +----+------- + 3 | three +(1 row) + +DELETE FROM uctest WHERE CURRENT OF c1; +SELECT f1,f2 FROM uctest ORDER BY f1; + f1 | f2 +----+----- + 8 | one +(1 row) + +DELETE FROM uctest WHERE CURRENT OF c1; -- no-op +SELECT f1,f2 FROM uctest ORDER BY f1; + f1 | f2 +----+------- + 8 | one +(1 row) + +UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; -- no-op +SELECT f1,f2 FROM uctest ORDER BY f1; + f1 | f2 +----+----- + 8 | one +(1 row) + +FETCH RELATIVE 0 FROM c1; + f1 | f2 +----+------- + 3 | three +(1 row) + +ROLLBACK; +SELECT f1,f2 FROM uctest ORDER BY f1; + f1 | f2 +----+------- + 3 | three + 8 | one +(2 rows) + +BEGIN; +DECLARE c1 CURSOR FOR SELECT f1,f2 FROM uctest ORDER BY 1 FOR UPDATE; +FETCH c1; + f1 | f2 +----+------- + 3 | three +(1 row) + +UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; +SELECT f1,f2 FROM uctest ORDER BY f1; + f1 | f2 +----+------- + 8 | one + 13 | three +(2 rows) + +UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; +SELECT f1,f2 FROM uctest ORDER BY f1; + f1 | f2 +----+------- + 8 | one + 23 | three +(2 rows) + +DELETE FROM uctest WHERE CURRENT OF c1; +SELECT f1,f2 FROM uctest ORDER BY f1; + f1 | f2 +----+----- + 8 | one +(1 row) + +DELETE FROM uctest WHERE CURRENT OF c1; -- no-op +SELECT f1,f2 FROM uctest ORDER BY f1; + f1 | f2 +----+----- + 8 | one +(1 row) + +UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; -- no-op +SELECT f1,f2 FROM uctest ORDER BY f1; + f1 | f2 +----+----- + 8 | one +(1 row) + +--- sensitive cursors can't currently scroll back, so this is an error: +FETCH RELATIVE 0 FROM c1; +ERROR: cursor can only scan forward +HINT: Declare it with SCROLL option to enable backward scan. +ROLLBACK; +SELECT f1,f2 FROM uctest ORDER BY f1; + f1 | f2 +----+------- + 3 | three + 8 | one +(2 rows) + +-- Check inheritance cases +CREATE TABLE ucchild () inherits (uctest); +INSERT INTO ucchild values(0, 100, 'hundred'); +SELECT f1,f2 FROM uctest ORDER BY f1; + f1 | f2 +-----+--------- + 3 | three + 8 | one + 100 | hundred +(3 rows) + +BEGIN; +DECLARE c1 CURSOR FOR SELECT f1,f2 FROM uctest ORDER BY 1 FOR UPDATE; +FETCH 1 FROM c1; + f1 | f2 +----+------- + 3 | three +(1 row) + +UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; +FETCH 1 FROM c1; + f1 | f2 +----+----- + 8 | one +(1 row) + +UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; +FETCH 1 FROM c1; + f1 | f2 +-----+--------- + 100 | hundred +(1 row) + +UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; +FETCH 1 FROM c1; + f1 | f2 +----+---- +(0 rows) + +COMMIT; +SELECT f1,f2 FROM uctest ORDER BY f1; + f1 | f2 +-----+--------- + 13 | three + 18 | one + 110 | hundred +(3 rows) + +-- Can update from a self-join, but only if FOR UPDATE says which to use +BEGIN; +DECLARE c1 CURSOR FOR SELECT a.f1,a.f2,b.f1,b.f2 FROM uctest a, uctest b WHERE a.f1 = b.f1 + 5 ORDER BY 1; +FETCH 1 FROM c1; + f1 | f2 | f1 | f2 +----+-----+----+------- + 18 | one | 13 | three +(1 row) + +UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; -- fail +ERROR: cursor "c1" is not a simply updatable scan of table "uctest" +ROLLBACK; +BEGIN; +DECLARE c1 CURSOR FOR SELECT a.f1,a.f2, b.f1, b.f2 FROM uctest a, uctest b WHERE a.f1 = b.f1 + 5 ORDER BY 1 FOR UPDATE; +FETCH 1 FROM c1; + f1 | f2 | f1 | f2 +----+-----+----+------- + 18 | one | 13 | three +(1 row) + +UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; -- fail +ERROR: cursor "c1" is not a simply updatable scan of table "uctest" +ROLLBACK; +BEGIN; +DECLARE c1 CURSOR FOR SELECT a.f1,a.f2,b.f1,b.f2 FROM uctest a, uctest b WHERE a.f1 = b.f1 + 5 ORDER BY 1 FOR SHARE OF a; +FETCH 1 FROM c1; + f1 | f2 | f1 | f2 +----+-----+----+------- + 18 | one | 13 | three +(1 row) + +UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; +SELECT * FROM uctest; + f1 | f2 +-----+--------- + 13 | three + 28 | one + 110 | hundred +(3 rows) + +ROLLBACK; +-- Check various error cases +DELETE FROM uctest WHERE CURRENT OF c1; -- fail, no such cursor +ERROR: cursor "c1" does not exist +DECLARE cx CURSOR WITH HOLD FOR SELECT f1,f2 FROM uctest; +DELETE FROM uctest WHERE CURRENT OF cx; -- fail, can't use held cursor +ERROR: cursor "cx" is held from a previous transaction +BEGIN; +DECLARE c CURSOR FOR SELECT * FROM tenk2 ORDER BY unique2; +FETCH 1 FROM c; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx +(1 row) + +DELETE FROM uctest WHERE CURRENT OF c; -- fail, cursor on wrong table +ERROR: cursor "c" does not have a FOR UPDATE/SHARE reference to table "uctest" +ROLLBACK; +BEGIN; +DECLARE c CURSOR FOR SELECT * FROM tenk2 FOR SHARE; +FETCH 1 FROM c; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx +(1 row) + +DELETE FROM uctest WHERE CURRENT OF c; -- fail, cursor on wrong table +ERROR: cursor "c" does not have a FOR UPDATE/SHARE reference to table "uctest" +ROLLBACK; +BEGIN; +DECLARE c CURSOR FOR SELECT * FROM tenk1 JOIN tenk2 USING (unique1); +FETCH 1 FROM c; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx +(1 row) + +DELETE FROM tenk1 WHERE CURRENT OF c; -- fail, cursor is on a join +ERROR: cursor "c" is not a simply updatable scan of table "tenk1" +ROLLBACK; +BEGIN; +DECLARE c CURSOR FOR SELECT f1,count(*) FROM uctest GROUP BY f1; +FETCH 1 FROM c; + f1 | count +----+------- + 13 | 1 +(1 row) + +DELETE FROM uctest WHERE CURRENT OF c; -- fail, cursor is on aggregation +ERROR: cursor "c" is not a simply updatable scan of table "uctest" +ROLLBACK; +BEGIN; +DECLARE c1 CURSOR FOR SELECT f1,f2 FROM uctest; +DELETE FROM uctest WHERE CURRENT OF c1; -- fail, no current row +ERROR: cursor "c1" is not positioned on a row +ROLLBACK; +-- WHERE CURRENT OF may someday work with views, but today is not that day. +-- For now, just make sure it errors out cleanly. +CREATE VIEW ucview AS SELECT f1,f2 FROM uctest ORDER BY 1; +CREATE RULE ucrule AS ON DELETE TO ucview DO INSTEAD + DELETE FROM uctest WHERE f1 = OLD.f1; +BEGIN; +DECLARE c1 CURSOR FOR SELECT * FROM ucview; +FETCH FROM c1; + f1 | f2 +----+------- + 13 | three +(1 row) + +DELETE FROM ucview WHERE CURRENT OF c1; -- fail, views not supported +ERROR: WHERE CURRENT OF on a view is not implemented +ROLLBACK; +-- Make sure snapshot management works okay, per bug report in +-- 235395b90909301035v7228ce63q392931f15aa74b31@mail.gmail.com +BEGIN; +SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; +CREATE TABLE cursor (a int, b int) distribute by hash(b); +INSERT INTO cursor VALUES (1); +DECLARE c1 NO SCROLL CURSOR FOR SELECT * FROM cursor FOR UPDATE; +UPDATE cursor SET a = 2; +FETCH ALL FROM c1; + a +--- +(0 rows) + +COMMIT; +DROP TABLE cursor; +DROP VIEW ucview; +DROP TABLE ucchild; +DROP TABLE uctest; diff --git a/src/test/regress/expected/uuid_1.out b/src/test/regress/expected/uuid_1.out index 39ea1e952d..982f1dd7b2 100644 --- a/src/test/regress/expected/uuid_1.out +++ b/src/test/regress/expected/uuid_1.out @@ -5,147 +5,144 @@ CREATE TABLE guid1 guid_field UUID, text_field TEXT DEFAULT(now()) ); -ERROR: Postgres-XC does not support DEFAULT with non-immutable functions yet -DETAIL: The feature is not currently supported CREATE TABLE guid2 ( guid_field UUID, text_field TEXT DEFAULT(now()) ); -ERROR: Postgres-XC does not support DEFAULT with non-immutable functions yet -DETAIL: The feature is not currently supported -- inserting invalid data tests -- too long INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-1111-111111111111F'); -ERROR: relation "guid1" does not exist +ERROR: invalid input syntax for uuid: "11111111-1111-1111-1111-111111111111F" LINE 1: INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-111... - ^ + ^ -- too short INSERT INTO guid1(guid_field) VALUES('{11111111-1111-1111-1111-11111111111}'); -ERROR: relation "guid1" does not exist +ERROR: invalid input syntax for uuid: "{11111111-1111-1111-1111-11111111111}" LINE 1: INSERT INTO guid1(guid_field) VALUES('{11111111-1111-1111-11... - ^ + ^ -- valid data but invalid format INSERT INTO guid1(guid_field) VALUES('111-11111-1111-1111-1111-111111111111'); -ERROR: relation "guid1" does not exist +ERROR: invalid input syntax for uuid: "111-11111-1111-1111-1111-111111111111" LINE 1: INSERT INTO guid1(guid_field) VALUES('111-11111-1111-1111-11... - ^ + ^ INSERT INTO guid1(guid_field) VALUES('{22222222-2222-2222-2222-222222222222 '); -ERROR: relation "guid1" does not exist +ERROR: invalid input syntax for uuid: "{22222222-2222-2222-2222-222222222222 " LINE 1: INSERT INTO guid1(guid_field) VALUES('{22222222-2222-2222-22... - ^ + ^ -- invalid data INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-G111-111111111111'); -ERROR: relation "guid1" does not exist +ERROR: invalid input syntax for uuid: "11111111-1111-1111-G111-111111111111" LINE 1: INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-G11... - ^ + ^ INSERT INTO guid1(guid_field) VALUES('11+11111-1111-1111-1111-111111111111'); -ERROR: relation "guid1" does not exist +ERROR: invalid input syntax for uuid: "11+11111-1111-1111-1111-111111111111" LINE 1: INSERT INTO guid1(guid_field) VALUES('11+11111-1111-1111-111... - ^ + ^ --inserting three input formats INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-1111-111111111111'); -ERROR: relation "guid1" does not exist -LINE 1: INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-111... - ^ INSERT INTO guid1(guid_field) VALUES('{22222222-2222-2222-2222-222222222222}'); -ERROR: relation "guid1" does not exist -LINE 1: INSERT INTO guid1(guid_field) VALUES('{22222222-2222-2222-22... - ^ INSERT INTO guid1(guid_field) VALUES('3f3e3c3b3a3039383736353433a2313e'); -ERROR: relation "guid1" does not exist -LINE 1: INSERT INTO guid1(guid_field) VALUES('3f3e3c3b3a303938373635... - ^ -- retrieving the inserted data SELECT guid_field FROM guid1 ORDER BY guid_field; -ERROR: relation "guid1" does not exist -LINE 1: SELECT guid_field FROM guid1 ORDER BY guid_field; - ^ + guid_field +-------------------------------------- + 11111111-1111-1111-1111-111111111111 + 22222222-2222-2222-2222-222222222222 + 3f3e3c3b-3a30-3938-3736-353433a2313e +(3 rows) + -- ordering test SELECT guid_field FROM guid1 ORDER BY guid_field ASC; -ERROR: relation "guid1" does not exist -LINE 1: SELECT guid_field FROM guid1 ORDER BY guid_field ASC; - ^ + guid_field +-------------------------------------- + 11111111-1111-1111-1111-111111111111 + 22222222-2222-2222-2222-222222222222 + 3f3e3c3b-3a30-3938-3736-353433a2313e +(3 rows) + SELECT guid_field FROM guid1 ORDER BY guid_field DESC; -ERROR: relation "guid1" does not exist -LINE 1: SELECT guid_field FROM guid1 ORDER BY guid_field DESC; - ^ + guid_field +-------------------------------------- + 3f3e3c3b-3a30-3938-3736-353433a2313e + 22222222-2222-2222-2222-222222222222 + 11111111-1111-1111-1111-111111111111 +(3 rows) + -- = operator test SELECT COUNT(*) FROM guid1 WHERE guid_field = '3f3e3c3b-3a30-3938-3736-353433a2313e'; -ERROR: relation "guid1" does not exist -LINE 1: SELECT COUNT(*) FROM guid1 WHERE guid_field = '3f3e3c3b-3a30... - ^ + count +------- + 1 +(1 row) + -- <> operator test SELECT COUNT(*) FROM guid1 WHERE guid_field <> '11111111111111111111111111111111'; -ERROR: relation "guid1" does not exist -LINE 1: SELECT COUNT(*) FROM guid1 WHERE guid_field <> '111111111111... - ^ + count +------- + 2 +(1 row) + -- < operator test SELECT COUNT(*) FROM guid1 WHERE guid_field < '22222222-2222-2222-2222-222222222222'; -ERROR: relation "guid1" does not exist -LINE 1: SELECT COUNT(*) FROM guid1 WHERE guid_field < '22222222-2222... - ^ + count +------- + 1 +(1 row) + -- <= operator test SELECT COUNT(*) FROM guid1 WHERE guid_field <= '22222222-2222-2222-2222-222222222222'; -ERROR: relation "guid1" does not exist -LINE 1: SELECT COUNT(*) FROM guid1 WHERE guid_field <= '22222222-222... - ^ + count +------- + 2 +(1 row) + -- > operator test SELECT COUNT(*) FROM guid1 WHERE guid_field > '22222222-2222-2222-2222-222222222222'; -ERROR: relation "guid1" does not exist -LINE 1: SELECT COUNT(*) FROM guid1 WHERE guid_field > '22222222-2222... - ^ + count +------- + 1 +(1 row) + -- >= operator test SELECT COUNT(*) FROM guid1 WHERE guid_field >= '22222222-2222-2222-2222-222222222222'; -ERROR: relation "guid1" does not exist -LINE 1: SELECT COUNT(*) FROM guid1 WHERE guid_field >= '22222222-222... - ^ + count +------- + 2 +(1 row) + -- btree and hash index creation test CREATE INDEX guid1_btree ON guid1 USING BTREE (guid_field); -ERROR: relation "guid1" does not exist CREATE INDEX guid1_hash ON guid1 USING HASH (guid_field); -ERROR: relation "guid1" does not exist -- unique index test CREATE UNIQUE INDEX guid1_unique_BTREE ON guid1 USING BTREE (guid_field); -ERROR: relation "guid1" does not exist +ERROR: Unique index of partitioned table must contain the hash/modulo distribution column. -- should fail INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-1111-111111111111'); -ERROR: relation "guid1" does not exist -LINE 1: INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-111... - ^ -- check to see whether the new indexes are actually there SELECT count(*) FROM pg_class WHERE relkind='i' AND relname LIKE 'guid%'; count ------- - 0 + 2 (1 row) -- populating the test tables with additional records INSERT INTO guid1(guid_field) VALUES('44444444-4444-4444-4444-444444444444'); -ERROR: relation "guid1" does not exist -LINE 1: INSERT INTO guid1(guid_field) VALUES('44444444-4444-4444-444... - ^ INSERT INTO guid2(guid_field) VALUES('11111111-1111-1111-1111-111111111111'); -ERROR: relation "guid2" does not exist -LINE 1: INSERT INTO guid2(guid_field) VALUES('11111111-1111-1111-111... - ^ INSERT INTO guid2(guid_field) VALUES('{22222222-2222-2222-2222-222222222222}'); -ERROR: relation "guid2" does not exist -LINE 1: INSERT INTO guid2(guid_field) VALUES('{22222222-2222-2222-22... - ^ INSERT INTO guid2(guid_field) VALUES('3f3e3c3b3a3039383736353433a2313e'); -ERROR: relation "guid2" does not exist -LINE 1: INSERT INTO guid2(guid_field) VALUES('3f3e3c3b3a303938373635... - ^ -- join test SELECT COUNT(*) FROM guid1 g1 INNER JOIN guid2 g2 ON g1.guid_field = g2.guid_field; -ERROR: relation "guid1" does not exist -LINE 1: SELECT COUNT(*) FROM guid1 g1 INNER JOIN guid2 g2 ON g1.guid... - ^ + count +------- + 4 +(1 row) + SELECT COUNT(*) FROM guid1 g1 LEFT JOIN guid2 g2 ON g1.guid_field = g2.guid_field WHERE g2.guid_field IS NULL; -ERROR: relation "guid1" does not exist -LINE 1: SELECT COUNT(*) FROM guid1 g1 LEFT JOIN guid2 g2 ON g1.guid_... - ^ + count +------- + 1 +(1 row) + -- clean up DROP TABLE guid1, guid2 CASCADE; -ERROR: table "guid1" does not exist diff --git a/src/test/regress/output/constraints_1.source b/src/test/regress/output/constraints_1.source index bd04188535..e6af9d80f5 100644 --- a/src/test/regress/output/constraints_1.source +++ b/src/test/regress/output/constraints_1.source @@ -30,28 +30,19 @@ SELECT '' AS five, * FROM DEFAULT_TBL ORDER BY i,x,f; CREATE SEQUENCE DEFAULT_SEQ; CREATE TABLE DEFAULTEXPR_TBL (i1 int DEFAULT 100 + (200-199) * 2, i2 int DEFAULT nextval('default_seq')); -ERROR: Postgres-XC does not support DEFAULT with non-immutable functions yet -DETAIL: The feature is not currently supported INSERT INTO DEFAULTEXPR_TBL VALUES (-1, -2); -ERROR: relation "defaultexpr_tbl" does not exist -LINE 1: INSERT INTO DEFAULTEXPR_TBL VALUES (-1, -2); - ^ INSERT INTO DEFAULTEXPR_TBL (i1) VALUES (-3); -ERROR: relation "defaultexpr_tbl" does not exist -LINE 1: INSERT INTO DEFAULTEXPR_TBL (i1) VALUES (-3); - ^ INSERT INTO DEFAULTEXPR_TBL (i2) VALUES (-4); -ERROR: relation "defaultexpr_tbl" does not exist -LINE 1: INSERT INTO DEFAULTEXPR_TBL (i2) VALUES (-4); - ^ INSERT INTO DEFAULTEXPR_TBL (i2) VALUES (NULL); -ERROR: relation "defaultexpr_tbl" does not exist -LINE 1: INSERT INTO DEFAULTEXPR_TBL (i2) VALUES (NULL); - ^ SELECT '' AS four, * FROM DEFAULTEXPR_TBL ORDER BY i1,i2; -ERROR: relation "defaultexpr_tbl" does not exist -LINE 1: SELECT '' AS four, * FROM DEFAULTEXPR_TBL ORDER BY i1,i2; - ^ + four | i1 | i2 +------+-----+---- + | -3 | 1 + | -1 | -2 + | 102 | -4 + | 102 | +(4 rows) + -- syntax errors -- test for extraneous comma CREATE TABLE error_tbl (i int DEFAULT (100, )); @@ -120,16 +111,13 @@ CREATE TABLE INSERT_TBL (x INT DEFAULT nextval('insert_seq'), z INT DEFAULT -1 * currval('insert_seq'), CONSTRAINT INSERT_CON CHECK (x >= 3 AND y <> 'check failed' AND x < 8), CHECK (x + z = 0)); -ERROR: Postgres-XC does not support DEFAULT with non-immutable functions yet -DETAIL: The feature is not currently supported INSERT INTO INSERT_TBL(x,z) VALUES (2, -2); -ERROR: relation "insert_tbl" does not exist -LINE 1: INSERT INTO INSERT_TBL(x,z) VALUES (2, -2); - ^ +ERROR: new row for relation "insert_tbl" violates check constraint "insert_con" SELECT '' AS zero, * FROM INSERT_TBL order by x,y,z; -ERROR: relation "insert_tbl" does not exist -LINE 1: SELECT '' AS zero, * FROM INSERT_TBL order by x,y,z; - ^ + zero | x | y | z +------+---+---+--- +(0 rows) + SELECT 'one' AS one, nextval('insert_seq'); one | nextval -----+--------- @@ -137,173 +125,147 @@ SELECT 'one' AS one, nextval('insert_seq'); (1 row) INSERT INTO INSERT_TBL(y) VALUES ('Y'); -ERROR: relation "insert_tbl" does not exist -LINE 1: INSERT INTO INSERT_TBL(y) VALUES ('Y'); - ^ +ERROR: new row for relation "insert_tbl" violates check constraint "insert_con" INSERT INTO INSERT_TBL(y) VALUES ('Y'); -ERROR: relation "insert_tbl" does not exist -LINE 1: INSERT INTO INSERT_TBL(y) VALUES ('Y'); - ^ INSERT INTO INSERT_TBL(x,z) VALUES (1, -2); -ERROR: relation "insert_tbl" does not exist -LINE 1: INSERT INTO INSERT_TBL(x,z) VALUES (1, -2); - ^ +ERROR: new row for relation "insert_tbl" violates check constraint "insert_tbl_check" INSERT INTO INSERT_TBL(z,x) VALUES (-7, 7); -ERROR: relation "insert_tbl" does not exist -LINE 1: INSERT INTO INSERT_TBL(z,x) VALUES (-7, 7); - ^ INSERT INTO INSERT_TBL VALUES (5, 'check failed', -5); -ERROR: relation "insert_tbl" does not exist -LINE 1: INSERT INTO INSERT_TBL VALUES (5, 'check failed', -5); - ^ +ERROR: new row for relation "insert_tbl" violates check constraint "insert_con" INSERT INTO INSERT_TBL VALUES (7, '!check failed', -7); -ERROR: relation "insert_tbl" does not exist -LINE 1: INSERT INTO INSERT_TBL VALUES (7, '!check failed', -7); - ^ INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-'); -ERROR: relation "insert_tbl" does not exist -LINE 1: INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-'); - ^ SELECT '' AS four, * FROM INSERT_TBL order by x,y,z; -ERROR: relation "insert_tbl" does not exist -LINE 1: SELECT '' AS four, * FROM INSERT_TBL order by x,y,z; - ^ + four | x | y | z +------+---+---------------+---- + | 3 | Y | -3 + | 4 | -!NULL- | -4 + | 7 | !check failed | -7 + | 7 | -NULL- | -7 +(4 rows) + INSERT INTO INSERT_TBL(y,z) VALUES ('check failed', 4); -ERROR: relation "insert_tbl" does not exist -LINE 1: INSERT INTO INSERT_TBL(y,z) VALUES ('check failed', 4); - ^ +ERROR: new row for relation "insert_tbl" violates check constraint "insert_tbl_check" INSERT INTO INSERT_TBL(x,y) VALUES (5, 'check failed'); -ERROR: relation "insert_tbl" does not exist -LINE 1: INSERT INTO INSERT_TBL(x,y) VALUES (5, 'check failed'); - ^ +ERROR: new row for relation "insert_tbl" violates check constraint "insert_con" INSERT INTO INSERT_TBL(x,y) VALUES (5, '!check failed'); -ERROR: relation "insert_tbl" does not exist -LINE 1: INSERT INTO INSERT_TBL(x,y) VALUES (5, '!check failed'); - ^ INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-'); -ERROR: relation "insert_tbl" does not exist -LINE 1: INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-'); - ^ SELECT '' AS six, * FROM INSERT_TBL order by x,y,z; -ERROR: relation "insert_tbl" does not exist -LINE 1: SELECT '' AS six, * FROM INSERT_TBL order by x,y,z; - ^ + six | x | y | z +-----+---+---------------+---- + | 3 | Y | -3 + | 4 | -!NULL- | -4 + | 5 | !check failed | -5 + | 6 | -!NULL- | -6 + | 7 | !check failed | -7 + | 7 | -NULL- | -7 +(6 rows) + SELECT 'seven' AS one, nextval('insert_seq'); one | nextval -------+--------- - seven | 2 + seven | 7 (1 row) INSERT INTO INSERT_TBL(y) VALUES ('Y'); -ERROR: relation "insert_tbl" does not exist -LINE 1: INSERT INTO INSERT_TBL(y) VALUES ('Y'); - ^ +ERROR: new row for relation "insert_tbl" violates check constraint "insert_con" SELECT 'eight' AS one, currval('insert_seq'); one | currval -------+--------- - eight | 2 + eight | 8 (1 row) -- According to SQL92, it is OK to insert a record that gives rise to NULL -- constraint-condition results. Postgres used to reject this, but it -- was wrong: INSERT INTO INSERT_TBL VALUES (null, null, null); -ERROR: relation "insert_tbl" does not exist -LINE 1: INSERT INTO INSERT_TBL VALUES (null, null, null); - ^ SELECT '' AS nine, * FROM INSERT_TBL order by x,y,z; -ERROR: relation "insert_tbl" does not exist -LINE 1: SELECT '' AS nine, * FROM INSERT_TBL order by x,y,z; - ^ + nine | x | y | z +------+---+---------------+---- + | 3 | Y | -3 + | 4 | -!NULL- | -4 + | 5 | !check failed | -5 + | 6 | -!NULL- | -6 + | 7 | !check failed | -7 + | 7 | -NULL- | -7 + | | | +(7 rows) + -- -- Check inheritance of defaults and constraints -- CREATE TABLE INSERT_CHILD (cx INT default 42, cy INT CHECK (cy > x)) INHERITS (INSERT_TBL); -ERROR: relation "insert_tbl" does not exist INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,11); -ERROR: relation "insert_child" does not exist -LINE 1: INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,11); - ^ INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,6); -ERROR: relation "insert_child" does not exist -LINE 1: INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,6); - ^ +ERROR: new row for relation "insert_child" violates check constraint "insert_child_check" INSERT INTO INSERT_CHILD(x,z,cy) VALUES (6,-7,7); -ERROR: relation "insert_child" does not exist -LINE 1: INSERT INTO INSERT_CHILD(x,z,cy) VALUES (6,-7,7); - ^ +ERROR: new row for relation "insert_child" violates check constraint "insert_tbl_check" INSERT INTO INSERT_CHILD(x,y,z,cy) VALUES (6,'check failed',-6,7); -ERROR: relation "insert_child" does not exist -LINE 1: INSERT INTO INSERT_CHILD(x,y,z,cy) VALUES (6,'check failed',... - ^ +ERROR: new row for relation "insert_child" violates check constraint "insert_con" SELECT * FROM INSERT_CHILD order by 1,2,3; -ERROR: relation "insert_child" does not exist -LINE 1: SELECT * FROM INSERT_CHILD order by 1,2,3; - ^ + x | y | z | cx | cy +---+--------+----+----+---- + 7 | -NULL- | -7 | 42 | 11 +(1 row) + DROP TABLE INSERT_CHILD; -ERROR: table "insert_child" does not exist -- -- Check constraints on INSERT INTO -- DELETE FROM INSERT_TBL; -ERROR: relation "insert_tbl" does not exist -LINE 1: DELETE FROM INSERT_TBL; - ^ ALTER SEQUENCE INSERT_SEQ RESTART WITH 4; CREATE TABLE tmp (xd INT, yd TEXT, zd INT); INSERT INTO tmp VALUES (null, 'Y', null); INSERT INTO tmp VALUES (5, '!check failed', null); INSERT INTO tmp VALUES (null, 'try again', null); INSERT INTO INSERT_TBL(y) select yd from tmp; -ERROR: relation "insert_tbl" does not exist -LINE 1: INSERT INTO INSERT_TBL(y) select yd from tmp; - ^ SELECT '' AS three, * FROM INSERT_TBL order by x,y,z; -ERROR: relation "insert_tbl" does not exist -LINE 1: SELECT '' AS three, * FROM INSERT_TBL order by x,y,z; - ^ + three | x | y | z +-------+---+---------------+---- + | 4 | Y | -4 + | 5 | !check failed | -5 + | 6 | try again | -6 +(3 rows) + INSERT INTO INSERT_TBL SELECT * FROM tmp WHERE yd = 'try again'; -ERROR: relation "insert_tbl" does not exist -LINE 1: INSERT INTO INSERT_TBL SELECT * FROM tmp WHERE yd = 'try aga... - ^ INSERT INTO INSERT_TBL(y,z) SELECT yd, -7 FROM tmp WHERE yd = 'try again'; -ERROR: relation "insert_tbl" does not exist -LINE 1: INSERT INTO INSERT_TBL(y,z) SELECT yd, -7 FROM tmp WHERE yd ... - ^ INSERT INTO INSERT_TBL(y,z) SELECT yd, -8 FROM tmp WHERE yd = 'try again'; -ERROR: relation "insert_tbl" does not exist -LINE 1: INSERT INTO INSERT_TBL(y,z) SELECT yd, -8 FROM tmp WHERE yd ... - ^ +ERROR: new row for relation "insert_tbl" violates check constraint "insert_con" SELECT '' AS four, * FROM INSERT_TBL order by x,y,z; -ERROR: relation "insert_tbl" does not exist -LINE 1: SELECT '' AS four, * FROM INSERT_TBL order by x,y,z; - ^ + four | x | y | z +------+---+---------------+---- + | 4 | Y | -4 + | 5 | !check failed | -5 + | 6 | try again | -6 + | 7 | try again | -7 + | | try again | + | | try again | +(6 rows) + DROP TABLE tmp; -- -- Check constraints on UPDATE -- UPDATE INSERT_TBL SET x = NULL WHERE x = 5; -ERROR: relation "insert_tbl" does not exist -LINE 1: UPDATE INSERT_TBL SET x = NULL WHERE x = 5; - ^ +ERROR: Partition column can't be updated in current version UPDATE INSERT_TBL SET x = 6 WHERE x = 6; -ERROR: relation "insert_tbl" does not exist -LINE 1: UPDATE INSERT_TBL SET x = 6 WHERE x = 6; - ^ +ERROR: Partition column can't be updated in current version UPDATE INSERT_TBL SET x = -z, z = -x; -ERROR: relation "insert_tbl" does not exist -LINE 1: UPDATE INSERT_TBL SET x = -z, z = -x; - ^ +ERROR: Partition column can't be updated in current version UPDATE INSERT_TBL SET x = z, z = x; -ERROR: relation "insert_tbl" does not exist -LINE 1: UPDATE INSERT_TBL SET x = z, z = x; - ^ +ERROR: Partition column can't be updated in current version SELECT * FROM INSERT_TBL order by x,y,z; -ERROR: relation "insert_tbl" does not exist -LINE 1: SELECT * FROM INSERT_TBL order by x,y,z; - ^ + x | y | z +---+---------------+---- + 4 | Y | -4 + 5 | !check failed | -5 + 6 | try again | -6 + 7 | try again | -7 + | try again | + | try again | +(6 rows) + -- DROP TABLE INSERT_TBL; -- -- Check constraints on COPY FROM diff --git a/src/test/regress/sql/portals.sql b/src/test/regress/sql/portals.sql index bccc81f487..541ad77a85 100644 --- a/src/test/regress/sql/portals.sql +++ b/src/test/regress/sql/portals.sql @@ -327,7 +327,7 @@ SELECT f1,f2 FROM uctest ORDER BY f1; -- Check DELETE WHERE CURRENT BEGIN; -DECLARE c1 CURSOR FOR SELECT f1,f2 FROM uctest ORDER BY f1; +DECLARE c1 SCROLL CURSOR FOR SELECT f1,f2 FROM uctest ORDER BY f1; FETCH 2 FROM c1; DELETE FROM uctest WHERE CURRENT OF c1; -- should show deletion @@ -352,7 +352,7 @@ SELECT f1,f2 FROM uctest ORDER BY f1; -- Check repeated-update and update-then-delete cases BEGIN; -DECLARE c1 CURSOR FOR SELECT f1,f2 FROM uctest; +DECLARE c1 SCROLL CURSOR FOR SELECT f1,f2 FROM uctest; FETCH c1; UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; SELECT f1,f2 FROM uctest ORDER BY 1; @@ -408,17 +408,17 @@ SELECT f1,f2 FROM uctest ORDER BY f1; -- Can update from a self-join, but only if FOR UPDATE says which to use BEGIN; -DECLARE c1 CURSOR FOR SELECT a.f1,a.f2 FROM uctest a, uctest b WHERE a.f1 = b.f1 + 5 ORDER BY 1; +DECLARE c1 CURSOR FOR SELECT a.f1,a.f2,b.f1,b.f2 FROM uctest a, uctest b WHERE a.f1 = b.f1 + 5 ORDER BY 1; FETCH 1 FROM c1; UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; -- fail ROLLBACK; BEGIN; -DECLARE c1 CURSOR FOR SELECT a.f1,a.f2 FROM uctest a, uctest b WHERE a.f1 = b.f1 + 5 ORDER BY 1 FOR UPDATE; +DECLARE c1 CURSOR FOR SELECT a.f1,a.f2, b.f1, b.f2 FROM uctest a, uctest b WHERE a.f1 = b.f1 + 5 ORDER BY 1 FOR UPDATE; FETCH 1 FROM c1; UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; -- fail ROLLBACK; BEGIN; -DECLARE c1 CURSOR FOR SELECT a.f1,a.f2 FROM uctest a, uctest b WHERE a.f1 = b.f1 + 5 ORDER BY 1 FOR SHARE OF a; +DECLARE c1 CURSOR FOR SELECT a.f1,a.f2,b.f1,b.f2 FROM uctest a, uctest b WHERE a.f1 = b.f1 + 5 ORDER BY 1 FOR SHARE OF a; FETCH 1 FROM c1; UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; SELECT f1,f2 FROM uctest ORDER BY f1; @@ -427,22 +427,26 @@ ROLLBACK; -- Check various error cases DELETE FROM uctest WHERE CURRENT OF c1; -- fail, no such cursor -DECLARE cx CURSOR WITH HOLD FOR SELECT f1,f2 FROM uctest ORDER BY 1; +DECLARE cx CURSOR WITH HOLD FOR SELECT f1,f2 FROM uctest; DELETE FROM uctest WHERE CURRENT OF cx; -- fail, can't use held cursor BEGIN; DECLARE c CURSOR FOR SELECT * FROM tenk2 ORDER BY unique2; +FETCH 1 FROM c; DELETE FROM uctest WHERE CURRENT OF c; -- fail, cursor on wrong table ROLLBACK; BEGIN; -DECLARE c CURSOR FOR SELECT * FROM tenk2 ORDER BY unique2 FOR SHARE; +DECLARE c CURSOR FOR SELECT * FROM tenk2 FOR SHARE; +FETCH 1 FROM c; DELETE FROM uctest WHERE CURRENT OF c; -- fail, cursor on wrong table ROLLBACK; BEGIN; DECLARE c CURSOR FOR SELECT * FROM tenk1 JOIN tenk2 USING (unique1); +FETCH 1 FROM c; DELETE FROM tenk1 WHERE CURRENT OF c; -- fail, cursor is on a join ROLLBACK; BEGIN; DECLARE c CURSOR FOR SELECT f1,count(*) FROM uctest GROUP BY f1; +FETCH 1 FROM c; DELETE FROM uctest WHERE CURRENT OF c; -- fail, cursor is on aggregation ROLLBACK; BEGIN; @@ -465,7 +469,7 @@ ROLLBACK; -- 235395b90909301035v7228ce63q392931f15aa74b31@mail.gmail.com BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -CREATE TABLE cursor (a int); +CREATE TABLE cursor (a int, b int) distribute by hash(b); INSERT INTO cursor VALUES (1); DECLARE c1 NO SCROLL CURSOR FOR SELECT * FROM cursor FOR UPDATE; UPDATE cursor SET a = 2; |