diff options
Diffstat (limited to 'src/backend/optimizer')
| -rw-r--r-- | src/backend/optimizer/plan/createplan.c | 26 | ||||
| -rw-r--r-- | src/backend/optimizer/plan/planner.c | 27 | ||||
| -rw-r--r-- | src/backend/optimizer/plan/setrefs.c | 52 | ||||
| -rw-r--r-- | src/backend/optimizer/plan/subselect.c | 4 | ||||
| -rw-r--r-- | src/backend/optimizer/prep/prepjointree.c | 6 | ||||
| -rw-r--r-- | src/backend/optimizer/prep/preptlist.c | 13 | ||||
| -rw-r--r-- | src/backend/optimizer/util/plancat.c | 352 |
7 files changed, 470 insertions, 10 deletions
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index eeb2a417643..3246332d6e3 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -4868,7 +4868,7 @@ make_modifytable(PlannerInfo *root, Index nominalRelation, List *resultRelations, List *subplans, List *withCheckOptionLists, List *returningLists, - List *rowMarks, int epqParam) + List *rowMarks, OnConflictExpr *onconflict, int epqParam) { ModifyTable *node = makeNode(ModifyTable); Plan *plan = &node->plan; @@ -4918,6 +4918,30 @@ make_modifytable(PlannerInfo *root, node->resultRelations = resultRelations; node->resultRelIndex = -1; /* will be set correctly in setrefs.c */ node->plans = subplans; + if (!onconflict) + { + node->onConflictAction = ONCONFLICT_NONE; + node->onConflictSet = NIL; + node->onConflictWhere = NULL; + node->arbiterIndexes = NIL; + } + else + { + node->onConflictAction = onconflict->action; + node->onConflictSet = onconflict->onConflictSet; + node->onConflictWhere = onconflict->onConflictWhere; + + /* + * If a set of unique index inference elements was provided (an + * INSERT...ON CONFLICT "inference specification"), then infer + * appropriate unique indexes (or throw an error if none are + * available). + */ + node->arbiterIndexes = infer_arbiter_indexes(root); + + node->exclRelRTI = onconflict->exclRelIndex; + node->exclRelTlist = onconflict->exclRelTlist; + } node->withCheckOptionLists = withCheckOptionLists; node->returningLists = returningLists; node->rowMarks = rowMarks; diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index ea4d4c55cbd..c80d45acaa9 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -243,6 +243,8 @@ standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams) result->queryId = parse->queryId; result->hasReturning = (parse->returningList != NIL); result->hasModifyingCTE = parse->hasModifyingCTE; + result->isUpsert = + (parse->onConflict && parse->onConflict->action == ONCONFLICT_UPDATE); result->canSetTag = parse->canSetTag; result->transientPlan = glob->transientPlan; result->planTree = top_plan; @@ -462,6 +464,17 @@ subquery_planner(PlannerGlobal *glob, Query *parse, parse->limitCount = preprocess_expression(root, parse->limitCount, EXPRKIND_LIMIT); + if (parse->onConflict) + { + parse->onConflict->onConflictSet = (List *) + preprocess_expression(root, (Node *) parse->onConflict->onConflictSet, + EXPRKIND_TARGET); + + parse->onConflict->onConflictWhere = + preprocess_expression(root, (Node *) parse->onConflict->onConflictWhere, + EXPRKIND_QUAL); + } + root->append_rel_list = (List *) preprocess_expression(root, (Node *) root->append_rel_list, EXPRKIND_APPINFO); @@ -612,6 +625,7 @@ subquery_planner(PlannerGlobal *glob, Query *parse, withCheckOptionLists, returningLists, rowMarks, + parse->onConflict, SS_assign_special_param(root)); } } @@ -802,6 +816,8 @@ inheritance_planner(PlannerInfo *root) List *rowMarks; ListCell *lc; + Assert(parse->commandType != CMD_INSERT); + /* * We generate a modified instance of the original Query for each target * relation, plan that, and put all the plans into a list that will be @@ -1046,6 +1062,8 @@ inheritance_planner(PlannerInfo *root) if (parse->returningList) returningLists = lappend(returningLists, subroot.parse->returningList); + + Assert(!parse->onConflict); } /* Mark result as unordered (probably unnecessary) */ @@ -1095,6 +1113,7 @@ inheritance_planner(PlannerInfo *root) withCheckOptionLists, returningLists, rowMarks, + NULL, SS_assign_special_param(root)); } @@ -1228,6 +1247,7 @@ grouping_planner(PlannerInfo *root, double tuple_fraction) bool use_hashed_grouping = false; WindowFuncLists *wflists = NULL; List *activeWindows = NIL; + OnConflictExpr *onconfl; MemSet(&agg_costs, 0, sizeof(AggClauseCosts)); @@ -1242,6 +1262,13 @@ grouping_planner(PlannerInfo *root, double tuple_fraction) /* Preprocess targetlist */ tlist = preprocess_targetlist(root, tlist); + onconfl = parse->onConflict; + if (onconfl) + onconfl->onConflictSet = + preprocess_onconflict_targetlist(onconfl->onConflictSet, + parse->resultRelation, + parse->rtable); + /* * Expand any rangetable entries that have security barrier quals. * This may add new security barrier subquery RTEs to the rangetable. diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index b7d6ff11223..612d32571af 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -739,7 +739,35 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset) splan->plan.targetlist = copyObject(linitial(newRL)); } + /* + * We treat ModifyTable with ON CONFLICT as a form of 'pseudo + * join', where the inner side is the EXLUDED tuple. Therefore + * use fix_join_expr to setup the relevant variables to + * INNER_VAR. We explicitly don't create any OUTER_VARs as + * those are already used by RETURNING and it seems better to + * be non-conflicting. + */ + if (splan->onConflictSet) + { + indexed_tlist *itlist; + + itlist = build_tlist_index(splan->exclRelTlist); + + splan->onConflictSet = + fix_join_expr(root, splan->onConflictSet, + NULL, itlist, + linitial_int(splan->resultRelations), + rtoffset); + + splan->onConflictWhere = (Node *) + fix_join_expr(root, (List *) splan->onConflictWhere, + NULL, itlist, + linitial_int(splan->resultRelations), + rtoffset); + } + splan->nominalRelation += rtoffset; + splan->exclRelRTI += rtoffset; foreach(l, splan->resultRelations) { @@ -1846,7 +1874,8 @@ search_indexed_tlist_for_sortgroupref(Node *node, * inner_itlist = NULL and acceptable_rel = the ID of the target relation. * * 'clauses' is the targetlist or list of join clauses - * 'outer_itlist' is the indexed target list of the outer join relation + * 'outer_itlist' is the indexed target list of the outer join relation, + * or NULL * 'inner_itlist' is the indexed target list of the inner join relation, * or NULL * 'acceptable_rel' is either zero or the rangetable index of a relation @@ -1886,12 +1915,17 @@ fix_join_expr_mutator(Node *node, fix_join_expr_context *context) Var *var = (Var *) node; /* First look for the var in the input tlists */ - newvar = search_indexed_tlist_for_var(var, - context->outer_itlist, - OUTER_VAR, - context->rtoffset); - if (newvar) - return (Node *) newvar; + if (context->outer_itlist) + { + newvar = search_indexed_tlist_for_var(var, + context->outer_itlist, + OUTER_VAR, + context->rtoffset); + if (newvar) + return (Node *) newvar; + } + + /* Then in the outer */ if (context->inner_itlist) { newvar = search_indexed_tlist_for_var(var, @@ -1920,7 +1954,7 @@ fix_join_expr_mutator(Node *node, fix_join_expr_context *context) PlaceHolderVar *phv = (PlaceHolderVar *) node; /* See if the PlaceHolderVar has bubbled up from a lower plan node */ - if (context->outer_itlist->has_ph_vars) + if (context->outer_itlist && context->outer_itlist->has_ph_vars) { newvar = search_indexed_tlist_for_non_var((Node *) phv, context->outer_itlist, @@ -1943,7 +1977,7 @@ fix_join_expr_mutator(Node *node, fix_join_expr_context *context) if (IsA(node, Param)) return fix_param_node(context->root, (Param *) node); /* Try matching more complex expressions too, if tlists have any */ - if (context->outer_itlist->has_non_vars) + if (context->outer_itlist && context->outer_itlist->has_non_vars) { newvar = search_indexed_tlist_for_non_var(node, context->outer_itlist, diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c index acfd0bcfbe5..0220672fc43 100644 --- a/src/backend/optimizer/plan/subselect.c +++ b/src/backend/optimizer/plan/subselect.c @@ -2340,6 +2340,10 @@ finalize_plan(PlannerInfo *root, Plan *plan, Bitmapset *valid_params, locally_added_param); finalize_primnode((Node *) mtplan->returningLists, &context); + finalize_primnode((Node *) mtplan->onConflictSet, + &context); + finalize_primnode((Node *) mtplan->onConflictWhere, + &context); foreach(l, mtplan->plans) { context.paramids = diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c index 50acfe40e97..4f0dc80d025 100644 --- a/src/backend/optimizer/prep/prepjointree.c +++ b/src/backend/optimizer/prep/prepjointree.c @@ -1030,6 +1030,9 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte, pullup_replace_vars((Node *) parse->targetList, &rvcontext); parse->returningList = (List *) pullup_replace_vars((Node *) parse->returningList, &rvcontext); + if (parse->onConflict) + parse->onConflict->onConflictSet = (List *) + pullup_replace_vars((Node *) parse->onConflict->onConflictSet, &rvcontext); replace_vars_in_jointree((Node *) parse->jointree, &rvcontext, lowest_nulling_outer_join); Assert(parse->setOperations == NULL); @@ -1605,6 +1608,9 @@ pull_up_simple_values(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte) pullup_replace_vars((Node *) parse->targetList, &rvcontext); parse->returningList = (List *) pullup_replace_vars((Node *) parse->returningList, &rvcontext); + if (parse->onConflict) + parse->onConflict->onConflictSet = (List *) + pullup_replace_vars((Node *) parse->onConflict->onConflictSet, &rvcontext); replace_vars_in_jointree((Node *) parse->jointree, &rvcontext, NULL); Assert(parse->setOperations == NULL); parse->havingQual = pullup_replace_vars(parse->havingQual, &rvcontext); diff --git a/src/backend/optimizer/prep/preptlist.c b/src/backend/optimizer/prep/preptlist.c index 580c8467703..6b0c689e0c9 100644 --- a/src/backend/optimizer/prep/preptlist.c +++ b/src/backend/optimizer/prep/preptlist.c @@ -181,6 +181,19 @@ preprocess_targetlist(PlannerInfo *root, List *tlist) return tlist; } +/* + * preprocess_onconflict_targetlist + * Process ON CONFLICT SET targetlist. + * + * Returns the new targetlist. + */ +List * +preprocess_onconflict_targetlist(List *tlist, int result_relation, List *range_table) +{ + return expand_targetlist(tlist, CMD_UPDATE, result_relation, range_table); +} + + /***************************************************************************** * * TARGETLIST EXPANSION diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c index 068ab39dd43..8bcc5064a37 100644 --- a/src/backend/optimizer/util/plancat.c +++ b/src/backend/optimizer/util/plancat.c @@ -25,6 +25,7 @@ #include "access/transam.h" #include "access/xlog.h" #include "catalog/catalog.h" +#include "catalog/dependency.h" #include "catalog/heap.h" #include "foreign/fdwapi.h" #include "miscadmin.h" @@ -50,6 +51,8 @@ int constraint_exclusion = CONSTRAINT_EXCLUSION_PARTITION; get_relation_info_hook_type get_relation_info_hook = NULL; +static bool infer_collation_opclass_match(InferenceElem *elem, Relation idxRel, + Bitmapset *inferAttrs, List *idxExprs); static int32 get_rel_data_width(Relation rel, int32 *attr_widths); static List *get_relation_constraints(PlannerInfo *root, Oid relationObjectId, RelOptInfo *rel, @@ -400,6 +403,355 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent, } /* + * infer_arbiter_indexes - + * Determine the unique indexes used to arbitrate speculative insertion. + * + * Uses user-supplied inference clause expressions and predicate to match a + * unique index from those defined and ready on the heap relation (target). + * An exact match is required on columns/expressions (although they can appear + * in any order). However, the predicate given by the user need only restrict + * insertion to a subset of some part of the table covered by some particular + * unique index (in particular, a partial unique index) in order to be + * inferred. + * + * The implementation does not consider which B-Tree operator class any + * particular available unique index attribute uses, unless one was specified + * in the inference specification. The same is true of collations. In + * particular, there is no system dependency on the default operator class for + * the purposes of inference. If no opclass (or collation) is specified, then + * all matching indexes (that may or may not match the default in terms of + * each attribute opclass/collation) are used for inference. + */ +List * +infer_arbiter_indexes(PlannerInfo *root) +{ + OnConflictExpr *onconflict = root->parse->onConflict; + /* Iteration state */ + Relation relation; + Oid relationObjectId; + Oid indexOidFromConstraint = InvalidOid; + List *indexList; + ListCell *l; + + /* Normalized inference attributes and inference expressions: */ + Bitmapset *inferAttrs = NULL; + List *inferElems = NIL; + + /* Result */ + List *candidates = NIL; + + /* + * Quickly return NIL for ON CONFLICT DO NOTHING without an inference + * specification or named constraint. ON CONFLICT DO UPDATE statements + * must always provide one or the other (but parser ought to have caught + * that already). + */ + if (onconflict->arbiterElems == NIL && + onconflict->constraint == InvalidOid) + return NIL; + + /* + * We need not lock the relation since it was already locked, either by + * the rewriter or when expand_inherited_rtentry() added it to the query's + * rangetable. + */ + relationObjectId = rt_fetch(root->parse->resultRelation, + root->parse->rtable)->relid; + + relation = heap_open(relationObjectId, NoLock); + + /* + * Build normalized/BMS representation of plain indexed attributes, as + * well as direct list of inference elements. This is required for + * matching the cataloged definition of indexes. + */ + foreach(l, onconflict->arbiterElems) + { + InferenceElem *elem; + Var *var; + int attno; + + elem = (InferenceElem *) lfirst(l); + + /* + * Parse analysis of inference elements performs full parse analysis + * of Vars, even for non-expression indexes (in contrast with utility + * command related use of IndexElem). However, indexes are cataloged + * with simple attribute numbers for non-expression indexes. Those + * are handled later. + */ + if (!IsA(elem->expr, Var)) + { + inferElems = lappend(inferElems, elem->expr); + continue; + } + + var = (Var *) elem->expr; + attno = var->varattno; + + if (attno < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_COLUMN_REFERENCE), + errmsg("system columns cannot be used in an ON CONFLICT clause"))); + else if (attno == 0) + elog(ERROR, "whole row unique index inference specifications are not valid"); + + inferAttrs = bms_add_member(inferAttrs, attno); + } + + /* + * Lookup named constraint's index. This is not immediately returned + * because some additional sanity checks are required. + */ + if (onconflict->constraint != InvalidOid) + { + indexOidFromConstraint = get_constraint_index(onconflict->constraint); + + if (indexOidFromConstraint == InvalidOid) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("constraint in ON CONFLICT clause has no associated index"))); + } + + indexList = RelationGetIndexList(relation); + + /* + * Using that representation, iterate through the list of indexes on the + * target relation to try and find a match + */ + foreach(l, indexList) + { + Oid indexoid = lfirst_oid(l); + Relation idxRel; + Form_pg_index idxForm; + Bitmapset *indexedAttrs = NULL; + List *idxExprs; + List *predExprs; + List *whereExplicit; + AttrNumber natt; + ListCell *el; + + /* + * Extract info from the relation descriptor for the index. We know + * that this is a target, so get lock type it is known will ultimately + * be required by the executor. + * + * Let executor complain about !indimmediate case directly, because + * enforcement needs to occur there anyway when an inference clause is + * omitted. + */ + idxRel = index_open(indexoid, RowExclusiveLock); + idxForm = idxRel->rd_index; + + if (!IndexIsValid(idxForm)) + goto next; + + /* + * If the index is valid, but cannot yet be used, ignore it. See + * src/backend/access/heap/README.HOT for discussion. + */ + if (idxForm->indcheckxmin && + !TransactionIdPrecedes(HeapTupleHeaderGetXmin(idxRel->rd_indextuple->t_data), + TransactionXmin)) + goto next; + + /* + * Look for match on "ON constraint_name" variant, which may not be + * unique constraint. This can only be a constraint name. + */ + if (indexOidFromConstraint == idxForm->indexrelid) + { + if (!idxForm->indisunique && onconflict->action == ONCONFLICT_UPDATE) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("ON CONFLICT DO UPDATE not supported with exclusion constraints"))); + + list_free(indexList); + index_close(idxRel, NoLock); + heap_close(relation, NoLock); + candidates = lappend_oid(candidates, idxForm->indexrelid); + return candidates; + } + else if (indexOidFromConstraint != InvalidOid) + { + /* No point in further work for index in named constraint case */ + goto next; + } + + /* + * Only considering conventional inference at this point (not named + * constraints), so index under consideration can be immediately + * skipped if it's not unique + */ + if (!idxForm->indisunique) + goto next; + + /* Build BMS representation of cataloged index attributes */ + for (natt = 0; natt < idxForm->indnatts; natt++) + { + int attno = idxRel->rd_index->indkey.values[natt]; + + if (attno < 0) + elog(ERROR, "system column in index"); + + if (attno != 0) + indexedAttrs = bms_add_member(indexedAttrs, attno); + } + + /* Non-expression attributes (if any) must match */ + if (!bms_equal(indexedAttrs, inferAttrs)) + goto next; + + /* Expression attributes (if any) must match */ + idxExprs = RelationGetIndexExpressions(idxRel); + foreach(el, onconflict->arbiterElems) + { + InferenceElem *elem = (InferenceElem *) lfirst(el); + + /* + * Ensure that collation/opclass aspects of inference expression + * element match. Even though this loop is primarily concerned + * with matching expressions, it is a convenient point to check + * this for both expressions and ordinary (non-expression) + * attributes appearing as inference elements. + */ + if (!infer_collation_opclass_match(elem, idxRel, inferAttrs, + idxExprs)) + goto next; + + /* + * Plain Vars don't factor into count of expression elements, and + * the question of whether or not they satisfy the index + * definition has already been considered (they must). + */ + if (IsA(elem->expr, Var)) + continue; + + /* + * Might as well avoid redundant check in the rare cases where + * infer_collation_opclass_match() is required to do real work. + * Otherwise, check that element expression appears in cataloged + * index definition. + */ + if (elem->infercollid != InvalidOid || + elem->inferopfamily != InvalidOid || + list_member(idxExprs, elem->expr)) + continue; + + goto next; + } + + /* + * Now that all inference elements were matched, ensure that the + * expression elements from inference clause are not missing any + * cataloged expressions. This does the right thing when unique + * indexes redundantly repeat the same attribute, or if attributes + * redundantly appear multiple times within an inference clause. + */ + if (list_difference(idxExprs, inferElems) != NIL) + goto next; + + /* + * Any user-supplied ON CONFLICT unique index inference WHERE clause + * need only be implied by the cataloged index definitions predicate. + */ + predExprs = RelationGetIndexPredicate(idxRel); + whereExplicit = make_ands_implicit((Expr *) onconflict->arbiterWhere); + + if (!predicate_implied_by(predExprs, whereExplicit)) + goto next; + + candidates = lappend_oid(candidates, idxForm->indexrelid); +next: + index_close(idxRel, NoLock); + } + + list_free(indexList); + heap_close(relation, NoLock); + + if (candidates == NIL) + ereport(ERROR, + (errcode(ERRCODE_INVALID_COLUMN_REFERENCE), + errmsg("there is no unique or exclusion constraint matching the ON CONFLICT specification"))); + + return candidates; +} + +/* + * infer_collation_opclass_match - ensure infer element opclass/collation match + * + * Given unique index inference element from inference specification, if + * collation was specified, or if opclass (represented here as opfamily + + * opcintype) was specified, verify that there is at least one matching + * indexed attribute (occasionally, there may be more). Skip this in the + * common case where inference specification does not include collation or + * opclass (instead matching everything, regardless of cataloged + * collation/opclass of indexed attribute). + * + * At least historically, Postgres has not offered collations or opclasses + * with alternative-to-default notions of equality, so these additional + * criteria should only be required infrequently. + * + * Don't give up immediately when an inference element matches some attribute + * cataloged as indexed but not matching additional opclass/collation + * criteria. This is done so that the implementation is as forgiving as + * possible of redundancy within cataloged index attributes (or, less + * usefully, within inference specification elements). If collations actually + * differ between apparently redundantly indexed attributes (redundant within + * or across indexes), then there really is no redundancy as such. + * + * Note that if an inference element specifies an opclass and a collation at + * once, both must match in at least one particular attribute within index + * catalog definition in order for that inference element to be considered + * inferred/satisfied. + */ +static bool +infer_collation_opclass_match(InferenceElem *elem, Relation idxRel, + Bitmapset *inferAttrs, List *idxExprs) +{ + AttrNumber natt; + + /* + * If inference specification element lacks collation/opclass, then no + * need to check for exact match. + */ + if (elem->infercollid == InvalidOid && elem->inferopfamily == InvalidOid) + return true; + + for (natt = 1; natt <= idxRel->rd_att->natts; natt++) + { + Oid opfamily = idxRel->rd_opfamily[natt - 1]; + Oid opcinputtype = idxRel->rd_opcintype[natt - 1]; + Oid collation = idxRel->rd_indcollation[natt - 1]; + + if (elem->inferopfamily != InvalidOid && + (elem->inferopfamily != opfamily || + elem->inferopcinputtype != opcinputtype)) + { + /* Attribute needed to match opclass, but didn't */ + continue; + } + + if (elem->infercollid != InvalidOid && + elem->infercollid != collation) + { + /* Attribute needed to match collation, but didn't */ + continue; + } + + if ((IsA(elem->expr, Var) && + bms_is_member(((Var *) elem->expr)->varattno, inferAttrs)) || + list_member(idxExprs, elem->expr)) + { + /* Found one match - good enough */ + return true; + } + } + + return false; +} + +/* * estimate_rel_size - estimate # pages and # tuples in a table or index * * We also estimate the fraction of the pages that are marked all-visible in |