public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v42 1/2] Subscripting for jsonb 4+ messages / 3 participants [nested] [flat]
* [PATCH v42 1/2] Subscripting for jsonb @ 2020-12-18 16:19 Dmitrii Dolgov <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Dmitrii Dolgov @ 2020-12-18 16:19 UTC (permalink / raw) Subscripting implementation for jsonb. It does not support slices, does not have a limit for number of subscripts and for assignment expects a replace value to be of jsonb type. There is also one functional difference in assignment via subscripting from jsonb_set, when an original jsonb container is NULL, subscripting replaces it with an empty jsonb and proceed with assignment. For the sake of code reuse, some parts of jsonb functionality were rearranged to allow use the same functions for jsonb_set and assign subscripting operation. The original idea belongs to Oleg Bartunov. Reviewed-by: Tom Lane, Arthur Zakirov, Pavel Stehule --- doc/src/sgml/json.sgml | 48 ++++ src/backend/utils/adt/Makefile | 1 + src/backend/utils/adt/jsonb_util.c | 76 ++++- src/backend/utils/adt/jsonbsubs.c | 413 ++++++++++++++++++++++++++++ src/backend/utils/adt/jsonfuncs.c | 180 ++++++------ src/include/catalog/pg_proc.dat | 4 + src/include/catalog/pg_type.dat | 3 +- src/include/utils/jsonb.h | 6 +- src/test/regress/expected/jsonb.out | 272 +++++++++++++++++- src/test/regress/sql/jsonb.sql | 84 +++++- 10 files changed, 982 insertions(+), 105 deletions(-) create mode 100644 src/backend/utils/adt/jsonbsubs.c diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml index 5b9a5557a4..100d1a60f4 100644 --- a/doc/src/sgml/json.sgml +++ b/doc/src/sgml/json.sgml @@ -602,6 +602,54 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qu </para> </sect2> + <sect2 id="jsonb-subscripting"> + <title><type>jsonb</type> Subscripting</title> + <para> + <type>jsonb</type> data type supports array-style subscripting expressions + to extract or update particular elements. It's possible to use multiple + subscripting expressions to extract nested values. In this case, a chain of + subscripting expressions follows the same rules as the + <literal>path</literal> argument in <literal>jsonb_set</literal> function, + e.g. in case of arrays it is a 0-based operation or that negative integers + that appear in <literal>path</literal> count from the end of JSON arrays. + The result of subscripting expressions is always jsonb data type. An + example of subscripting syntax: +<programlisting> +-- Extract value by key +SELECT ('{"a": 1}'::jsonb)['a']; + +-- Extract nested value by key path +SELECT ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c']; + +-- Extract element by index +SELECT ('[1, "2", null]'::jsonb)[1]; + +-- Update value by key, note the single quotes - the assigned value +-- needs to be of jsonb type as well +UPDATE table_name SET jsonb_field['key'] = '1'; + +-- Select records using where clause with subscripting. Since the result of +-- subscripting is jsonb and we basically want to compare two jsonb objects, we +-- need to put the value in double quotes to be able to convert it to jsonb. +SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"'; +</programlisting> + + Subscripting for <type>jsonb</type> does not support slice expressions, + even if it contains an array. + + In case if source <type>jsonb</type> is <literal>NULL</literal>, assignment + via subscripting will proceed as if it was an empty JSON object: +<programlisting> +-- If jsonb_field here is NULL, the result is {"a": 1} +UPDATE table_name SET jsonb_field['a'] = '1'; + +-- If jsonb_field here is NULL, the result is [1] +UPDATE table_name SET jsonb_field[0] = '1'; +</programlisting> + + </para> + </sect2> + <sect2> <title>Transforms</title> diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile index 82732146d3..279ff15ade 100644 --- a/src/backend/utils/adt/Makefile +++ b/src/backend/utils/adt/Makefile @@ -50,6 +50,7 @@ OBJS = \ jsonb_op.o \ jsonb_util.o \ jsonfuncs.o \ + jsonbsubs.o \ jsonpath.o \ jsonpath_exec.o \ jsonpath_gram.o \ diff --git a/src/backend/utils/adt/jsonb_util.c b/src/backend/utils/adt/jsonb_util.c index 4eeffa1424..41a1c1f9bb 100644 --- a/src/backend/utils/adt/jsonb_util.c +++ b/src/backend/utils/adt/jsonb_util.c @@ -68,18 +68,29 @@ static JsonbValue *pushJsonbValueScalar(JsonbParseState **pstate, JsonbIteratorToken seq, JsonbValue *scalarVal); +JsonbValue * +JsonbToJsonbValue(Jsonb *jsonb) +{ + JsonbValue *val = (JsonbValue *) palloc(sizeof(JsonbValue)); + + val->type = jbvBinary; + val->val.binary.data = &jsonb->root; + val->val.binary.len = VARSIZE(jsonb) - VARHDRSZ; + + return val; +} + /* * Turn an in-memory JsonbValue into a Jsonb for on-disk storage. * - * There isn't a JsonbToJsonbValue(), because generally we find it more - * convenient to directly iterate through the Jsonb representation and only - * really convert nested scalar values. JsonbIteratorNext() does this, so that - * clients of the iteration code don't have to directly deal with the binary - * representation (JsonbDeepContains() is a notable exception, although all - * exceptions are internal to this module). In general, functions that accept - * a JsonbValue argument are concerned with the manipulation of scalar values, - * or simple containers of scalar values, where it would be inconvenient to - * deal with a great amount of other state. + * Generally we find it more convenient to directly iterate through the Jsonb + * representation and only really convert nested scalar values. + * JsonbIteratorNext() does this, so that clients of the iteration code don't + * have to directly deal with the binary representation (JsonbDeepContains() is + * a notable exception, although all exceptions are internal to this module). + * In general, functions that accept a JsonbValue argument are concerned with + * the manipulation of scalar values, or simple containers of scalar values, + * where it would be inconvenient to deal with a great amount of other state. */ Jsonb * JsonbValueToJsonb(JsonbValue *val) @@ -563,6 +574,30 @@ pushJsonbValue(JsonbParseState **pstate, JsonbIteratorToken seq, JsonbValue *res = NULL; JsonbValue v; JsonbIteratorToken tok; + int i; + + if (jbval && (seq == WJB_ELEM || seq == WJB_VALUE) && jbval->type == jbvObject) + { + pushJsonbValue(pstate, WJB_BEGIN_OBJECT, NULL); + for (i = 0; i < jbval->val.object.nPairs; i++) + { + pushJsonbValue(pstate, WJB_KEY, &jbval->val.object.pairs[i].key); + pushJsonbValue(pstate, WJB_VALUE, &jbval->val.object.pairs[i].value); + } + + return pushJsonbValue(pstate, WJB_END_OBJECT, NULL); + } + + if (jbval && (seq == WJB_ELEM || seq == WJB_VALUE) && jbval->type == jbvArray) + { + pushJsonbValue(pstate, WJB_BEGIN_ARRAY, NULL); + for (i = 0; i < jbval->val.array.nElems; i++) + { + pushJsonbValue(pstate, WJB_ELEM, &jbval->val.array.elems[i]); + } + + return pushJsonbValue(pstate, WJB_END_ARRAY, NULL); + } if (!jbval || (seq != WJB_ELEM && seq != WJB_VALUE) || jbval->type != jbvBinary) @@ -573,9 +608,30 @@ pushJsonbValue(JsonbParseState **pstate, JsonbIteratorToken seq, /* unpack the binary and add each piece to the pstate */ it = JsonbIteratorInit(jbval->val.binary.data); + + if ((jbval->val.binary.data->header & JB_FSCALAR) && *pstate) + { + tok = JsonbIteratorNext(&it, &v, true); + Assert(tok == WJB_BEGIN_ARRAY); + Assert(v.type == jbvArray && v.val.array.rawScalar); + + tok = JsonbIteratorNext(&it, &v, true); + Assert(tok == WJB_ELEM); + + res = pushJsonbValueScalar(pstate, seq, &v); + + tok = JsonbIteratorNext(&it, &v, true); + Assert(tok == WJB_END_ARRAY); + Assert(it == NULL); + + return res; + } + while ((tok = JsonbIteratorNext(&it, &v, false)) != WJB_DONE) res = pushJsonbValueScalar(pstate, tok, - tok < WJB_BEGIN_ARRAY ? &v : NULL); + tok < WJB_BEGIN_ARRAY || + (tok == WJB_BEGIN_ARRAY && + v.val.array.rawScalar) ? &v : NULL); return res; } diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c new file mode 100644 index 0000000000..306c37b5a6 --- /dev/null +++ b/src/backend/utils/adt/jsonbsubs.c @@ -0,0 +1,413 @@ +/*------------------------------------------------------------------------- + * + * jsonbsubs.c + * Subscripting support functions for jsonb. + * + * Portions Copyright (c) 1996-2020, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * + * IDENTIFICATION + * src/backend/utils/adt/jsonbsubs.c + * + *------------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "executor/execExpr.h" +#include "nodes/makefuncs.h" +#include "nodes/nodeFuncs.h" +#include "nodes/subscripting.h" +#include "parser/parse_coerce.h" +#include "parser/parse_expr.h" +#include "utils/jsonb.h" +#include "utils/jsonfuncs.h" +#include "utils/builtins.h" +#include "utils/lsyscache.h" + + +/* SubscriptingRefState.workspace for jsonb subscripting execution */ +typedef struct JsonbSubWorkspace +{ + bool expectArray; /* jsonb root is expected to be an array */ + Oid *indexOid; /* OID of coerced subscript expression, + could be only integer or text */ + Datum *index; /* Subscript values in Datum format */ +} JsonbSubWorkspace; + + +/* + * Finish parse analysis of a SubscriptingRef expression for a jsonb. + * + * Transform the subscript expressions, coerce them to text, + * and determine the result type of the SubscriptingRef node. + */ +static void +jsonb_subscript_transform(SubscriptingRef *sbsref, + List *indirection, + ParseState *pstate, + bool isSlice, + bool isAssignment) +{ + List *upperIndexpr = NIL; + ListCell *idx; + + /* + * Transform and convert the subscript expressions. Jsonb subscripting does + * not support slices, look only and the upper index. + */ + foreach(idx, indirection) + { + A_Indices *ai = lfirst_node(A_Indices, idx); + Node *subExpr; + + if (isSlice) + { + Node *expr = ai->uidx ? ai->uidx : ai->lidx; + + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("jsonb subscript does not support slices"), + parser_errposition(pstate, exprLocation(expr)))); + } + + if (ai->uidx) + { + Oid subExprType = InvalidOid, + targetType = UNKNOWNOID; + + subExpr = transformExpr(pstate, ai->uidx, pstate->p_expr_kind); + subExprType = exprType(subExpr); + + if (subExprType != UNKNOWNOID) + { + Oid targets[2] = {INT4OID, TEXTOID}; + + /* + * Jsonb can handle multiple subscript types, but cases when a + * subscript could be coerced to multiple target types must be + * avoided, similar to overloaded functions. It could be + * possibly extend with jsonpath in the future. + */ + for (int i = 0; i < 2; i++) + { + if (can_coerce_type(1, &subExprType, &targets[i], COERCION_IMPLICIT)) + { + /* + * One type has already succeeded, it means there are + * two coercion targets possible, failure. + */ + if (targetType != UNKNOWNOID) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("subscript type is not supported"), + errhint("Jsonb subscript must be coerced " + "only to one type, integer or text."), + parser_errposition(pstate, exprLocation(subExpr)))); + + targetType = targets[i]; + } + } + + /* + * No suitable types were found, failure. + */ + if (targetType == UNKNOWNOID) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("subscript type is not supported"), + errhint("Jsonb subscript must be coercet to either integer or text"), + parser_errposition(pstate, exprLocation(subExpr)))); + } + else + targetType = TEXTOID; + + /* + * We known from can_coerce_type that coercion will succeed, so + * coerce_type could be used. Note the implicit coercion context, + * which is required to handle subscripts of different types, + * similar to overloaded functions. + */ + subExpr = coerce_type(pstate, + subExpr, subExprType, + targetType, -1, + COERCION_IMPLICIT, + COERCE_IMPLICIT_CAST, + -1); + if (subExpr == NULL) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("jsonb subscript must have text type"), + parser_errposition(pstate, exprLocation(subExpr)))); + } + else + { + /* + * Slice with omitted upper bound. Should not happen as we already + * errored out on slice earlier, but handle this just in case. + */ + Assert(isSlice && ai->is_slice); + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("jsonb subscript does not support slices"), + parser_errposition(pstate, exprLocation(ai->uidx)))); + } + + upperIndexpr = lappend(upperIndexpr, subExpr); + } + + /* store the transformed lists into the SubscriptRef node */ + sbsref->refupperindexpr = upperIndexpr; + sbsref->reflowerindexpr = NIL; + + /* Determine the result type of the subscripting operation; always jsonb */ + sbsref->refrestype = JSONBOID; + sbsref->reftypmod = -1; +} + +/* + * During execution, process the subscripts in a SubscriptingRef expression. + * + * The subscript expressions are already evaluated in Datum form in the + * SubscriptingRefState's arrays. Check and convert them as necessary. + * + * If any subscript is NULL, we throw error in assignment cases, or in fetch + * cases set result to NULL and return false (instructing caller to skip the + * rest of the SubscriptingRef sequence). + */ +static bool +jsonb_subscript_check_subscripts(ExprState *state, + ExprEvalStep *op, + ExprContext *econtext) +{ + SubscriptingRefState *sbsrefstate = op->d.sbsref_subscript.state; + JsonbSubWorkspace *workspace = (JsonbSubWorkspace *) sbsrefstate->workspace; + + /* + * In case if the first subscript is an integer, the source jsonb is + * expected to be an array. This information is not used directly, all such + * cases are handled within corresponding jsonb assign functions. But if + * the source jsonb is NULL the expected type will be used to construct an + * empty source. + */ + if (sbsrefstate->numupper > 0 && sbsrefstate->upperprovided[0] && + !sbsrefstate->upperindexnull[0] && workspace->indexOid[0] == INT4OID) + workspace->expectArray = true; + + /* Process upper subscripts */ + for (int i = 0; i < sbsrefstate->numupper; i++) + { + if (sbsrefstate->upperprovided[i]) + { + /* If any index expr yields NULL, result is NULL or error */ + if (sbsrefstate->upperindexnull[i]) + { + if (sbsrefstate->isassignment) + ereport(ERROR, + (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), + errmsg("jsonb subscript in assignment must not be null"))); + *op->resnull = true; + return false; + } + + /* + * For jsonb fetch and assign functions we need to provide path in + * text format. Convert if it's not already text. + */ + if (workspace->indexOid[i] == INT4OID) + { + Datum datum = sbsrefstate->upperindex[i]; + char *cs = DatumGetCString(DirectFunctionCall1(int4out, datum)); + workspace->index[i] = CStringGetTextDatum(cs); + } + else + workspace->index[i] = sbsrefstate->upperindex[i]; + } + } + + return true; +} + +/* + * Evaluate SubscriptingRef fetch for a jsonb element. + * + * Source container is in step's result variable (it's known not NULL, since + * we set fetch_strict to true). + */ +static void +jsonb_subscript_fetch(ExprState *state, + ExprEvalStep *op, + ExprContext *econtext) +{ + SubscriptingRefState *sbsrefstate = op->d.sbsref.state; + JsonbSubWorkspace *workspace = (JsonbSubWorkspace *) sbsrefstate->workspace; + Jsonb *jsonbSource; + + /* Should not get here if source jsonb (or any subscript) is null */ + Assert(!(*op->resnull)); + + jsonbSource = DatumGetJsonbP(*op->resvalue); + *op->resvalue = jsonb_get_element(jsonbSource, + workspace->index, + sbsrefstate->numupper, + op->resnull, + false); +} + +/* + * Evaluate SubscriptingRef assignment for a jsonb element assignment. + * + * Input container (possibly null) is in result area, replacement value is in + * SubscriptingRefState's replacevalue/replacenull. + */ +static void +jsonb_subscript_assign(ExprState *state, + ExprEvalStep *op, + ExprContext *econtext) +{ + SubscriptingRefState *sbsrefstate = op->d.sbsref.state; + JsonbSubWorkspace *workspace = (JsonbSubWorkspace *) sbsrefstate->workspace; + Jsonb *jsonbSource; + JsonbValue *replacevalue; + + if (sbsrefstate->replacenull) + { + replacevalue = (JsonbValue *) palloc(sizeof(JsonbValue)); + replacevalue->type = jbvNull; + } + else + replacevalue = + JsonbToJsonbValue(DatumGetJsonbP(sbsrefstate->replacevalue)); + + /* + * In case if the input container is null, set up an empty jsonb and + * proceed with the assignment. + */ + if (*op->resnull) + { + JsonbValue *newSource = (JsonbValue *) palloc(sizeof(JsonbValue)); + + /* + * To avoid any surprising results, set up an empty jsonb array in case + * of an array is expected (i.e. the first subscript is integer), + * otherwise jsonb object. + */ + if (workspace->expectArray) + { + newSource->type = jbvArray; + newSource->val.array.nElems = 0; + newSource->val.array.rawScalar = false; + } + else + { + newSource->type = jbvObject; + newSource->val.object.nPairs = 0; + } + + jsonbSource = JsonbValueToJsonb(newSource); + *op->resnull = false; + } + else + jsonbSource = DatumGetJsonbP(*op->resvalue); + + *op->resvalue = jsonb_set_element(jsonbSource, + workspace->index, + sbsrefstate->numupper, + replacevalue); + /* The result is never NULL, so no need to change *op->resnull */ +} + +/* + * Compute old jsonb element value for a SubscriptingRef assignment + * expression. Will only be called if the new-value subexpression + * contains SubscriptingRef or FieldStore. This is the same as the + * regular fetch case, except that we have to handle a null jsonb, + * and the value should be stored into the SubscriptingRefState's + * prevvalue/prevnull fields. + */ +static void +jsonb_subscript_fetch_old(ExprState *state, + ExprEvalStep *op, + ExprContext *econtext) +{ + SubscriptingRefState *sbsrefstate = op->d.sbsref.state; + + if (*op->resnull) + { + /* whole jsonb is null, so any element is too */ + sbsrefstate->prevvalue = (Datum) 0; + sbsrefstate->prevnull = true; + } + else + { + Jsonb *jsonbSource = DatumGetJsonbP(*op->resvalue); + sbsrefstate->prevvalue = jsonb_get_element(jsonbSource, + sbsrefstate->upperindex, + sbsrefstate->numupper, + &sbsrefstate->prevnull, + false); + } +} + +/* + * Set up execution state for a jsonb subscript operation. Opposite to the + * arrays subscription, there is no limit for number of subscripts as jsonb + * type itself doesn't have nesting limits. + */ +static void +jsonb_exec_setup(const SubscriptingRef *sbsref, + SubscriptingRefState *sbsrefstate, + SubscriptExecSteps *methods) +{ + JsonbSubWorkspace *workspace; + ListCell *lc; + int nupper = sbsref->refupperindexpr->length; + char *ptr; + + /* Allocate type-specific workspace with space for per-subscript data */ + workspace = palloc0(MAXALIGN(sizeof(JsonbSubWorkspace)) + + nupper * (sizeof(Datum) + sizeof(Oid))); + workspace->expectArray = false; + ptr = ((char *) workspace) + MAXALIGN(sizeof(JsonbSubWorkspace)); + workspace->indexOid = (Oid *) ptr; + ptr += nupper * sizeof(Oid); + workspace->index = (Datum *) ptr; + + sbsrefstate->workspace = workspace; + + /* Collect subscript data types necessary at execution time */ + foreach(lc, sbsref->refupperindexpr) + { + Node *expr = lfirst(lc); + int i = foreach_current_index(lc); + + workspace->indexOid[i] = exprType(expr); + } + + /* + * Pass back pointers to appropriate step execution functions. + */ + methods->sbs_check_subscripts = jsonb_subscript_check_subscripts; + methods->sbs_fetch = jsonb_subscript_fetch; + methods->sbs_assign = jsonb_subscript_assign; + methods->sbs_fetch_old = jsonb_subscript_fetch_old; +} + +/* + * jsonb_subscript_handler + * Subscripting handler for jsonb. + * + */ +Datum +jsonb_subscript_handler(PG_FUNCTION_ARGS) +{ + static const SubscriptRoutines sbsroutines = { + .transform = jsonb_subscript_transform, + .exec_setup = jsonb_exec_setup, + .fetch_strict = true, /* fetch returns NULL for NULL inputs */ + .fetch_leakproof = true, /* fetch returns NULL for bad subscript */ + .store_leakproof = false /* ... but assignment throws error */ + }; + + PG_RETURN_POINTER(&sbsroutines); +} diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c index 69100feab7..5a0ba6b220 100644 --- a/src/backend/utils/adt/jsonfuncs.c +++ b/src/backend/utils/adt/jsonfuncs.c @@ -461,18 +461,18 @@ static Datum populate_domain(DomainIOData *io, Oid typid, const char *colname, /* functions supporting jsonb_delete, jsonb_set and jsonb_concat */ static JsonbValue *IteratorConcat(JsonbIterator **it1, JsonbIterator **it2, JsonbParseState **state); -static JsonbValue *setPath(JsonbIterator **it, Datum *path_elems, +extern JsonbValue *setPath(JsonbIterator **it, Datum *path_elems, bool *path_nulls, int path_len, - JsonbParseState **st, int level, Jsonb *newval, + JsonbParseState **st, int level, JsonbValue *newval, int op_type); static void setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls, int path_len, JsonbParseState **st, int level, - Jsonb *newval, uint32 npairs, int op_type); + JsonbValue *newval, uint32 npairs, int op_type); static void setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls, int path_len, JsonbParseState **st, - int level, Jsonb *newval, uint32 nelems, int op_type); -static void addJsonbToParseState(JsonbParseState **jbps, Jsonb *jb); + int level, + JsonbValue *newval, uint32 nelems, int op_type); /* function supporting iterate_json_values */ static void iterate_values_scalar(void *state, char *token, JsonTokenType tokentype); @@ -1448,13 +1448,9 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) ArrayType *path = PG_GETARG_ARRAYTYPE_P(1); Datum *pathtext; bool *pathnulls; + bool isnull; int npath; - int i; - bool have_object = false, - have_array = false; - JsonbValue *jbvp = NULL; - JsonbValue jbvbuf; - JsonbContainer *container; + Datum res; /* * If the array contains any null elements, return NULL, on the grounds @@ -1469,9 +1465,26 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) deconstruct_array(path, TEXTOID, -1, false, TYPALIGN_INT, &pathtext, &pathnulls, &npath); - /* Identify whether we have object, array, or scalar at top-level */ - container = &jb->root; + res = jsonb_get_element(jb, pathtext, npath, &isnull, as_text); + if (isnull) + PG_RETURN_NULL(); + else + PG_RETURN_DATUM(res); +} + +Datum +jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text) +{ + JsonbContainer *container = &jb->root; + JsonbValue *jbvp = NULL; + int i; + bool have_object = false, + have_array = false; + + *isnull = false; + + /* Identify whether we have object, array, or scalar at top-level */ if (JB_ROOT_IS_OBJECT(jb)) have_object = true; else if (JB_ROOT_IS_ARRAY(jb) && !JB_ROOT_IS_SCALAR(jb)) @@ -1496,7 +1509,7 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) { if (as_text) { - PG_RETURN_TEXT_P(cstring_to_text(JsonbToCString(NULL, + return PointerGetDatum(cstring_to_text(JsonbToCString(NULL, container, VARSIZE(jb)))); } @@ -1512,22 +1525,25 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) if (have_object) { jbvp = getKeyJsonValueFromContainer(container, - VARDATA(pathtext[i]), - VARSIZE(pathtext[i]) - VARHDRSZ, - &jbvbuf); + VARDATA(path[i]), + VARSIZE(path[i]) - VARHDRSZ, + NULL); } else if (have_array) { long lindex; uint32 index; - char *indextext = TextDatumGetCString(pathtext[i]); + char *indextext = TextDatumGetCString(path[i]); char *endptr; errno = 0; lindex = strtol(indextext, &endptr, 10); if (endptr == indextext || *endptr != '\0' || errno != 0 || lindex > INT_MAX || lindex < INT_MIN) - PG_RETURN_NULL(); + { + *isnull = true; + return PointerGetDatum(NULL); + } if (lindex >= 0) { @@ -1545,7 +1561,10 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) nelements = JsonContainerSize(container); if (-lindex > nelements) - PG_RETURN_NULL(); + { + *isnull = true; + return PointerGetDatum(NULL); + } else index = nelements + lindex; } @@ -1555,11 +1574,15 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) else { /* scalar, extraction yields a null */ - PG_RETURN_NULL(); + *isnull = true; + return PointerGetDatum(NULL); } if (jbvp == NULL) - PG_RETURN_NULL(); + { + *isnull = true; + return PointerGetDatum(NULL); + } else if (i == npath - 1) break; @@ -1581,9 +1604,12 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) if (as_text) { if (jbvp->type == jbvNull) - PG_RETURN_NULL(); + { + *isnull = true; + return PointerGetDatum(NULL); + } - PG_RETURN_TEXT_P(JsonbValueAsText(jbvp)); + return PointerGetDatum(JsonbValueAsText(jbvp)); } else { @@ -1594,6 +1620,28 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) } } +Datum +jsonb_set_element(Jsonb* jb, Datum *path, int path_len, + JsonbValue *newval) +{ + JsonbValue *res; + JsonbParseState *state = NULL; + JsonbIterator *it; + bool *path_nulls = palloc0(path_len * sizeof(bool)); + + if (newval->type == jbvArray && newval->val.array.rawScalar) + *newval = newval->val.array.elems[0]; + + it = JsonbIteratorInit(&jb->root); + + res = setPath(&it, path, path_nulls, path_len, &state, 0, + newval, JB_PATH_CREATE); + + pfree(path_nulls); + + PG_RETURN_JSONB_P(JsonbValueToJsonb(res)); +} + /* * Return the text representation of the given JsonbValue. */ @@ -4151,58 +4199,6 @@ jsonb_strip_nulls(PG_FUNCTION_ARGS) PG_RETURN_POINTER(JsonbValueToJsonb(res)); } -/* - * Add values from the jsonb to the parse state. - * - * If the parse state container is an object, the jsonb is pushed as - * a value, not a key. - * - * This needs to be done using an iterator because pushJsonbValue doesn't - * like getting jbvBinary values, so we can't just push jb as a whole. - */ -static void -addJsonbToParseState(JsonbParseState **jbps, Jsonb *jb) -{ - JsonbIterator *it; - JsonbValue *o = &(*jbps)->contVal; - JsonbValue v; - JsonbIteratorToken type; - - it = JsonbIteratorInit(&jb->root); - - Assert(o->type == jbvArray || o->type == jbvObject); - - if (JB_ROOT_IS_SCALAR(jb)) - { - (void) JsonbIteratorNext(&it, &v, false); /* skip array header */ - Assert(v.type == jbvArray); - (void) JsonbIteratorNext(&it, &v, false); /* fetch scalar value */ - - switch (o->type) - { - case jbvArray: - (void) pushJsonbValue(jbps, WJB_ELEM, &v); - break; - case jbvObject: - (void) pushJsonbValue(jbps, WJB_VALUE, &v); - break; - default: - elog(ERROR, "unexpected parent of nested structure"); - } - } - else - { - while ((type = JsonbIteratorNext(&it, &v, false)) != WJB_DONE) - { - if (type == WJB_KEY || type == WJB_VALUE || type == WJB_ELEM) - (void) pushJsonbValue(jbps, type, &v); - else - (void) pushJsonbValue(jbps, type, NULL); - } - } - -} - /* * SQL function jsonb_pretty (jsonb) * @@ -4474,7 +4470,8 @@ jsonb_set(PG_FUNCTION_ARGS) { Jsonb *in = PG_GETARG_JSONB_P(0); ArrayType *path = PG_GETARG_ARRAYTYPE_P(1); - Jsonb *newval = PG_GETARG_JSONB_P(2); + Jsonb *newjsonb = PG_GETARG_JSONB_P(2); + JsonbValue *newval = JsonbToJsonbValue(newjsonb); bool create = PG_GETARG_BOOL(3); JsonbValue *res = NULL; Datum *path_elems; @@ -4632,7 +4629,8 @@ jsonb_insert(PG_FUNCTION_ARGS) { Jsonb *in = PG_GETARG_JSONB_P(0); ArrayType *path = PG_GETARG_ARRAYTYPE_P(1); - Jsonb *newval = PG_GETARG_JSONB_P(2); + Jsonb *newjsonb = PG_GETARG_JSONB_P(2); + JsonbValue *newval = JsonbToJsonbValue(newjsonb); bool after = PG_GETARG_BOOL(3); JsonbValue *res = NULL; Datum *path_elems; @@ -4787,10 +4785,10 @@ IteratorConcat(JsonbIterator **it1, JsonbIterator **it2, * All path elements before the last must already exist * whatever bits in op_type are set, or nothing is done. */ -static JsonbValue * +JsonbValue * setPath(JsonbIterator **it, Datum *path_elems, bool *path_nulls, int path_len, - JsonbParseState **st, int level, Jsonb *newval, int op_type) + JsonbParseState **st, int level, JsonbValue *newval, int op_type) { JsonbValue v; JsonbIteratorToken r; @@ -4843,11 +4841,11 @@ setPath(JsonbIterator **it, Datum *path_elems, static void setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls, int path_len, JsonbParseState **st, int level, - Jsonb *newval, uint32 npairs, int op_type) + JsonbValue *newval, uint32 npairs, int op_type) { - JsonbValue v; int i; - JsonbValue k; + JsonbValue k, + v; bool done = false; if (level >= path_len || path_nulls[level]) @@ -4864,7 +4862,7 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls, newkey.val.string.val = VARDATA_ANY(path_elems[level]); (void) pushJsonbValue(st, WJB_KEY, &newkey); - addJsonbToParseState(st, newval); + (void) pushJsonbValue(st, WJB_VALUE, newval); } for (i = 0; i < npairs; i++) @@ -4895,7 +4893,7 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls, if (!(op_type & JB_PATH_DELETE)) { (void) pushJsonbValue(st, WJB_KEY, &k); - addJsonbToParseState(st, newval); + (void) pushJsonbValue(st, WJB_VALUE, newval); } done = true; } @@ -4918,7 +4916,7 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls, newkey.val.string.val = VARDATA_ANY(path_elems[level]); (void) pushJsonbValue(st, WJB_KEY, &newkey); - addJsonbToParseState(st, newval); + (void) pushJsonbValue(st, WJB_VALUE, newval); } (void) pushJsonbValue(st, r, &k); @@ -4950,7 +4948,7 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls, static void setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls, int path_len, JsonbParseState **st, int level, - Jsonb *newval, uint32 nelems, int op_type) + JsonbValue *newval, uint32 nelems, int op_type) { JsonbValue v; int idx, @@ -4998,7 +4996,7 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls, (op_type & JB_PATH_CREATE_OR_INSERT)) { Assert(newval != NULL); - addJsonbToParseState(st, newval); + (void) pushJsonbValue(st, WJB_ELEM, newval); done = true; } @@ -5014,7 +5012,7 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls, r = JsonbIteratorNext(it, &v, true); /* skip */ if (op_type & (JB_PATH_INSERT_BEFORE | JB_PATH_CREATE)) - addJsonbToParseState(st, newval); + (void) pushJsonbValue(st, WJB_ELEM, newval); /* * We should keep current value only in case of @@ -5025,7 +5023,7 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls, (void) pushJsonbValue(st, r, &v); if (op_type & (JB_PATH_INSERT_AFTER | JB_PATH_REPLACE)) - addJsonbToParseState(st, newval); + (void) pushJsonbValue(st, WJB_ELEM, newval); done = true; } @@ -5059,7 +5057,7 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls, if ((op_type & JB_PATH_CREATE_OR_INSERT) && !done && level == path_len - 1 && i == nelems - 1) { - addJsonbToParseState(st, newval); + (void) pushJsonbValue(st, WJB_ELEM, newval); } } } diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 139f4a08bd..feae8cc4b0 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -11239,6 +11239,10 @@ { oid => '9256', descr => 'raw array subscripting support', proname => 'raw_array_subscript_handler', prorettype => 'internal', proargtypes => 'internal', prosrc => 'raw_array_subscript_handler' }, +# type subscripting support +{ oid => '6098', descr => 'jsonb subscripting logic', + proname => 'jsonb_subscript_handler', prorettype => 'internal', + proargtypes => 'internal', prosrc => 'jsonb_subscript_handler' }, # collation management functions { oid => '3445', descr => 'import collations from operating system', diff --git a/src/include/catalog/pg_type.dat b/src/include/catalog/pg_type.dat index 62018f063a..4a530ca907 100644 --- a/src/include/catalog/pg_type.dat +++ b/src/include/catalog/pg_type.dat @@ -444,7 +444,8 @@ { oid => '3802', array_type_oid => '3807', descr => 'Binary JSON', typname => 'jsonb', typlen => '-1', typbyval => 'f', typcategory => 'U', typinput => 'jsonb_in', typoutput => 'jsonb_out', typreceive => 'jsonb_recv', - typsend => 'jsonb_send', typalign => 'i', typstorage => 'x' }, + typsend => 'jsonb_send', typalign => 'i', typstorage => 'x', + typsubscript => 'jsonb_subscript_handler' }, { oid => '4072', array_type_oid => '4073', descr => 'JSON path', typname => 'jsonpath', typlen => '-1', typbyval => 'f', typcategory => 'U', typinput => 'jsonpath_in', typoutput => 'jsonpath_out', diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h index 5860011693..64f1ccbf77 100644 --- a/src/include/utils/jsonb.h +++ b/src/include/utils/jsonb.h @@ -392,6 +392,7 @@ extern JsonbValue *pushJsonbValue(JsonbParseState **pstate, extern JsonbIterator *JsonbIteratorInit(JsonbContainer *container); extern JsonbIteratorToken JsonbIteratorNext(JsonbIterator **it, JsonbValue *val, bool skipNested); +extern JsonbValue *JsonbToJsonbValue(Jsonb *jsonb); extern Jsonb *JsonbValueToJsonb(JsonbValue *val); extern bool JsonbDeepContains(JsonbIterator **val, JsonbIterator **mContained); @@ -407,5 +408,8 @@ extern char *JsonbToCStringIndent(StringInfo out, JsonbContainer *in, extern bool JsonbExtractScalar(JsonbContainer *jbc, JsonbValue *res); extern const char *JsonbTypeName(JsonbValue *jb); - +extern Datum jsonb_set_element(Jsonb *jb, Datum *path, int path_len, + JsonbValue *newval); +extern Datum jsonb_get_element(Jsonb *jb, Datum *path, int npath, + bool *isnull, bool as_text); #endif /* __JSONB_H__ */ diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out index 1e6c6ef200..bb3f25ec3f 100644 --- a/src/test/regress/expected/jsonb.out +++ b/src/test/regress/expected/jsonb.out @@ -4599,7 +4599,7 @@ select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'use_ {"a": 1, "b": null} (1 row) -\pset null +\pset null '' -- jsonb_insert select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"'); jsonb_insert @@ -4729,6 +4729,276 @@ HINT: Try using the function jsonb_set to replace key value. select jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"', true); ERROR: cannot replace existing key HINT: Try using the function jsonb_set to replace key value. +-- jsonb subscript +select ('123'::jsonb)['a']; + jsonb +------- + +(1 row) + +select ('123'::jsonb)[0]; + jsonb +------- + +(1 row) + +select ('123'::jsonb)[NULL]; + jsonb +------- + +(1 row) + +select ('{"a": 1}'::jsonb)['a']; + jsonb +------- + 1 +(1 row) + +select ('{"a": 1}'::jsonb)[0]; + jsonb +------- + +(1 row) + +select ('{"a": 1}'::jsonb)['not_exist']; + jsonb +------- + +(1 row) + +select ('{"a": 1}'::jsonb)[NULL]; + jsonb +------- + +(1 row) + +select ('[1, "2", null]'::jsonb)['a']; + jsonb +------- + +(1 row) + +select ('[1, "2", null]'::jsonb)[0]; + jsonb +------- + 1 +(1 row) + +select ('[1, "2", null]'::jsonb)['1']; + jsonb +------- + "2" +(1 row) + +select ('[1, "2", null]'::jsonb)[1.0]; +ERROR: subscript type is not supported +LINE 1: select ('[1, "2", null]'::jsonb)[1.0]; + ^ +HINT: Jsonb subscript must be coercet to either integer or text +select ('[1, "2", null]'::jsonb)[2]; + jsonb +------- + null +(1 row) + +select ('[1, "2", null]'::jsonb)[3]; + jsonb +------- + +(1 row) + +select ('[1, "2", null]'::jsonb)[-2]; + jsonb +------- + "2" +(1 row) + +select ('[1, "2", null]'::jsonb)[1]['a']; + jsonb +------- + +(1 row) + +select ('[1, "2", null]'::jsonb)[1][0]; + jsonb +------- + +(1 row) + +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['b']; + jsonb +------- + "c" +(1 row) + +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d']; + jsonb +----------- + [1, 2, 3] +(1 row) + +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d'][1]; + jsonb +------- + 2 +(1 row) + +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d']['a']; + jsonb +------- + +(1 row) + +select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']; + jsonb +--------------- + {"a2": "aaa"} +(1 row) + +select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2']; + jsonb +------- + "aaa" +(1 row) + +select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2']['a3']; + jsonb +------- + +(1 row) + +select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1']; + jsonb +----------------------- + ["aaa", "bbb", "ccc"] +(1 row) + +select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1'][2]; + jsonb +------- + "ccc" +(1 row) + +-- slices are not supported +select ('{"a": 1}'::jsonb)['a':'b']; +ERROR: jsonb subscript does not support slices +LINE 1: select ('{"a": 1}'::jsonb)['a':'b']; + ^ +select ('[1, "2", null]'::jsonb)[1:2]; +ERROR: jsonb subscript does not support slices +LINE 1: select ('[1, "2", null]'::jsonb)[1:2]; + ^ +select ('[1, "2", null]'::jsonb)[:2]; +ERROR: jsonb subscript does not support slices +LINE 1: select ('[1, "2", null]'::jsonb)[:2]; + ^ +select ('[1, "2", null]'::jsonb)[1:]; +ERROR: jsonb subscript does not support slices +LINE 1: select ('[1, "2", null]'::jsonb)[1:]; + ^ +select ('[1, "2", null]'::jsonb)[:]; +ERROR: jsonb subscript does not support slices +create TEMP TABLE test_jsonb_subscript ( + id int, + test_json jsonb +); +insert into test_jsonb_subscript values +(1, '{}'), -- empty jsonb +(2, '{"key": "value"}'); -- jsonb with data +-- update empty jsonb +update test_jsonb_subscript set test_json['a'] = '1' where id = 1; +select * from test_jsonb_subscript; + id | test_json +----+------------------ + 2 | {"key": "value"} + 1 | {"a": 1} +(2 rows) + +-- update jsonb with some data +update test_jsonb_subscript set test_json['a'] = '1' where id = 2; +select * from test_jsonb_subscript; + id | test_json +----+-------------------------- + 1 | {"a": 1} + 2 | {"a": 1, "key": "value"} +(2 rows) + +-- replace jsonb +update test_jsonb_subscript set test_json['a'] = '"test"'; +select * from test_jsonb_subscript; + id | test_json +----+------------------------------- + 1 | {"a": "test"} + 2 | {"a": "test", "key": "value"} +(2 rows) + +-- replace by object +update test_jsonb_subscript set test_json['a'] = '{"b": 1}'::jsonb; +select * from test_jsonb_subscript; + id | test_json +----+--------------------------------- + 1 | {"a": {"b": 1}} + 2 | {"a": {"b": 1}, "key": "value"} +(2 rows) + +-- replace by array +update test_jsonb_subscript set test_json['a'] = '[1, 2, 3]'::jsonb; +select * from test_jsonb_subscript; + id | test_json +----+---------------------------------- + 1 | {"a": [1, 2, 3]} + 2 | {"a": [1, 2, 3], "key": "value"} +(2 rows) + +-- use jsonb subscription in where clause +select * from test_jsonb_subscript where test_json['key'] = '"value"'; + id | test_json +----+---------------------------------- + 2 | {"a": [1, 2, 3], "key": "value"} +(1 row) + +select * from test_jsonb_subscript where test_json['key_doesnt_exists'] = '"value"'; + id | test_json +----+----------- +(0 rows) + +select * from test_jsonb_subscript where test_json['key'] = '"wrong_value"'; + id | test_json +----+----------- +(0 rows) + +-- NULL +update test_jsonb_subscript set test_json[NULL] = '1'; +ERROR: jsonb subscript in assignment must not be null +update test_jsonb_subscript set test_json['another_key'] = NULL; +select * from test_jsonb_subscript; + id | test_json +----+------------------------------------------------------- + 1 | {"a": [1, 2, 3], "another_key": null} + 2 | {"a": [1, 2, 3], "key": "value", "another_key": null} +(2 rows) + +-- NULL as jsonb source +insert into test_jsonb_subscript values (3, NULL); +update test_jsonb_subscript set test_json['a'] = '1' where id = 3; +select * from test_jsonb_subscript; + id | test_json +----+------------------------------------------------------- + 1 | {"a": [1, 2, 3], "another_key": null} + 2 | {"a": [1, 2, 3], "key": "value", "another_key": null} + 3 | {"a": 1} +(3 rows) + +update test_jsonb_subscript set test_json = NULL where id = 3; +update test_jsonb_subscript set test_json[0] = '1'; +select * from test_jsonb_subscript; + id | test_json +----+--------------------------------------------------------------- + 1 | {"0": 1, "a": [1, 2, 3], "another_key": null} + 2 | {"0": 1, "a": [1, 2, 3], "key": "value", "another_key": null} + 3 | [1] +(3 rows) + -- jsonb to tsvector select to_tsvector('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::jsonb); to_tsvector diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql index b6409767f6..20aa8fe0e2 100644 --- a/src/test/regress/sql/jsonb.sql +++ b/src/test/regress/sql/jsonb.sql @@ -1177,7 +1177,7 @@ select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'retu select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'delete_key') as delete_key; select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'use_json_null') as use_json_null; -\pset null +\pset null '' -- jsonb_insert select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"'); @@ -1208,6 +1208,88 @@ select jsonb_insert('{"a": {"b": "value"}}', '{a, c}', '"new_value"', true); select jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"'); select jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"', true); +-- jsonb subscript +select ('123'::jsonb)['a']; +select ('123'::jsonb)[0]; +select ('123'::jsonb)[NULL]; +select ('{"a": 1}'::jsonb)['a']; +select ('{"a": 1}'::jsonb)[0]; +select ('{"a": 1}'::jsonb)['not_exist']; +select ('{"a": 1}'::jsonb)[NULL]; +select ('[1, "2", null]'::jsonb)['a']; +select ('[1, "2", null]'::jsonb)[0]; +select ('[1, "2", null]'::jsonb)['1']; +select ('[1, "2", null]'::jsonb)[1.0]; +select ('[1, "2", null]'::jsonb)[2]; +select ('[1, "2", null]'::jsonb)[3]; +select ('[1, "2", null]'::jsonb)[-2]; +select ('[1, "2", null]'::jsonb)[1]['a']; +select ('[1, "2", null]'::jsonb)[1][0]; +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['b']; +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d']; +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d'][1]; +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d']['a']; +select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']; +select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2']; +select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2']['a3']; +select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1']; +select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1'][2]; + +-- slices are not supported +select ('{"a": 1}'::jsonb)['a':'b']; +select ('[1, "2", null]'::jsonb)[1:2]; +select ('[1, "2", null]'::jsonb)[:2]; +select ('[1, "2", null]'::jsonb)[1:]; +select ('[1, "2", null]'::jsonb)[:]; + +create TEMP TABLE test_jsonb_subscript ( + id int, + test_json jsonb +); + +insert into test_jsonb_subscript values +(1, '{}'), -- empty jsonb +(2, '{"key": "value"}'); -- jsonb with data + +-- update empty jsonb +update test_jsonb_subscript set test_json['a'] = '1' where id = 1; +select * from test_jsonb_subscript; + +-- update jsonb with some data +update test_jsonb_subscript set test_json['a'] = '1' where id = 2; +select * from test_jsonb_subscript; + +-- replace jsonb +update test_jsonb_subscript set test_json['a'] = '"test"'; +select * from test_jsonb_subscript; + +-- replace by object +update test_jsonb_subscript set test_json['a'] = '{"b": 1}'::jsonb; +select * from test_jsonb_subscript; + +-- replace by array +update test_jsonb_subscript set test_json['a'] = '[1, 2, 3]'::jsonb; +select * from test_jsonb_subscript; + +-- use jsonb subscription in where clause +select * from test_jsonb_subscript where test_json['key'] = '"value"'; +select * from test_jsonb_subscript where test_json['key_doesnt_exists'] = '"value"'; +select * from test_jsonb_subscript where test_json['key'] = '"wrong_value"'; + +-- NULL +update test_jsonb_subscript set test_json[NULL] = '1'; +update test_jsonb_subscript set test_json['another_key'] = NULL; +select * from test_jsonb_subscript; + +-- NULL as jsonb source +insert into test_jsonb_subscript values (3, NULL); +update test_jsonb_subscript set test_json['a'] = '1' where id = 3; +select * from test_jsonb_subscript; + +update test_jsonb_subscript set test_json = NULL where id = 3; +update test_jsonb_subscript set test_json[0] = '1'; +select * from test_jsonb_subscript; + -- jsonb to tsvector select to_tsvector('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::jsonb); -- 2.21.0 --2lozexjxkmbehzdg Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v42-0002-Filling-gaps-in-jsonb.patch" ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: allowing extensions to control planner behavior @ 2024-09-18 15:48 Robert Haas <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Robert Haas @ 2024-09-18 15:48 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: pgsql-hackers On Tue, Aug 27, 2024 at 4:07 PM Robert Haas <[email protected]> wrote: > I think that the beginning of add_paths_to_joinrel() looks like a > useful spot to get control. You could, for example, have a hook there > which returns a bitmask indicating which of merge-join, nested-loop, > and hash join will be allowable for this call; that hook would then > allow for control over the join method and the join order, and the > join order control is strong enough that you can implement either of > the two interpretations above. This idea theorizes that 0001 was wrong > to make the path mask a per-RelOptInfo value, because there could be > many calls to add_paths_to_joinrel() for a single RelOptInfo and, in > this idea, every one of those can enforce a different mask. Here is an implementation of this idea. I think this is significantly more elegant than the previous patch. Functionally, it does a better job allowing for control over join planning than the previous patch, because you can control both the join method and the join order. It does not attempt to provide control over scan or appendrel methods; I could build similar machinery for those cases, but let's talk about this case, first. As non-for-commit proofs-of-concept, I've included two sample contrib modules here, one called alphabet_join and one called hint_via_alias. alphabet_join forces the join to be done in alphabetical order by table alias. Consider this test query: SELECT COUNT(*) FROM pgbench_accounts THE INNER JOIN pgbench_accounts QUICK on THE.aid = QUICK.aid INNER JOIN pgbench_accounts BROWN on THE.aid = BROWN.aid INNER JOIN pgbench_accounts FOX on THE.aid = FOX.aid INNER JOIN pgbench_accounts JUMPED on THE.aid = JUMPED.aid INNER JOIN pgbench_accounts OVER on THE.aid = OVER.aid INNER JOIN pgbench_accounts LAZY on THE.aid = LAZY.aid INNER JOIN pgbench_accounts DOG on THE.aid = DOG.aid; When you just execute this normally, the join order matches the order you enter it: THE QUICK BROWN FOX JUMPED OVER LAZY DOG. But if you load alphabet_join, then the join order becomes BROWN DOG FOX JUMPED LAZY OVER QUICK THE. It is unlikely that anyone wants their join order to be determined by strict alphabetical order, but again, this is just intended to show that the hook works. hint_via_alias whose table alias starts with mj_, hj_, or nl_ using a merge-join, hash-join, or nested loop, respectively. Here again, I don't think that passing hints through the table alias names is probably the best thing from a UI perspective, but unlike the previous one which is clearly a toy, I can imagine someone actually trying to use this one on a real server. If we want anything in contrib at all it should probably be something much better than this, but again at this stage I'm just trying to showcase the hook. > Potentially, such a hook could return additional information, either > by using more bits of the bitmask or by returning other information > via some other data type. For instance, I still believe that > distinguishing between parameterized-nestloops and > unparameterized-nestloops would be real darn useful, so we could have > separate bits for each; or you could have a bit to control whether > foreign-join paths get disabled (or are considered at all), or you > could have separate bits for merge joins that involve 0, 1, or 2 > sorts. Whether we need or want any or all of that is certainly > debatable, but the point is that if you did want some of that, or > something else, it doesn't look difficult to feed that information > through to the places where you would need it to be available. I spent a lot of time thinking about what should and should not be in scope for this hook and decided against both of the ideas above. They're not necessarily bad ideas but they feel like examples of arbitrary policy that you could want to implement, and I don't think it's viable to have every arbitrary policy that someone happens to favor in the core code. If we want extensions to be able to achieve these kinds of results, I think we're going to need a hook at either initial_cost_XXX time that would be free to make arbitrary decisions about cost and disabled nodes for each possible path we might generate, or a hook at final_cost_XXX time that could make paths more disabled (but not less) or more costly (but not less costly unless it also makes them more disabled). For now, I have not done that, because I think the hook that I've added to add_paths_to_joinrel is fairly powerful and significantly cheaper than a hook that has to fire for every possible generated path. Also, even if we do add that, I bet it's useful to let this hook pass some state through to that hook, as a way of avoiding recomputation. However, although I didn't end up including either of the policies mentioned above in this patch, I still did end up subdividing the "merge join" strategy according to whether or not a Materialize node is used, and the "nested loop" strategy according to whether we use Materialize, Memoize, or neither. At least according to my reading of the code, the planner really does consider these to be separate sub-strategies: it thinks about whether to use a nested loop without a materialize node, and it thinks about whether to do a nested loop with a materialize node, and there's separate code for those things. So this doesn't feel like an arbitrary distinction to me. In contrast, the parameterized-vs-unparameterized nested loop thing is just a question of whether the outer path that we happen to choose happens to satisfy some part of the parameterization of the inner path we happen to choose; the code neither knows nor cares whether that will occur. There is also a pragmatic reason to make sure that the hook allows for control over use of these sub-strategies: pg_hint_plan has Memoize and NoMemoize hints, and if whatever hook we add here can't replace what pg_hint_plan is already doing, then it's clearly not up to the mark. I also spent some time thinking about what behavior this hook does and does not allow you to control. As noted, it allows you to control the join method and the join order, including which table ends up on which side of the join. But, is that good enough to reproduce a very specific plan, say one that you saw before and liked? Let's imagine that you've arranged to disable every path in outerrel and innerrel other than the ones that you want to be chosen, either using some hackery or some future patch not included here. Now, you want to use this patch to make sure that those are joined in the way that you want them to be joined. Can you do that? I think the answer is "mostly". You'll be able to get the join method you want used, and you'll be able to get Memoize and/or Materialize nodes if you want them or avoid them if you don't. Also, join_path_setup_hook will see JOIN_UNIQUE_INNER or JOIN_UNIQUE_OUTER so if we're thinking of implementing a semijoin via uniquify+join, the hook will be able to encourage or discourage that approach if it wants. However, it *won't* be able to force the uniquification to happen using hashing rather than sorting or vice versa, or at least not without doing something pretty kludgy. Also, it won't be able to force a merge join produced by sort_inner_and_outer() to use the sort keys that it prefers. Merge joins produced by match_unsorted_outer() are entirely a function of the input paths, but those produced by sort_inner_and_outer() are not. Aside from these two cases, I found no other gaps. AFAICS, the only way to close the gap around unique-ification strategy would be with some piece of bespoke infrastructure that just does exactly that. The inability to control the sort order selected by sort_inner_and_outer() could, I believe, be closed by a hook at initial or final cost time. As noted above, such a hook is also useful when you're not trying to arrive at a specific plan, but rather have some policy around what kind of plan you want to end up with and wish to penalize plans that don't comply with your policy. So maybe this is an argument for adding that hook. That said, even without that, this might be close enough for government work. If the planner chooses the correct input paths and if it also chooses a merge join, how likely is it that it will now choose the wrong pathkeys to perform that merge join? I bet it's quite unlikely, because I think the effect of the logic we have will probably be to just do the smallest possible amount of sorting, and that's also probably the answer the user wants. So it might be OK in practice to just not worry about this. On the other hand, a leading cause of disasters is people assuming that certain things would not go wrong and then having those exact things go wrong, so it's probably unwise to be confident that the attached patch is all we'll ever need. Still, I think it's a pretty useful starting point. It is mostly enough to give you control over join planning, and if combined with similar work for scan planning, I think it would be enough for pg_hint_plan. If we also got control over appendrel and agg planning, then you could do a bunch more cool things. Comments? -- Robert Haas EDB: http://www.enterprisedb.com Attachments: [application/octet-stream] v3-0003-New-contrib-module-hint_via_alias.patch (6.0K, ../../CA+TgmoYadvnRGM7VEqgKbuZ+c+VN3jSDx+_38e66a9yk2fiovA@mail.gmail.com/2-v3-0003-New-contrib-module-hint_via_alias.patch) download | inline diff: From 2e612e565918608e192125830b821df5d1aa3c84 Mon Sep 17 00:00:00 2001 From: Robert Haas <[email protected]> Date: Wed, 18 Sep 2024 10:13:19 -0400 Subject: [PATCH v3 3/3] New contrib module: hint_via_alias. This forces a table to be merge joined, hash joined, or joined via a nested loop if the table alias starts with mj_, hj_, or nl_, respectively. It demonstrates that join_path_setup_hook is sufficient to control the join method, and is not intended for commit. --- contrib/Makefile | 1 + contrib/hint_via_alias/Makefile | 17 ++++ contrib/hint_via_alias/hint_via_alias.c | 114 ++++++++++++++++++++++++ contrib/hint_via_alias/meson.build | 12 +++ contrib/meson.build | 1 + 5 files changed, 145 insertions(+) create mode 100644 contrib/hint_via_alias/Makefile create mode 100644 contrib/hint_via_alias/hint_via_alias.c create mode 100644 contrib/hint_via_alias/meson.build diff --git a/contrib/Makefile b/contrib/Makefile index b3422616698..2b47095ce18 100644 --- a/contrib/Makefile +++ b/contrib/Makefile @@ -22,6 +22,7 @@ SUBDIRS = \ earthdistance \ file_fdw \ fuzzystrmatch \ + hint_via_alias \ hstore \ intagg \ intarray \ diff --git a/contrib/hint_via_alias/Makefile b/contrib/hint_via_alias/Makefile new file mode 100644 index 00000000000..2e0e540d352 --- /dev/null +++ b/contrib/hint_via_alias/Makefile @@ -0,0 +1,17 @@ +# contrib/hint_via_alias/Makefile + +MODULE_big = hint_via_alias +OBJS = \ + $(WIN32RES) \ + hint_via_alias.o + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = contrib/hint_via_alias +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif diff --git a/contrib/hint_via_alias/hint_via_alias.c b/contrib/hint_via_alias/hint_via_alias.c new file mode 100644 index 00000000000..fc0fbd85c22 --- /dev/null +++ b/contrib/hint_via_alias/hint_via_alias.c @@ -0,0 +1,114 @@ +/*------------------------------------------------------------------------- + * + * hint_via_alias.c + * force tables to be joined in using a nestedloop, mergejoin, or hash + * join if their alias name begins with nl_, mj_, or hj_. + * + * Copyright (c) 2016-2024, PostgreSQL Global Development Group + * + * contrib/hint_via_alias/hint_via_alias.c + *------------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "fmgr.h" +#include "optimizer/paths.h" +#include "parser/parsetree.h" + +typedef enum +{ + HVA_UNSPECIFIED, + HVA_HASHJOIN, + HVA_MERGEJOIN, + HVA_NESTLOOP +} hva_hint; + +static void hva_join_path_setup_hook(PlannerInfo *root, + RelOptInfo *joinrel, + RelOptInfo *outerrel, + RelOptInfo *innerrel, + JoinType jointype, + JoinPathExtraData *extra); +static hva_hint get_hint(PlannerInfo *root, Index relid); + +static join_path_setup_hook_type prev_join_path_setup_hook = NULL; + +PG_MODULE_MAGIC; + +void +_PG_init(void) +{ + prev_join_path_setup_hook = join_path_setup_hook; + join_path_setup_hook = hva_join_path_setup_hook; +} + +static void +hva_join_path_setup_hook(PlannerInfo *root, RelOptInfo *joinrel, + RelOptInfo *outerrel, RelOptInfo *innerrel, + JoinType jointype, JoinPathExtraData *extra) +{ + hva_hint outerhint = HVA_UNSPECIFIED; + hva_hint innerhint = HVA_UNSPECIFIED; + hva_hint hint; + + if (outerrel->reloptkind == RELOPT_BASEREL || + outerrel->reloptkind == RELOPT_OTHER_MEMBER_REL) + outerhint = get_hint(root, outerrel->relid); + + if (innerrel->reloptkind == RELOPT_BASEREL || + innerrel->reloptkind == RELOPT_OTHER_MEMBER_REL) + innerhint = get_hint(root, innerrel->relid); + + /* + * If the hints conflict, that's not necessarily an indication of user error. + * For example, if the user joins A to B and supplies different join method hints + * for A and B, we will end up using a disabled path. However, if they are joining + * A, B, and C and supply different join method hints for A and B, we could + * potentially respect both hints by avoiding a direct A-B join altogether. Even if + * it does turn out that we can't respect all the hints, we don't need any special + * handling for that here: the planner will just return a disabled path. + */ + if (outerhint != HVA_UNSPECIFIED && innerhint != HVA_UNSPECIFIED && + outerhint != innerhint) + { + extra->jsa_mask = 0; + return; + } + + if (outerhint != HVA_UNSPECIFIED) + hint = outerhint; + else + hint = innerhint; + + switch (hint) + { + case HVA_UNSPECIFIED: + break; + case HVA_NESTLOOP: + extra->jsa_mask &= JSA_NESTLOOP_ANY; + break; + case HVA_HASHJOIN: + extra->jsa_mask &= JSA_HASHJOIN; + break; + case HVA_MERGEJOIN: + extra->jsa_mask &= JSA_MERGEJOIN_ANY; + break; + } +} + +static hva_hint +get_hint(PlannerInfo *root, Index relid) +{ + RangeTblEntry *rte = planner_rt_fetch(relid, root); + + Assert(rte->eref != NULL && rte->eref->aliasname != NULL); + + if (strncmp(rte->eref->aliasname, "nl_", 3) == 0) + return HVA_NESTLOOP; + else if (strncmp(rte->eref->aliasname, "hj_", 3) == 0) + return HVA_HASHJOIN; + else if (strncmp(rte->eref->aliasname, "mj_", 3) == 0) + return HVA_MERGEJOIN; + else + return HVA_UNSPECIFIED; +} diff --git a/contrib/hint_via_alias/meson.build b/contrib/hint_via_alias/meson.build new file mode 100644 index 00000000000..7e42c5783ab --- /dev/null +++ b/contrib/hint_via_alias/meson.build @@ -0,0 +1,12 @@ +# Copyright (c) 2022-2024, PostgreSQL Global Development Group + +hint_via_alias_sources = files( + 'hint_via_alias.c', +) + +hint_via_alias = shared_module('hint_via_alias', + hint_via_alias_sources, + kwargs: contrib_mod_args, +) + +contrib_targets += hint_via_alias diff --git a/contrib/meson.build b/contrib/meson.build index 4372242c8f3..261e4c480e2 100644 --- a/contrib/meson.build +++ b/contrib/meson.build @@ -30,6 +30,7 @@ subdir('dict_xsyn') subdir('earthdistance') subdir('file_fdw') subdir('fuzzystrmatch') +subdir('hint_via_alias') subdir('hstore') subdir('hstore_plperl') subdir('hstore_plpython') -- 2.39.3 (Apple Git-145) [application/octet-stream] v3-0002-New-contrib-module-alphabet_join.patch (4.7K, ../../CA+TgmoYadvnRGM7VEqgKbuZ+c+VN3jSDx+_38e66a9yk2fiovA@mail.gmail.com/3-v3-0002-New-contrib-module-alphabet_join.patch) download | inline diff: From 3845a51afdd2d197ff61368f45a5178e46d5fded Mon Sep 17 00:00:00 2001 From: Robert Haas <[email protected]> Date: Fri, 30 Aug 2024 10:27:31 -0400 Subject: [PATCH v3 2/3] New contrib module: alphabet_join. This forces joins to be done alphabetically by alias name. It demonstrates that join_path_setup_hook is sufficient to control the join order, and is not intended for commit. --- contrib/Makefile | 1 + contrib/alphabet_join/Makefile | 17 ++++++ contrib/alphabet_join/alphabet_join.c | 74 +++++++++++++++++++++++++++ contrib/alphabet_join/meson.build | 12 +++++ contrib/meson.build | 1 + 5 files changed, 105 insertions(+) create mode 100644 contrib/alphabet_join/Makefile create mode 100644 contrib/alphabet_join/alphabet_join.c create mode 100644 contrib/alphabet_join/meson.build diff --git a/contrib/Makefile b/contrib/Makefile index abd780f2774..b3422616698 100644 --- a/contrib/Makefile +++ b/contrib/Makefile @@ -5,6 +5,7 @@ top_builddir = .. include $(top_builddir)/src/Makefile.global SUBDIRS = \ + alphabet_join \ amcheck \ auth_delay \ auto_explain \ diff --git a/contrib/alphabet_join/Makefile b/contrib/alphabet_join/Makefile new file mode 100644 index 00000000000..204bc35b3d4 --- /dev/null +++ b/contrib/alphabet_join/Makefile @@ -0,0 +1,17 @@ +# contrib/alphabet_join/Makefile + +MODULE_big = alphabet_join +OBJS = \ + $(WIN32RES) \ + alphabet_join.o + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = contrib/alphabet_join +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif diff --git a/contrib/alphabet_join/alphabet_join.c b/contrib/alphabet_join/alphabet_join.c new file mode 100644 index 00000000000..6794bded047 --- /dev/null +++ b/contrib/alphabet_join/alphabet_join.c @@ -0,0 +1,74 @@ +/*------------------------------------------------------------------------- + * + * alphabet_join.c + * force tables to be joined in alphabetical order by alias name. + * this is just a demonstration, so we don't worry about collation here. + * + * Copyright (c) 2016-2024, PostgreSQL Global Development Group + * + * contrib/alphabet_join/alphabet_join.c + *------------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "fmgr.h" +#include "optimizer/paths.h" +#include "parser/parsetree.h" + +static void aj_join_path_setup_hook(PlannerInfo *root, + RelOptInfo *joinrel, + RelOptInfo *outerrel, + RelOptInfo *innerrel, + JoinType jointype, + JoinPathExtraData *extra); + +static join_path_setup_hook_type prev_join_path_setup_hook = NULL; + +PG_MODULE_MAGIC; + +void +_PG_init(void) +{ + prev_join_path_setup_hook = join_path_setup_hook; + join_path_setup_hook = aj_join_path_setup_hook; +} + +static void +aj_join_path_setup_hook(PlannerInfo *root, RelOptInfo *joinrel, + RelOptInfo *outerrel, RelOptInfo *innerrel, + JoinType jointype, JoinPathExtraData *extra) +{ + int relid; + char *outerrel_last = NULL; + + /* Find the alphabetically last outerrel. */ + relid = -1; + while ((relid = bms_next_member(outerrel->relids, relid)) >= 0) + { + RangeTblEntry *rte = planner_rt_fetch(relid, root); + + Assert(rte->eref != NULL && rte->eref->aliasname != NULL); + + if (outerrel_last == NULL || + strcmp(outerrel_last, rte->eref->aliasname) < 0) + outerrel_last = rte->eref->aliasname; + } + + /* + * If any innerrel is alphabetically before the last outerrel, then + * this join order is not alphabetical and should be rejected. + */ + relid = -1; + while ((relid = bms_next_member(innerrel->relids, relid)) >= 0) + { + RangeTblEntry *rte = planner_rt_fetch(relid, root); + + Assert(rte->eref != NULL && rte->eref->aliasname != NULL); + + if (strcmp(rte->eref->aliasname, outerrel_last) < 0) + { + extra->jsa_mask = 0; + return; + } + } +} diff --git a/contrib/alphabet_join/meson.build b/contrib/alphabet_join/meson.build new file mode 100644 index 00000000000..437cb14af58 --- /dev/null +++ b/contrib/alphabet_join/meson.build @@ -0,0 +1,12 @@ +# Copyright (c) 2022-2024, PostgreSQL Global Development Group + +alphabet_join_sources = files( + 'alphabet_join.c', +) + +alphabet_join = shared_module('alphabet_join', + alphabet_join_sources, + kwargs: contrib_mod_args, +) + +contrib_targets += alphabet_join diff --git a/contrib/meson.build b/contrib/meson.build index 14a89068650..4372242c8f3 100644 --- a/contrib/meson.build +++ b/contrib/meson.build @@ -12,6 +12,7 @@ contrib_doc_args = { 'install_dir': contrib_doc_dir, } +subdir('alphabet_join') subdir('amcheck') subdir('auth_delay') subdir('auto_explain') -- 2.39.3 (Apple Git-145) [application/octet-stream] v3-0001-Allow-extensions-to-control-join-strategy.patch (49.0K, ../../CA+TgmoYadvnRGM7VEqgKbuZ+c+VN3jSDx+_38e66a9yk2fiovA@mail.gmail.com/4-v3-0001-Allow-extensions-to-control-join-strategy.patch) download | inline diff: From 815bd68324ce90e71e0ab40d7d9a08dea940423a Mon Sep 17 00:00:00 2001 From: Robert Haas <[email protected]> Date: Thu, 29 Aug 2024 15:38:58 -0400 Subject: [PATCH v3 1/3] Allow extensions to control join strategy. At the start of join planning, we build a bitmask called jsa_mask based on the values of the various enable_* planner GUCs, indicating which join strategies are allowed. Extensions can override this mask on a plan-wide basis using join_search_hook, or, generaly more usefully, they can change the mask for each call to add_paths_to_joinrel using a new hook called join_path_setup_hook. This is sufficient to allow an extension to force the use of particular join strategies either in general or for specific joins, and it is also sufficient to allow an extension to force the join order. There are a number of things that this patch doesn't let you do. First, it won't help if you want to implement some policy where the allowable join methods might differ for each individual combination of input paths (e.g. disable nested loops only when the inner side's parameterization is not even partially satisfied by the outer side's paramaeterization). Second, it doesn't allow you to control the uniquification strategy for a particular joinrel. Third, it doesn't give you any control over aspects of planning other than join planning. Future patches may close some of these gaps. --- src/backend/optimizer/geqo/geqo_eval.c | 22 +++-- src/backend/optimizer/geqo/geqo_main.c | 10 ++- src/backend/optimizer/geqo/geqo_pool.c | 4 +- src/backend/optimizer/path/allpaths.c | 38 +++++++-- src/backend/optimizer/path/costsize.c | 59 +++++++++----- src/backend/optimizer/path/joinpath.c | 104 +++++++++++++++++------- src/backend/optimizer/path/joinrels.c | 79 ++++++++++-------- src/backend/optimizer/plan/createplan.c | 1 + src/backend/optimizer/util/pathnode.c | 6 +- src/backend/optimizer/util/relnode.c | 17 ++-- src/include/nodes/pathnodes.h | 2 + src/include/optimizer/cost.h | 4 +- src/include/optimizer/geqo.h | 9 +- src/include/optimizer/geqo_pool.h | 2 +- src/include/optimizer/pathnode.h | 9 +- src/include/optimizer/paths.h | 56 +++++++++++-- 16 files changed, 292 insertions(+), 130 deletions(-) diff --git a/src/backend/optimizer/geqo/geqo_eval.c b/src/backend/optimizer/geqo/geqo_eval.c index d2f7f4e5f3c..6b1d8df3ff6 100644 --- a/src/backend/optimizer/geqo/geqo_eval.c +++ b/src/backend/optimizer/geqo/geqo_eval.c @@ -40,7 +40,7 @@ typedef struct } Clump; static List *merge_clump(PlannerInfo *root, List *clumps, Clump *new_clump, - int num_gene, bool force); + int num_gene, bool force, unsigned jsa_mask); static bool desirable_join(PlannerInfo *root, RelOptInfo *outer_rel, RelOptInfo *inner_rel); @@ -54,7 +54,7 @@ static bool desirable_join(PlannerInfo *root, * returns DBL_MAX. */ Cost -geqo_eval(PlannerInfo *root, Gene *tour, int num_gene) +geqo_eval(PlannerInfo *root, Gene *tour, int num_gene, unsigned jsa_mask) { MemoryContext mycontext; MemoryContext oldcxt; @@ -99,7 +99,7 @@ geqo_eval(PlannerInfo *root, Gene *tour, int num_gene) root->join_rel_hash = NULL; /* construct the best path for the given combination of relations */ - joinrel = gimme_tree(root, tour, num_gene); + joinrel = gimme_tree(root, tour, num_gene, jsa_mask); /* * compute fitness, if we found a valid join @@ -160,7 +160,7 @@ geqo_eval(PlannerInfo *root, Gene *tour, int num_gene) * since there's no provision for un-clumping, this must lead to failure.) */ RelOptInfo * -gimme_tree(PlannerInfo *root, Gene *tour, int num_gene) +gimme_tree(PlannerInfo *root, Gene *tour, int num_gene, unsigned jsa_mask) { GeqoPrivateData *private = (GeqoPrivateData *) root->join_search_private; List *clumps; @@ -196,7 +196,8 @@ gimme_tree(PlannerInfo *root, Gene *tour, int num_gene) cur_clump->size = 1; /* Merge it into the clumps list, using only desirable joins */ - clumps = merge_clump(root, clumps, cur_clump, num_gene, false); + clumps = merge_clump(root, clumps, cur_clump, num_gene, false, + jsa_mask); } if (list_length(clumps) > 1) @@ -210,7 +211,8 @@ gimme_tree(PlannerInfo *root, Gene *tour, int num_gene) { Clump *clump = (Clump *) lfirst(lc); - fclumps = merge_clump(root, fclumps, clump, num_gene, true); + fclumps = merge_clump(root, fclumps, clump, num_gene, true, + jsa_mask); } clumps = fclumps; } @@ -236,7 +238,7 @@ gimme_tree(PlannerInfo *root, Gene *tour, int num_gene) */ static List * merge_clump(PlannerInfo *root, List *clumps, Clump *new_clump, int num_gene, - bool force) + bool force, unsigned jsa_mask) { ListCell *lc; int pos; @@ -259,7 +261,8 @@ merge_clump(PlannerInfo *root, List *clumps, Clump *new_clump, int num_gene, */ joinrel = make_join_rel(root, old_clump->joinrel, - new_clump->joinrel); + new_clump->joinrel, + jsa_mask); /* Keep searching if join order is not valid */ if (joinrel) @@ -292,7 +295,8 @@ merge_clump(PlannerInfo *root, List *clumps, Clump *new_clump, int num_gene, * others. When no further merge is possible, we'll reinsert * it into the list. */ - return merge_clump(root, clumps, old_clump, num_gene, force); + return merge_clump(root, clumps, old_clump, num_gene, force, + jsa_mask); } } } diff --git a/src/backend/optimizer/geqo/geqo_main.c b/src/backend/optimizer/geqo/geqo_main.c index 0c5540e2af4..c69b60d2e95 100644 --- a/src/backend/optimizer/geqo/geqo_main.c +++ b/src/backend/optimizer/geqo/geqo_main.c @@ -69,7 +69,8 @@ static int gimme_number_generations(int pool_size); */ RelOptInfo * -geqo(PlannerInfo *root, int number_of_rels, List *initial_rels) +geqo(PlannerInfo *root, int number_of_rels, List *initial_rels, + unsigned jsa_mask) { GeqoPrivateData private; int generation; @@ -116,7 +117,7 @@ geqo(PlannerInfo *root, int number_of_rels, List *initial_rels) pool = alloc_pool(root, pool_size, number_of_rels); /* random initialization of the pool */ - random_init_pool(root, pool); + random_init_pool(root, pool, jsa_mask); /* sort the pool according to cheapest path as fitness */ sort_pool(root, pool); /* we have to do it only one time, since all @@ -218,7 +219,8 @@ geqo(PlannerInfo *root, int number_of_rels, List *initial_rels) /* EVALUATE FITNESS */ - kid->worth = geqo_eval(root, kid->string, pool->string_length); + kid->worth = geqo_eval(root, kid->string, pool->string_length, + jsa_mask); /* push the kid into the wilderness of life according to its worth */ spread_chromo(root, kid, pool); @@ -269,7 +271,7 @@ geqo(PlannerInfo *root, int number_of_rels, List *initial_rels) */ best_tour = (Gene *) pool->data[0].string; - best_rel = gimme_tree(root, best_tour, pool->string_length); + best_rel = gimme_tree(root, best_tour, pool->string_length, jsa_mask); if (best_rel == NULL) elog(ERROR, "geqo failed to make a valid plan"); diff --git a/src/backend/optimizer/geqo/geqo_pool.c b/src/backend/optimizer/geqo/geqo_pool.c index 0ec97d5a3f1..dbec3c50943 100644 --- a/src/backend/optimizer/geqo/geqo_pool.c +++ b/src/backend/optimizer/geqo/geqo_pool.c @@ -88,7 +88,7 @@ free_pool(PlannerInfo *root, Pool *pool) * initialize genetic pool */ void -random_init_pool(PlannerInfo *root, Pool *pool) +random_init_pool(PlannerInfo *root, Pool *pool, unsigned jsa_mask) { Chromosome *chromo = (Chromosome *) pool->data; int i; @@ -107,7 +107,7 @@ random_init_pool(PlannerInfo *root, Pool *pool) { init_tour(root, chromo[i].string, pool->string_length); pool->data[i].worth = geqo_eval(root, chromo[i].string, - pool->string_length); + pool->string_length, jsa_mask); if (pool->data[i].worth < DBL_MAX) i++; else diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index 172edb643a4..61863482346 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -898,7 +898,7 @@ set_tablesample_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry * bms_membership(root->all_query_rels) != BMS_SINGLETON) && !(GetTsmRoutine(rte->tablesample->tsmhandler)->repeatable_across_scans)) { - path = (Path *) create_material_path(rel, path); + path = (Path *) create_material_path(rel, path, enable_material); } add_path(rel, path); @@ -3371,6 +3371,29 @@ make_rel_from_joinlist(PlannerInfo *root, List *joinlist) } else { + unsigned jsa_mask; + + /* Compute the initial join strategy advice mask. */ + jsa_mask = JSA_FOREIGN; + if (enable_hashjoin) + jsa_mask |= JSA_HASHJOIN; + if (enable_mergejoin) + { + jsa_mask |= JSA_MERGEJOIN_PLAIN; + if (enable_material) + jsa_mask |= JSA_MERGEJOIN_MATERIALIZE; + } + if (enable_nestloop) + { + jsa_mask |= JSA_NESTLOOP_PLAIN; + if (enable_material) + jsa_mask |= JSA_NESTLOOP_MATERIALIZE; + if (enable_memoize) + jsa_mask |= JSA_NESTLOOP_MEMOIZE; + } + if (enable_partitionwise_join) + jsa_mask |= JSA_PARTITIONWISE; + /* * Consider the different orders in which we could join the rels, * using a plugin, GEQO, or the regular join search code. @@ -3381,11 +3404,13 @@ make_rel_from_joinlist(PlannerInfo *root, List *joinlist) root->initial_rels = initial_rels; if (join_search_hook) - return (*join_search_hook) (root, levels_needed, initial_rels); + return (*join_search_hook) (root, levels_needed, initial_rels, + jsa_mask); else if (enable_geqo && levels_needed >= geqo_threshold) - return geqo(root, levels_needed, initial_rels); + return geqo(root, levels_needed, initial_rels, jsa_mask); else - return standard_join_search(root, levels_needed, initial_rels); + return standard_join_search(root, levels_needed, initial_rels, + jsa_mask); } } @@ -3419,7 +3444,8 @@ make_rel_from_joinlist(PlannerInfo *root, List *joinlist) * original states of those data structures. See geqo_eval() for an example. */ RelOptInfo * -standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels) +standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels, + unsigned jsa_mask) { int lev; RelOptInfo *rel; @@ -3454,7 +3480,7 @@ standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels) * level, and build paths for making each one from every available * pair of lower-level relations. */ - join_search_one_level(root, lev); + join_search_one_level(root, lev, jsa_mask); /* * Run generate_partitionwise_join_paths() and diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c index e1523d15df1..0700c634bf4 100644 --- a/src/backend/optimizer/path/costsize.c +++ b/src/backend/optimizer/path/costsize.c @@ -2481,7 +2481,7 @@ cost_merge_append(Path *path, PlannerInfo *root, */ void cost_material(Path *path, - int input_disabled_nodes, + bool enabled, int input_disabled_nodes, Cost input_startup_cost, Cost input_total_cost, double tuples, int width) { @@ -2519,7 +2519,7 @@ cost_material(Path *path, run_cost += seq_page_cost * npages; } - path->disabled_nodes = input_disabled_nodes + (enable_material ? 0 : 1); + path->disabled_nodes = input_disabled_nodes + (enabled ? 0 : 1); path->startup_cost = startup_cost; path->total_cost = startup_cost + run_cost; } @@ -3266,7 +3266,7 @@ cost_group(Path *path, PlannerInfo *root, */ void initial_cost_nestloop(PlannerInfo *root, JoinCostWorkspace *workspace, - JoinType jointype, + JoinType jointype, unsigned nestloop_subtype, Path *outer_path, Path *inner_path, JoinPathExtraData *extra) { @@ -3280,7 +3280,7 @@ initial_cost_nestloop(PlannerInfo *root, JoinCostWorkspace *workspace, Cost inner_rescan_run_cost; /* Count up disabled nodes. */ - disabled_nodes = enable_nestloop ? 0 : 1; + disabled_nodes = (extra->jsa_mask & nestloop_subtype) == 0 ? 1 : 0; disabled_nodes += inner_path->disabled_nodes; disabled_nodes += outer_path->disabled_nodes; @@ -3676,7 +3676,12 @@ initial_cost_mergejoin(PlannerInfo *root, JoinCostWorkspace *workspace, Assert(outerstartsel <= outerendsel); Assert(innerstartsel <= innerendsel); - disabled_nodes = enable_mergejoin ? 0 : 1; + /* + * Assume for now that this node is not itself disabled. We'll sort out + * whether that's really the case in final_cost_mergejoin(); here, we'll + * just account for any disabled child nodes. + */ + disabled_nodes = 0; /* cost of source data */ @@ -3814,9 +3819,6 @@ final_cost_mergejoin(PlannerInfo *root, MergePath *path, rescannedtuples; double rescanratio; - /* Set the number of disabled nodes. */ - path->jpath.path.disabled_nodes = workspace->disabled_nodes; - /* Protect some assumptions below that rowcounts aren't zero */ if (inner_path_rows <= 0) inner_path_rows = 1; @@ -3943,16 +3945,20 @@ final_cost_mergejoin(PlannerInfo *root, MergePath *path, path->materialize_inner = false; /* - * Prefer materializing if it looks cheaper, unless the user has asked to - * suppress materialization. + * If merge joins with materialization are enabled, then choose + * materialization if either (a) it looks cheaper or (b) merge joins + * without materialization are disabled. */ - else if (enable_material && mat_inner_cost < bare_inner_cost) + else if ((extra->jsa_mask & JSA_MERGEJOIN_MATERIALIZE) != 0 && + (mat_inner_cost < bare_inner_cost || + (extra->jsa_mask & JSA_MERGEJOIN_PLAIN) == 0)) path->materialize_inner = true; /* - * Even if materializing doesn't look cheaper, we *must* do it if the - * inner path is to be used directly (without sorting) and it doesn't - * support mark/restore. + * Regardless of what plan shapes are enabled and what the costs seem + * to be, we *must* materialize it if the inner path is to be used directly + * (without sorting) and it doesn't support mark/restore. Planner failure + * is not an option! * * Since the inner side must be ordered, and only Sorts and IndexScans can * create order to begin with, and they both support mark/restore, you @@ -3960,10 +3966,6 @@ final_cost_mergejoin(PlannerInfo *root, MergePath *path, * merge joins can *preserve* the order of their inputs, so they can be * selected as the input of a mergejoin, and they don't support * mark/restore at present. - * - * We don't test the value of enable_material here, because - * materialization is required for correctness in this case, and turning - * it off does not entitle us to deliver an invalid plan. */ else if (innersortkeys == NIL && !ExecSupportsMarkRestore(inner_path)) @@ -3980,7 +3982,8 @@ final_cost_mergejoin(PlannerInfo *root, MergePath *path, * rather than necessary for correctness, we skip it if enable_material is * off. */ - else if (enable_material && innersortkeys != NIL && + else if ((extra->jsa_mask & JSA_MERGEJOIN_MATERIALIZE) != 0 && + innersortkeys != NIL && relation_byte_size(inner_path_rows, inner_path->pathtarget->width) > (work_mem * 1024L)) @@ -3988,11 +3991,25 @@ final_cost_mergejoin(PlannerInfo *root, MergePath *path, else path->materialize_inner = false; - /* Charge the right incremental cost for the chosen case */ + /* Get the number of disabled nodes, not yet including this one. */ + path->jpath.path.disabled_nodes = workspace->disabled_nodes; + + /* + * Charge the right incremental cost for the chosen case, and increment + * disabled_nodes if appropriate. + */ if (path->materialize_inner) + { run_cost += mat_inner_cost; + if ((extra->jsa_mask & JSA_MERGEJOIN_MATERIALIZE) == 0) + ++path->jpath.path.disabled_nodes; + } else + { run_cost += bare_inner_cost; + if ((extra->jsa_mask & JSA_MERGEJOIN_PLAIN) == 0) + ++path->jpath.path.disabled_nodes; + } /* CPU costs */ @@ -4132,7 +4149,7 @@ initial_cost_hashjoin(PlannerInfo *root, JoinCostWorkspace *workspace, size_t space_allowed; /* unused */ /* Count up disabled nodes. */ - disabled_nodes = enable_hashjoin ? 0 : 1; + disabled_nodes = (extra->jsa_mask & JSA_HASHJOIN) == 0 ? 1 : 0; disabled_nodes += inner_path->disabled_nodes; disabled_nodes += outer_path->disabled_nodes; diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c index a244300463c..90f3829d04d 100644 --- a/src/backend/optimizer/path/joinpath.c +++ b/src/backend/optimizer/path/joinpath.c @@ -28,8 +28,9 @@ #include "utils/lsyscache.h" #include "utils/typcache.h" -/* Hook for plugins to get control in add_paths_to_joinrel() */ +/* Hooks for plugins to get control in add_paths_to_joinrel() */ set_join_pathlist_hook_type set_join_pathlist_hook = NULL; +join_path_setup_hook_type join_path_setup_hook = NULL; /* * Paths parameterized by a parent rel can be considered to be parameterized @@ -129,7 +130,8 @@ add_paths_to_joinrel(PlannerInfo *root, RelOptInfo *innerrel, JoinType jointype, SpecialJoinInfo *sjinfo, - List *restrictlist) + List *restrictlist, + unsigned jsa_mask) { JoinPathExtraData extra; bool mergejoin_allowed = true; @@ -152,6 +154,7 @@ add_paths_to_joinrel(PlannerInfo *root, extra.mergeclause_list = NIL; extra.sjinfo = sjinfo; extra.param_source_rels = NULL; + extra.jsa_mask = jsa_mask; /* * See if the inner relation is provably unique for this outer rel. @@ -203,13 +206,39 @@ add_paths_to_joinrel(PlannerInfo *root, break; } + /* + * Give extensions a chance to take control. In particular, an extension + * might want to modify extra.jsa_mask so as to provide join strategy + * advice. An extension can also override jsa_mask on a query-wide basis + * by using join_search_hook, but extensions that want to provide + * different advice when joining different rels, or even different advice + * for the same joinrel based on the choice of innerrel and outerrel, need + * to use this hook. + * + * A very simple way for an extension to use this hook is to set + * extra.jsa_mask = 0, if it simply doesn't want any of the paths + * generated by this call to add_paths_to_joinrel() to be selected. An + * extension could use this technique to constrain the join order, since + * it could thereby arrange to reject all paths from join orders that it + * does not like. An extension can also selectively clear bits from + * extra.jsa_mask to rule out specific techniques for specific joins, or + * even replace the mask entirely. + * + * NB: Below this point, this function should be careful to reference only + * extra.jsa_mask, and not jsa_mask directly, to avoid disregarding any + * changes made by the hook we're about to call. + */ + if (join_path_setup_hook) + join_path_setup_hook(root, joinrel, outerrel, innerrel, + jointype, &extra); + /* * Find potential mergejoin clauses. We can skip this if we are not * interested in doing a mergejoin. However, mergejoin may be our only - * way of implementing a full outer join, so override enable_mergejoin if - * it's a full join. + * way of implementing a full outer join, so in that case we don't care + * whether mergejoins are disabled. */ - if (enable_mergejoin || jointype == JOIN_FULL) + if ((extra.jsa_mask & JSA_MERGEJOIN_ANY) != 0 || jointype == JOIN_FULL) extra.mergeclause_list = select_mergejoin_clauses(root, joinrel, outerrel, @@ -317,10 +346,10 @@ add_paths_to_joinrel(PlannerInfo *root, /* * 4. Consider paths where both outer and inner relations must be hashed - * before being joined. As above, disregard enable_hashjoin for full - * joins, because there may be no other alternative. + * before being joined. As above, when it's a full join, we must try this + * even when the path type is disabled, because it may be our only option. */ - if (enable_hashjoin || jointype == JOIN_FULL) + if ((extra.jsa_mask & JSA_HASHJOIN) != 0 || jointype == JOIN_FULL) hash_inner_and_outer(root, joinrel, outerrel, innerrel, jointype, &extra); @@ -329,7 +358,7 @@ add_paths_to_joinrel(PlannerInfo *root, * to the same server and assigned to the same user to check access * permissions as, give the FDW a chance to push down joins. */ - if (joinrel->fdwroutine && + if ((extra.jsa_mask & JSA_FOREIGN) != 0 && joinrel->fdwroutine && joinrel->fdwroutine->GetForeignJoinPaths) joinrel->fdwroutine->GetForeignJoinPaths(root, joinrel, outerrel, innerrel, @@ -338,8 +367,13 @@ add_paths_to_joinrel(PlannerInfo *root, /* * 6. Finally, give extensions a chance to manipulate the path list. They * could add new paths (such as CustomPaths) by calling add_path(), or - * add_partial_path() if parallel aware. They could also delete or modify - * paths added by the core code. + * add_partial_path() if parallel aware. + * + * In theory, extensions could also use this hook to delete or modify + * paths added by the core code, but in practice this is difficult to make + * work, since it's too late to get back any paths that have already been + * discarded by add_path() or add_partial_path(). If you're trying to + * suppress paths, consider using join_path_setup_hook instead. */ if (set_join_pathlist_hook) set_join_pathlist_hook(root, joinrel, outerrel, innerrel, @@ -829,6 +863,7 @@ try_nestloop_path(PlannerInfo *root, Path *inner_path, List *pathkeys, JoinType jointype, + unsigned nestloop_subtype, JoinPathExtraData *extra) { Relids required_outer; @@ -913,7 +948,7 @@ try_nestloop_path(PlannerInfo *root, * The latter two steps are expensive enough to make this two-phase * methodology worthwhile. */ - initial_cost_nestloop(root, &workspace, jointype, + initial_cost_nestloop(root, &workspace, jointype, nestloop_subtype, outer_path, inner_path, extra); if (add_path_precheck(joinrel, workspace.disabled_nodes, @@ -951,6 +986,7 @@ try_partial_nestloop_path(PlannerInfo *root, Path *inner_path, List *pathkeys, JoinType jointype, + unsigned nestloop_subtype, JoinPathExtraData *extra) { JoinCostWorkspace workspace; @@ -998,7 +1034,7 @@ try_partial_nestloop_path(PlannerInfo *root, * Before creating a path, get a quick lower bound on what it is likely to * cost. Bail out right away if it looks terrible. */ - initial_cost_nestloop(root, &workspace, jointype, + initial_cost_nestloop(root, &workspace, jointype, nestloop_subtype, outer_path, inner_path, extra); if (!add_partial_path_precheck(joinrel, workspace.disabled_nodes, workspace.total_cost, pathkeys)) @@ -1893,20 +1929,21 @@ match_unsorted_outer(PlannerInfo *root, if (inner_cheapest_total == NULL) return; inner_cheapest_total = (Path *) - create_unique_path(root, innerrel, inner_cheapest_total, extra->sjinfo); + create_unique_path(root, innerrel, inner_cheapest_total, + extra->sjinfo); Assert(inner_cheapest_total); } else if (nestjoinOK) { /* - * Consider materializing the cheapest inner path, unless - * enable_material is off or the path in question materializes its - * output anyway. + * Consider materializing the cheapest inner path, unless that is + * disabled or the path in question materializes its output anyway. */ - if (enable_material && inner_cheapest_total != NULL && + if ((extra->jsa_mask & JSA_NESTLOOP_MATERIALIZE) != 0 && + inner_cheapest_total != NULL && !ExecMaterializesOutput(inner_cheapest_total->pathtype)) matpath = (Path *) - create_material_path(innerrel, inner_cheapest_total); + create_material_path(innerrel, inner_cheapest_total, true); } foreach(lc1, outerrel->pathlist) @@ -1954,6 +1991,7 @@ match_unsorted_outer(PlannerInfo *root, inner_cheapest_total, merge_pathkeys, jointype, + JSA_NESTLOOP_PLAIN, extra); } else if (nestjoinOK) @@ -1977,6 +2015,7 @@ match_unsorted_outer(PlannerInfo *root, innerpath, merge_pathkeys, jointype, + JSA_NESTLOOP_PLAIN, extra); /* @@ -1993,6 +2032,7 @@ match_unsorted_outer(PlannerInfo *root, mpath, merge_pathkeys, jointype, + JSA_NESTLOOP_MEMOIZE, extra); } @@ -2004,6 +2044,7 @@ match_unsorted_outer(PlannerInfo *root, matpath, merge_pathkeys, jointype, + JSA_NESTLOOP_MATERIALIZE, extra); } @@ -2135,20 +2176,18 @@ consider_parallel_nestloop(PlannerInfo *root, /* * Consider materializing the cheapest inner path, unless: 1) we're doing * JOIN_UNIQUE_INNER, because in this case we have to unique-ify the - * cheapest inner path, 2) enable_material is off, 3) the cheapest inner - * path is not parallel-safe, 4) the cheapest inner path is parameterized - * by the outer rel, or 5) the cheapest inner path materializes its output - * anyway. + * cheapest inner path, 2) materialization is disabled here, 3) the + * cheapest inner path is not parallel-safe, 4) the cheapest inner path is + * parameterized by the outer rel, or 5) the cheapest inner path + * materializes its output anyway. */ if (save_jointype != JOIN_UNIQUE_INNER && - enable_material && inner_cheapest_total->parallel_safe && + (extra->jsa_mask & JSA_NESTLOOP_MATERIALIZE) != 0 && + inner_cheapest_total->parallel_safe && !PATH_PARAM_BY_REL(inner_cheapest_total, outerrel) && !ExecMaterializesOutput(inner_cheapest_total->pathtype)) - { matpath = (Path *) - create_material_path(innerrel, inner_cheapest_total); - Assert(matpath->parallel_safe); - } + create_material_path(innerrel, inner_cheapest_total, true); foreach(lc1, outerrel->partial_pathlist) { @@ -2193,7 +2232,8 @@ consider_parallel_nestloop(PlannerInfo *root, } try_partial_nestloop_path(root, joinrel, outerpath, innerpath, - pathkeys, jointype, extra); + pathkeys, jointype, JSA_NESTLOOP_PLAIN, + extra); /* * Try generating a memoize path and see if that makes the nested @@ -2204,13 +2244,15 @@ consider_parallel_nestloop(PlannerInfo *root, extra); if (mpath != NULL) try_partial_nestloop_path(root, joinrel, outerpath, mpath, - pathkeys, jointype, extra); + pathkeys, jointype, + JSA_NESTLOOP_MEMOIZE, extra); } /* Also consider materialized form of the cheapest inner path */ if (matpath != NULL) try_partial_nestloop_path(root, joinrel, outerpath, matpath, - pathkeys, jointype, extra); + pathkeys, jointype, + JSA_NESTLOOP_MATERIALIZE, extra); } } diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c index 7db5e30eef8..dad5c555365 100644 --- a/src/backend/optimizer/path/joinrels.c +++ b/src/backend/optimizer/path/joinrels.c @@ -26,10 +26,12 @@ static void make_rels_by_clause_joins(PlannerInfo *root, RelOptInfo *old_rel, List *other_rels, - int first_rel_idx); + int first_rel_idx, + unsigned jsa_mask); static void make_rels_by_clauseless_joins(PlannerInfo *root, RelOptInfo *old_rel, - List *other_rels); + List *other_rels, + unsigned jsa_mask); static bool has_join_restriction(PlannerInfo *root, RelOptInfo *rel); static bool has_legal_joinclause(PlannerInfo *root, RelOptInfo *rel); static bool restriction_is_constant_false(List *restrictlist, @@ -37,11 +39,13 @@ static bool restriction_is_constant_false(List *restrictlist, bool only_pushed_down); static void populate_joinrel_with_paths(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2, RelOptInfo *joinrel, - SpecialJoinInfo *sjinfo, List *restrictlist); + SpecialJoinInfo *sjinfo, + List *restrictlist, unsigned jsa_mask); static void try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2, RelOptInfo *joinrel, SpecialJoinInfo *parent_sjinfo, - List *parent_restrictlist); + List *parent_restrictlist, + unsigned jsa_mask); static SpecialJoinInfo *build_child_join_sjinfo(PlannerInfo *root, SpecialJoinInfo *parent_sjinfo, Relids left_relids, Relids right_relids); @@ -69,7 +73,7 @@ static void get_matching_part_pairs(PlannerInfo *root, RelOptInfo *joinrel, * The result is returned in root->join_rel_level[level]. */ void -join_search_one_level(PlannerInfo *root, int level) +join_search_one_level(PlannerInfo *root, int level, unsigned jsa_mask) { List **joinrels = root->join_rel_level; ListCell *r; @@ -114,7 +118,8 @@ join_search_one_level(PlannerInfo *root, int level) else first_rel = 0; - make_rels_by_clause_joins(root, old_rel, joinrels[1], first_rel); + make_rels_by_clause_joins(root, old_rel, joinrels[1], first_rel, + jsa_mask); } else { @@ -132,7 +137,8 @@ join_search_one_level(PlannerInfo *root, int level) */ make_rels_by_clauseless_joins(root, old_rel, - joinrels[1]); + joinrels[1], + jsa_mask); } } @@ -189,7 +195,7 @@ join_search_one_level(PlannerInfo *root, int level) if (have_relevant_joinclause(root, old_rel, new_rel) || have_join_order_restriction(root, old_rel, new_rel)) { - (void) make_join_rel(root, old_rel, new_rel); + (void) make_join_rel(root, old_rel, new_rel, jsa_mask); } } } @@ -227,7 +233,8 @@ join_search_one_level(PlannerInfo *root, int level) make_rels_by_clauseless_joins(root, old_rel, - joinrels[1]); + joinrels[1], + jsa_mask); } /*---------- @@ -279,7 +286,8 @@ static void make_rels_by_clause_joins(PlannerInfo *root, RelOptInfo *old_rel, List *other_rels, - int first_rel_idx) + int first_rel_idx, + unsigned jsa_mask) { ListCell *l; @@ -291,7 +299,7 @@ make_rels_by_clause_joins(PlannerInfo *root, (have_relevant_joinclause(root, old_rel, other_rel) || have_join_order_restriction(root, old_rel, other_rel))) { - (void) make_join_rel(root, old_rel, other_rel); + (void) make_join_rel(root, old_rel, other_rel, jsa_mask); } } } @@ -312,7 +320,8 @@ make_rels_by_clause_joins(PlannerInfo *root, static void make_rels_by_clauseless_joins(PlannerInfo *root, RelOptInfo *old_rel, - List *other_rels) + List *other_rels, + unsigned jsa_mask) { ListCell *l; @@ -322,7 +331,7 @@ make_rels_by_clauseless_joins(PlannerInfo *root, if (!bms_overlap(other_rel->relids, old_rel->relids)) { - (void) make_join_rel(root, old_rel, other_rel); + (void) make_join_rel(root, old_rel, other_rel, jsa_mask); } } } @@ -701,7 +710,8 @@ init_dummy_sjinfo(SpecialJoinInfo *sjinfo, Relids left_relids, * turned into joins. */ RelOptInfo * -make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2) +make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2, + unsigned jsa_mask) { Relids joinrelids; SpecialJoinInfo *sjinfo; @@ -759,7 +769,7 @@ make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2) */ joinrel = build_join_rel(root, joinrelids, rel1, rel2, sjinfo, pushed_down_joins, - &restrictlist); + &restrictlist, jsa_mask); /* * If we've already proven this join is empty, we needn't consider any @@ -773,7 +783,7 @@ make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2) /* Add paths to the join relation. */ populate_joinrel_with_paths(root, rel1, rel2, joinrel, sjinfo, - restrictlist); + restrictlist, jsa_mask); bms_free(joinrelids); @@ -892,7 +902,8 @@ add_outer_joins_to_relids(PlannerInfo *root, Relids input_relids, static void populate_joinrel_with_paths(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2, RelOptInfo *joinrel, - SpecialJoinInfo *sjinfo, List *restrictlist) + SpecialJoinInfo *sjinfo, List *restrictlist, + unsigned jsa_mask) { /* * Consider paths using each rel as both outer and inner. Depending on @@ -923,10 +934,10 @@ populate_joinrel_with_paths(PlannerInfo *root, RelOptInfo *rel1, } add_paths_to_joinrel(root, joinrel, rel1, rel2, JOIN_INNER, sjinfo, - restrictlist); + restrictlist, jsa_mask); add_paths_to_joinrel(root, joinrel, rel2, rel1, JOIN_INNER, sjinfo, - restrictlist); + restrictlist, jsa_mask); break; case JOIN_LEFT: if (is_dummy_rel(rel1) || @@ -940,10 +951,10 @@ populate_joinrel_with_paths(PlannerInfo *root, RelOptInfo *rel1, mark_dummy_rel(rel2); add_paths_to_joinrel(root, joinrel, rel1, rel2, JOIN_LEFT, sjinfo, - restrictlist); + restrictlist, jsa_mask); add_paths_to_joinrel(root, joinrel, rel2, rel1, JOIN_RIGHT, sjinfo, - restrictlist); + restrictlist, jsa_mask); break; case JOIN_FULL: if ((is_dummy_rel(rel1) && is_dummy_rel(rel2)) || @@ -954,10 +965,10 @@ populate_joinrel_with_paths(PlannerInfo *root, RelOptInfo *rel1, } add_paths_to_joinrel(root, joinrel, rel1, rel2, JOIN_FULL, sjinfo, - restrictlist); + restrictlist, jsa_mask); add_paths_to_joinrel(root, joinrel, rel2, rel1, JOIN_FULL, sjinfo, - restrictlist); + restrictlist, jsa_mask); /* * If there are join quals that aren't mergeable or hashable, we @@ -990,10 +1001,10 @@ populate_joinrel_with_paths(PlannerInfo *root, RelOptInfo *rel1, } add_paths_to_joinrel(root, joinrel, rel1, rel2, JOIN_SEMI, sjinfo, - restrictlist); + restrictlist, jsa_mask); add_paths_to_joinrel(root, joinrel, rel2, rel1, JOIN_RIGHT_SEMI, sjinfo, - restrictlist); + restrictlist, jsa_mask); } /* @@ -1016,10 +1027,10 @@ populate_joinrel_with_paths(PlannerInfo *root, RelOptInfo *rel1, } add_paths_to_joinrel(root, joinrel, rel1, rel2, JOIN_UNIQUE_INNER, sjinfo, - restrictlist); + restrictlist, jsa_mask); add_paths_to_joinrel(root, joinrel, rel2, rel1, JOIN_UNIQUE_OUTER, sjinfo, - restrictlist); + restrictlist, jsa_mask); } break; case JOIN_ANTI: @@ -1034,10 +1045,10 @@ populate_joinrel_with_paths(PlannerInfo *root, RelOptInfo *rel1, mark_dummy_rel(rel2); add_paths_to_joinrel(root, joinrel, rel1, rel2, JOIN_ANTI, sjinfo, - restrictlist); + restrictlist, jsa_mask); add_paths_to_joinrel(root, joinrel, rel2, rel1, JOIN_RIGHT_ANTI, sjinfo, - restrictlist); + restrictlist, jsa_mask); break; default: /* other values not expected here */ @@ -1046,7 +1057,8 @@ populate_joinrel_with_paths(PlannerInfo *root, RelOptInfo *rel1, } /* Apply partitionwise join technique, if possible. */ - try_partitionwise_join(root, rel1, rel2, joinrel, sjinfo, restrictlist); + try_partitionwise_join(root, rel1, rel2, joinrel, sjinfo, restrictlist, + jsa_mask); } @@ -1480,7 +1492,7 @@ restriction_is_constant_false(List *restrictlist, static void try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2, RelOptInfo *joinrel, SpecialJoinInfo *parent_sjinfo, - List *parent_restrictlist) + List *parent_restrictlist, unsigned jsa_mask) { bool rel1_is_simple = IS_SIMPLE_REL(rel1); bool rel2_is_simple = IS_SIMPLE_REL(rel2); @@ -1662,7 +1674,8 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2, { child_joinrel = build_child_join_rel(root, child_rel1, child_rel2, joinrel, child_restrictlist, - child_sjinfo, nappinfos, appinfos); + child_sjinfo, nappinfos, + appinfos, jsa_mask); joinrel->part_rels[cnt_parts] = child_joinrel; joinrel->live_parts = bms_add_member(joinrel->live_parts, cnt_parts); joinrel->all_partrels = bms_add_members(joinrel->all_partrels, @@ -1677,7 +1690,7 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2, /* And make paths for the child join */ populate_joinrel_with_paths(root, child_rel1, child_rel2, child_joinrel, child_sjinfo, - child_restrictlist); + child_restrictlist, jsa_mask); /* * When there are thousands of partitions involved, this loop will diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index bb45ef318fb..14fd480fa89 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -6550,6 +6550,7 @@ materialize_finished_plan(Plan *subplan) /* Set cost data */ cost_material(&matpath, + enable_material, subplan->disabled_nodes, subplan->startup_cost, subplan->total_cost, diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index fc97bf6ee26..a704d0cd7bb 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -1631,7 +1631,7 @@ create_group_result_path(PlannerInfo *root, RelOptInfo *rel, * pathnode. */ MaterialPath * -create_material_path(RelOptInfo *rel, Path *subpath) +create_material_path(RelOptInfo *rel, Path *subpath, bool enabled) { MaterialPath *pathnode = makeNode(MaterialPath); @@ -1650,6 +1650,7 @@ create_material_path(RelOptInfo *rel, Path *subpath) pathnode->subpath = subpath; cost_material(&pathnode->path, + enabled, subpath->disabled_nodes, subpath->startup_cost, subpath->total_cost, @@ -4158,7 +4159,8 @@ reparameterize_path(PlannerInfo *root, Path *path, loop_count); if (spath == NULL) return NULL; - return (Path *) create_material_path(rel, spath); + return (Path *) create_material_path(rel, spath, + enable_material); } case T_Memoize: { diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c index d7266e4cdba..9e328c5ac7c 100644 --- a/src/backend/optimizer/util/relnode.c +++ b/src/backend/optimizer/util/relnode.c @@ -69,7 +69,8 @@ static void build_joinrel_partition_info(PlannerInfo *root, RelOptInfo *joinrel, RelOptInfo *outer_rel, RelOptInfo *inner_rel, SpecialJoinInfo *sjinfo, - List *restrictlist); + List *restrictlist, + unsigned jsa_mask); static bool have_partkey_equi_join(PlannerInfo *root, RelOptInfo *joinrel, RelOptInfo *rel1, RelOptInfo *rel2, JoinType jointype, List *restrictlist); @@ -668,7 +669,8 @@ build_join_rel(PlannerInfo *root, RelOptInfo *inner_rel, SpecialJoinInfo *sjinfo, List *pushed_down_joins, - List **restrictlist_ptr) + List **restrictlist_ptr, + unsigned jsa_mask) { RelOptInfo *joinrel; List *restrictlist; @@ -817,7 +819,7 @@ build_join_rel(PlannerInfo *root, /* Store the partition information. */ build_joinrel_partition_info(root, joinrel, outer_rel, inner_rel, sjinfo, - restrictlist); + restrictlist, jsa_mask); /* * Set estimates of the joinrel's size. @@ -882,7 +884,8 @@ RelOptInfo * build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel, RelOptInfo *inner_rel, RelOptInfo *parent_joinrel, List *restrictlist, SpecialJoinInfo *sjinfo, - int nappinfos, AppendRelInfo **appinfos) + int nappinfos, AppendRelInfo **appinfos, + unsigned jsa_mask) { RelOptInfo *joinrel = makeNode(RelOptInfo); @@ -981,7 +984,7 @@ build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel, /* Is the join between partitions itself partitioned? */ build_joinrel_partition_info(root, joinrel, outer_rel, inner_rel, sjinfo, - restrictlist); + restrictlist, jsa_mask); /* Child joinrel is parallel safe if parent is parallel safe. */ joinrel->consider_parallel = parent_joinrel->consider_parallel; @@ -2005,12 +2008,12 @@ static void build_joinrel_partition_info(PlannerInfo *root, RelOptInfo *joinrel, RelOptInfo *outer_rel, RelOptInfo *inner_rel, SpecialJoinInfo *sjinfo, - List *restrictlist) + List *restrictlist, unsigned jsa_mask) { PartitionScheme part_scheme; /* Nothing to do if partitionwise join technique is disabled. */ - if (!enable_partitionwise_join) + if ((jsa_mask & JSA_PARTITIONWISE) == 0) { Assert(!IS_PARTITIONED_REL(joinrel)); return; diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index 07e2415398e..4470c7817da 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -3231,6 +3231,7 @@ typedef struct SemiAntiJoinFactors * sjinfo is extra info about special joins for selectivity estimation * semifactors is as shown above (only valid for SEMI/ANTI/inner_unique joins) * param_source_rels are OK targets for parameterization of result paths + * jsa_mask is a bitmask of JSA_* constants to direct the join strategy */ typedef struct JoinPathExtraData { @@ -3240,6 +3241,7 @@ typedef struct JoinPathExtraData SpecialJoinInfo *sjinfo; SemiAntiJoinFactors semifactors; Relids param_source_rels; + unsigned jsa_mask; } JoinPathExtraData; /* diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h index 854a782944a..071a8749cfa 100644 --- a/src/include/optimizer/cost.h +++ b/src/include/optimizer/cost.h @@ -125,7 +125,7 @@ extern void cost_merge_append(Path *path, PlannerInfo *root, Cost input_startup_cost, Cost input_total_cost, double tuples); extern void cost_material(Path *path, - int input_disabled_nodes, + bool enabled, int input_disabled_nodes, Cost input_startup_cost, Cost input_total_cost, double tuples, int width); extern void cost_agg(Path *path, PlannerInfo *root, @@ -148,7 +148,7 @@ extern void cost_group(Path *path, PlannerInfo *root, double input_tuples); extern void initial_cost_nestloop(PlannerInfo *root, JoinCostWorkspace *workspace, - JoinType jointype, + JoinType jointype, unsigned nestloop_subtype, Path *outer_path, Path *inner_path, JoinPathExtraData *extra); extern void final_cost_nestloop(PlannerInfo *root, NestPath *path, diff --git a/src/include/optimizer/geqo.h b/src/include/optimizer/geqo.h index c52906d0916..a9d14b07aa1 100644 --- a/src/include/optimizer/geqo.h +++ b/src/include/optimizer/geqo.h @@ -81,10 +81,13 @@ typedef struct /* routines in geqo_main.c */ extern RelOptInfo *geqo(PlannerInfo *root, - int number_of_rels, List *initial_rels); + int number_of_rels, List *initial_rels, + unsigned jsa_mask); /* routines in geqo_eval.c */ -extern Cost geqo_eval(PlannerInfo *root, Gene *tour, int num_gene); -extern RelOptInfo *gimme_tree(PlannerInfo *root, Gene *tour, int num_gene); +extern Cost geqo_eval(PlannerInfo *root, Gene *tour, int num_gene, + unsigned jsa_mask); +extern RelOptInfo *gimme_tree(PlannerInfo *root, Gene *tour, int num_gene, + unsigned jsa_mask); #endif /* GEQO_H */ diff --git a/src/include/optimizer/geqo_pool.h b/src/include/optimizer/geqo_pool.h index b5e80554724..bd1ed152907 100644 --- a/src/include/optimizer/geqo_pool.h +++ b/src/include/optimizer/geqo_pool.h @@ -29,7 +29,7 @@ extern Pool *alloc_pool(PlannerInfo *root, int pool_size, int string_length); extern void free_pool(PlannerInfo *root, Pool *pool); -extern void random_init_pool(PlannerInfo *root, Pool *pool); +extern void random_init_pool(PlannerInfo *root, Pool *pool, unsigned jsa_mask); extern Chromosome *alloc_chromo(PlannerInfo *root, int string_length); extern void free_chromo(PlannerInfo *root, Chromosome *chromo); diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h index 1035e6560c1..ed2e3a1c8b8 100644 --- a/src/include/optimizer/pathnode.h +++ b/src/include/optimizer/pathnode.h @@ -82,7 +82,8 @@ extern GroupResultPath *create_group_result_path(PlannerInfo *root, RelOptInfo *rel, PathTarget *target, List *havingqual); -extern MaterialPath *create_material_path(RelOptInfo *rel, Path *subpath); +extern MaterialPath *create_material_path(RelOptInfo *rel, Path *subpath, + bool enabled); extern MemoizePath *create_memoize_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath, @@ -324,7 +325,8 @@ extern RelOptInfo *build_join_rel(PlannerInfo *root, RelOptInfo *inner_rel, SpecialJoinInfo *sjinfo, List *pushed_down_joins, - List **restrictlist_ptr); + List **restrictlist_ptr, + unsigned jsa_mask); extern Relids min_join_parameterization(PlannerInfo *root, Relids joinrelids, RelOptInfo *outer_rel, @@ -351,6 +353,7 @@ extern RelOptInfo *build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel, RelOptInfo *inner_rel, RelOptInfo *parent_joinrel, List *restrictlist, SpecialJoinInfo *sjinfo, - int nappinfos, AppendRelInfo **appinfos); + int nappinfos, AppendRelInfo **appinfos, + unsigned jsa_mask); #endif /* PATHNODE_H */ diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h index a78e90610fc..9ce925dc292 100644 --- a/src/include/optimizer/paths.h +++ b/src/include/optimizer/paths.h @@ -16,10 +16,44 @@ #include "nodes/pathnodes.h" +/* + * Join strategy advice. + * + * Paths that don't match the join strategy advice will be either be disabled + * or will not be generated in the first place. It's only permissible to skip + * generating a path if doing so can't result in planner failure. The initial + * mask is computed on the basis of the various enable_* GUCs, and can be + * overriden by hooks. + * + * We have five main join strategies: a foreign join (when supported by the + * relevant FDW), a merge join, a nested loop, a hash join, and a partitionwise + * join. Merge joins are further subdivided based on whether the inner side + * is materialized, and nested loops are further subdivided based on whether + * the inner side is materialized, memoized, or neither. "Plain" means a + * strategy where neither materialization nor memoization is used. + * + * If you don't care whether materialization or memoization is used, set all + * the bits for the relevant major join strategy. If you do care, just set the + * subset of bits that correspond to the cases you want to allow. + */ +#define JSA_FOREIGN 0x0001 +#define JSA_MERGEJOIN_PLAIN 0x0002 +#define JSA_MERGEJOIN_MATERIALIZE 0x0004 +#define JSA_NESTLOOP_PLAIN 0x0008 +#define JSA_NESTLOOP_MATERIALIZE 0x0010 +#define JSA_NESTLOOP_MEMOIZE 0x0020 +#define JSA_HASHJOIN 0x0040 +#define JSA_PARTITIONWISE 0x0080 + +#define JSA_MERGEJOIN_ANY \ + (JSA_MERGEJOIN_PLAIN | JSA_MERGEJOIN_MATERIALIZE) +#define JSA_NESTLOOP_ANY \ + (JSA_NESTLOOP_PLAIN | JSA_NESTLOOP_MATERIALIZE | JSA_NESTLOOP_MEMOIZE) /* * allpaths.c */ + extern PGDLLIMPORT bool enable_geqo; extern PGDLLIMPORT int geqo_threshold; extern PGDLLIMPORT int min_parallel_table_scan_size; @@ -33,7 +67,14 @@ typedef void (*set_rel_pathlist_hook_type) (PlannerInfo *root, RangeTblEntry *rte); extern PGDLLIMPORT set_rel_pathlist_hook_type set_rel_pathlist_hook; -/* Hook for plugins to get control in add_paths_to_joinrel() */ +/* Hooks for plugins to get control in add_paths_to_joinrel() */ +typedef void (*join_path_setup_hook_type) (PlannerInfo *root, + RelOptInfo *joinrel, + RelOptInfo *outerrel, + RelOptInfo *innerrel, + JoinType jointype, + JoinPathExtraData *extra); +extern PGDLLIMPORT join_path_setup_hook_type join_path_setup_hook; typedef void (*set_join_pathlist_hook_type) (PlannerInfo *root, RelOptInfo *joinrel, RelOptInfo *outerrel, @@ -45,13 +86,14 @@ extern PGDLLIMPORT set_join_pathlist_hook_type set_join_pathlist_hook; /* Hook for plugins to replace standard_join_search() */ typedef RelOptInfo *(*join_search_hook_type) (PlannerInfo *root, int levels_needed, - List *initial_rels); + List *initial_rels, + unsigned jsa_mask); extern PGDLLIMPORT join_search_hook_type join_search_hook; extern RelOptInfo *make_one_rel(PlannerInfo *root, List *joinlist); extern RelOptInfo *standard_join_search(PlannerInfo *root, int levels_needed, - List *initial_rels); + List *initial_rels, unsigned jsa_mask); extern void generate_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_rows); @@ -92,15 +134,17 @@ extern bool create_tidscan_paths(PlannerInfo *root, RelOptInfo *rel); extern void add_paths_to_joinrel(PlannerInfo *root, RelOptInfo *joinrel, RelOptInfo *outerrel, RelOptInfo *innerrel, JoinType jointype, SpecialJoinInfo *sjinfo, - List *restrictlist); + List *restrictlist, unsigned jsa_mask); /* * joinrels.c * routines to determine which relations to join */ -extern void join_search_one_level(PlannerInfo *root, int level); +extern void join_search_one_level(PlannerInfo *root, int level, + unsigned jsa_mask); extern RelOptInfo *make_join_rel(PlannerInfo *root, - RelOptInfo *rel1, RelOptInfo *rel2); + RelOptInfo *rel1, RelOptInfo *rel2, + unsigned jsa_mask); extern Relids add_outer_joins_to_relids(PlannerInfo *root, Relids input_relids, SpecialJoinInfo *sjinfo, List **pushed_down_joins); -- 2.39.3 (Apple Git-145) ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: allowing extensions to control planner behavior @ 2024-09-30 09:50 Andrei Lepikhov <[email protected]> parent: Robert Haas <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Andrei Lepikhov @ 2024-09-30 09:50 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: pgsql-hackers; Tom Lane <[email protected]>; Michael Paquier <[email protected]> On 18/9/2024 17:48, Robert Haas wrote: > Comments? Let me share my personal experience on path management in the planner. The main thing important for extensions is flexibility - I would discuss a decision that is not limited by join ordering but could be applied to implement an index picking strategy, Memoize/Material choice versus a non-cached one, choice of custom paths, etc. The most flexible way I have found to this moment is a collaboration between the get_relation_info_hook and add_path hook. In get_relation_info, we have enough context and can add some information to RelOptInfo - I added an extra list to this structure where extensions can add helpful info. Using extensible nodes, we can tackle interference between extensions. The add_path hook can analyse new and old paths and also look into the extensible data inside RelOptInfo. The issue with lots of calls eases by quick return on the out-of-scope paths: usually extensions manage some specific path type or relation and quick test of RelOptInfo::extra_list allow to sort out unnecessary cases. Being flexible, this approach is less invasive. Now, I use it to implement heuristics demanded by clients for cases when the estimator predicts only one row - usually, it means that the optimiser underestimates cardinality. For example, in-place switch-off of NestLoop if it uses too many clauses, or rules to pick up index scan if we have alternative scans, each of them predicts only one tuple. Positive outcomes includes: we don't alter path costs; extension may be sure that core doesn't remove path from the list if the extension forbids it. In attachment - hooks for add_path and add_partial_path. As you can see, because of differences in these routines hooks also implemented differently. Also the compare_path_costs_fuzzily is exported, but it is really good stuff for an extension. -- regards, Andrei Lepikhov From af8f5bd65e33c819723231ce433dcd51438b7ef0 Mon Sep 17 00:00:00 2001 From: "Andrei V. Lepikhov" <[email protected]> Date: Thu, 26 Sep 2024 10:34:08 +0200 Subject: [PATCH 1/2] Introduce compare_path_hook. The add_path function is the only interface to safely add and remove paths in the pathlist. Postgres core provides a few optimisation hooks that an extension can use to offer additional paths. However, it is still uncertain whether such a path will be replaced until the end of the path population process. This hook allows the extension to control the comparison of a new path with each pathlist's path and denote the optimiser which path is better. It doesn't point to the optimiser directly about what exactly to do with the incoming path and paths, already existed in pathlist. Tag: optimizer. caused by: PGPRO-10445, PGPRO-11017. --- src/backend/optimizer/util/pathnode.c | 21 +++++++++++---------- src/include/optimizer/pathnode.h | 17 +++++++++++++++++ 2 files changed, 28 insertions(+), 10 deletions(-) diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index fc97bf6ee2..ae57932862 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -31,13 +31,6 @@ #include "utils/memutils.h" #include "utils/selfuncs.h" -typedef enum -{ - COSTS_EQUAL, /* path costs are fuzzily equal */ - COSTS_BETTER1, /* first path is cheaper than second */ - COSTS_BETTER2, /* second path is cheaper than first */ - COSTS_DIFFERENT, /* neither path dominates the other on cost */ -} PathCostComparison; /* * STD_FUZZ_FACTOR is the normal fuzz factor for compare_path_costs_fuzzily. @@ -46,6 +39,9 @@ typedef enum */ #define STD_FUZZ_FACTOR 1.01 +/* Hook for plugins to get control over the add_path decision */ +compare_path_hook_type compare_path_hook = NULL; + static List *translate_sub_tlist(List *tlist, int relid); static int append_total_cost_compare(const ListCell *a, const ListCell *b); static int append_startup_cost_compare(const ListCell *a, const ListCell *b); @@ -178,7 +174,7 @@ compare_fractional_path_costs(Path *path1, Path *path2, * (But if total costs are fuzzily equal, we compare startup costs anyway, * in hopes of eliminating one path or the other.) */ -static PathCostComparison +PathCostComparison compare_path_costs_fuzzily(Path *path1, Path *path2, double fuzz_factor) { #define CONSIDER_PATH_STARTUP_COST(p) \ @@ -490,8 +486,13 @@ add_path(RelOptInfo *parent_rel, Path *new_path) /* * Do a fuzzy cost comparison with standard fuzziness limit. */ - costcmp = compare_path_costs_fuzzily(new_path, old_path, - STD_FUZZ_FACTOR); + + if (compare_path_hook) + costcmp = (*compare_path_hook) (parent_rel, new_path, old_path, + STD_FUZZ_FACTOR); + else + costcmp = compare_path_costs_fuzzily(new_path, old_path, + STD_FUZZ_FACTOR); /* * If the two paths compare differently for startup and total cost, diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h index 1035e6560c..4b95d85e1a 100644 --- a/src/include/optimizer/pathnode.h +++ b/src/include/optimizer/pathnode.h @@ -18,11 +18,28 @@ #include "nodes/pathnodes.h" +typedef enum +{ + COSTS_EQUAL, /* path costs are fuzzily equal */ + COSTS_BETTER1, /* first path is cheaper than second */ + COSTS_BETTER2, /* second path is cheaper than first */ + COSTS_DIFFERENT, /* neither path dominates the other on cost */ +} PathCostComparison; + +/* Hook for plugins to get control when grouping_planner() plans upper rels */ +typedef PathCostComparison (*compare_path_hook_type) (RelOptInfo *parent_rel, + Path *new_path, + Path *old_path, + double fuzz_factor); +extern PGDLLIMPORT compare_path_hook_type compare_path_hook; + /* * prototypes for pathnode.c */ extern int compare_path_costs(Path *path1, Path *path2, CostSelector criterion); +extern PathCostComparison +compare_path_costs_fuzzily(Path *path1, Path *path2, double fuzz_factor); extern int compare_fractional_path_costs(Path *path1, Path *path2, double fraction); extern void set_cheapest(RelOptInfo *parent_rel); -- 2.46.2 From f63c93a404554f7e48a01ed14661a119ae10b604 Mon Sep 17 00:00:00 2001 From: "Andrei V. Lepikhov" <[email protected]> Date: Mon, 30 Sep 2024 13:32:43 +0700 Subject: [PATCH 2/2] Introduce compare_partial_path_hook. By analogy of compare_path_hook, let an extension to alter decisions on accepting new and removing old path. The add_partial_path() routine has different logic. Hence, the hook also differs from non-partial case. Also, move hooks and newly exported functions to paths.h. Do we need to revert it and allow to stay in the pathnode.h? --- src/backend/optimizer/util/pathnode.c | 5 +++++ src/include/optimizer/pathnode.h | 17 ----------------- src/include/optimizer/paths.h | 24 ++++++++++++++++++++++++ 3 files changed, 29 insertions(+), 17 deletions(-) diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index ae57932862..5f91ead226 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -41,6 +41,7 @@ /* Hook for plugins to get control over the add_path decision */ compare_path_hook_type compare_path_hook = NULL; +compare_partial_path_hook_type compare_partial_path_hook = NULL; static List *translate_sub_tlist(List *tlist, int relid); static int append_total_cost_compare(const ListCell *a, const ListCell *b); @@ -870,6 +871,10 @@ add_partial_path(RelOptInfo *parent_rel, Path *new_path) } } + if (compare_partial_path_hook) + (*compare_partial_path_hook)(parent_rel, new_path, old_path, + &accept_new, &remove_old); + /* * Remove current element from partial_pathlist if dominated by new. */ diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h index 4b95d85e1a..1035e6560c 100644 --- a/src/include/optimizer/pathnode.h +++ b/src/include/optimizer/pathnode.h @@ -18,28 +18,11 @@ #include "nodes/pathnodes.h" -typedef enum -{ - COSTS_EQUAL, /* path costs are fuzzily equal */ - COSTS_BETTER1, /* first path is cheaper than second */ - COSTS_BETTER2, /* second path is cheaper than first */ - COSTS_DIFFERENT, /* neither path dominates the other on cost */ -} PathCostComparison; - -/* Hook for plugins to get control when grouping_planner() plans upper rels */ -typedef PathCostComparison (*compare_path_hook_type) (RelOptInfo *parent_rel, - Path *new_path, - Path *old_path, - double fuzz_factor); -extern PGDLLIMPORT compare_path_hook_type compare_path_hook; - /* * prototypes for pathnode.c */ extern int compare_path_costs(Path *path1, Path *path2, CostSelector criterion); -extern PathCostComparison -compare_path_costs_fuzzily(Path *path1, Path *path2, double fuzz_factor); extern int compare_fractional_path_costs(Path *path1, Path *path2, double fraction); extern void set_cheapest(RelOptInfo *parent_rel); diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h index 54869d4401..47ae26033c 100644 --- a/src/include/optimizer/paths.h +++ b/src/include/optimizer/paths.h @@ -271,4 +271,28 @@ extern PathKey *make_canonical_pathkey(PlannerInfo *root, extern void add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel, List *live_childrels); + +typedef enum +{ + COSTS_EQUAL, /* path costs are fuzzily equal */ + COSTS_BETTER1, /* first path is cheaper than second */ + COSTS_BETTER2, /* second path is cheaper than first */ + COSTS_DIFFERENT, /* neither path dominates the other on cost */ +} PathCostComparison; + +extern PathCostComparison compare_path_costs_fuzzily(Path *path1, Path *path2, + double fuzz_factor); +/* Hook for plugins to get control when grouping_planner() plans upper rels */ +typedef PathCostComparison (*compare_path_hook_type) (RelOptInfo *rel, + Path *new_path, + Path *old_path, + double fuzz_factor); +typedef void (*compare_partial_path_hook_type) (RelOptInfo *rel, + Path *new_path, + Path *old_path, + bool *accept_new, + bool *remove_old); +extern PGDLLIMPORT compare_path_hook_type compare_path_hook; +extern PGDLLIMPORT compare_partial_path_hook_type compare_partial_path_hook; + #endif /* PATHS_H */ -- 2.46.2 Attachments: [text/plain] 0001-Introduce-compare_path_hook.patch (4.2K, ../../[email protected]/2-0001-Introduce-compare_path_hook.patch) download | inline diff: From af8f5bd65e33c819723231ce433dcd51438b7ef0 Mon Sep 17 00:00:00 2001 From: "Andrei V. Lepikhov" <[email protected]> Date: Thu, 26 Sep 2024 10:34:08 +0200 Subject: [PATCH 1/2] Introduce compare_path_hook. The add_path function is the only interface to safely add and remove paths in the pathlist. Postgres core provides a few optimisation hooks that an extension can use to offer additional paths. However, it is still uncertain whether such a path will be replaced until the end of the path population process. This hook allows the extension to control the comparison of a new path with each pathlist's path and denote the optimiser which path is better. It doesn't point to the optimiser directly about what exactly to do with the incoming path and paths, already existed in pathlist. Tag: optimizer. caused by: PGPRO-10445, PGPRO-11017. --- src/backend/optimizer/util/pathnode.c | 21 +++++++++++---------- src/include/optimizer/pathnode.h | 17 +++++++++++++++++ 2 files changed, 28 insertions(+), 10 deletions(-) diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index fc97bf6ee2..ae57932862 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -31,13 +31,6 @@ #include "utils/memutils.h" #include "utils/selfuncs.h" -typedef enum -{ - COSTS_EQUAL, /* path costs are fuzzily equal */ - COSTS_BETTER1, /* first path is cheaper than second */ - COSTS_BETTER2, /* second path is cheaper than first */ - COSTS_DIFFERENT, /* neither path dominates the other on cost */ -} PathCostComparison; /* * STD_FUZZ_FACTOR is the normal fuzz factor for compare_path_costs_fuzzily. @@ -46,6 +39,9 @@ typedef enum */ #define STD_FUZZ_FACTOR 1.01 +/* Hook for plugins to get control over the add_path decision */ +compare_path_hook_type compare_path_hook = NULL; + static List *translate_sub_tlist(List *tlist, int relid); static int append_total_cost_compare(const ListCell *a, const ListCell *b); static int append_startup_cost_compare(const ListCell *a, const ListCell *b); @@ -178,7 +174,7 @@ compare_fractional_path_costs(Path *path1, Path *path2, * (But if total costs are fuzzily equal, we compare startup costs anyway, * in hopes of eliminating one path or the other.) */ -static PathCostComparison +PathCostComparison compare_path_costs_fuzzily(Path *path1, Path *path2, double fuzz_factor) { #define CONSIDER_PATH_STARTUP_COST(p) \ @@ -490,8 +486,13 @@ add_path(RelOptInfo *parent_rel, Path *new_path) /* * Do a fuzzy cost comparison with standard fuzziness limit. */ - costcmp = compare_path_costs_fuzzily(new_path, old_path, - STD_FUZZ_FACTOR); + + if (compare_path_hook) + costcmp = (*compare_path_hook) (parent_rel, new_path, old_path, + STD_FUZZ_FACTOR); + else + costcmp = compare_path_costs_fuzzily(new_path, old_path, + STD_FUZZ_FACTOR); /* * If the two paths compare differently for startup and total cost, diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h index 1035e6560c..4b95d85e1a 100644 --- a/src/include/optimizer/pathnode.h +++ b/src/include/optimizer/pathnode.h @@ -18,11 +18,28 @@ #include "nodes/pathnodes.h" +typedef enum +{ + COSTS_EQUAL, /* path costs are fuzzily equal */ + COSTS_BETTER1, /* first path is cheaper than second */ + COSTS_BETTER2, /* second path is cheaper than first */ + COSTS_DIFFERENT, /* neither path dominates the other on cost */ +} PathCostComparison; + +/* Hook for plugins to get control when grouping_planner() plans upper rels */ +typedef PathCostComparison (*compare_path_hook_type) (RelOptInfo *parent_rel, + Path *new_path, + Path *old_path, + double fuzz_factor); +extern PGDLLIMPORT compare_path_hook_type compare_path_hook; + /* * prototypes for pathnode.c */ extern int compare_path_costs(Path *path1, Path *path2, CostSelector criterion); +extern PathCostComparison +compare_path_costs_fuzzily(Path *path1, Path *path2, double fuzz_factor); extern int compare_fractional_path_costs(Path *path1, Path *path2, double fraction); extern void set_cheapest(RelOptInfo *parent_rel); -- 2.46.2 [text/plain] 0002-Introduce-compare_partial_path_hook.patch (4.2K, ../../[email protected]/3-0002-Introduce-compare_partial_path_hook.patch) download | inline diff: From f63c93a404554f7e48a01ed14661a119ae10b604 Mon Sep 17 00:00:00 2001 From: "Andrei V. Lepikhov" <[email protected]> Date: Mon, 30 Sep 2024 13:32:43 +0700 Subject: [PATCH 2/2] Introduce compare_partial_path_hook. By analogy of compare_path_hook, let an extension to alter decisions on accepting new and removing old path. The add_partial_path() routine has different logic. Hence, the hook also differs from non-partial case. Also, move hooks and newly exported functions to paths.h. Do we need to revert it and allow to stay in the pathnode.h? --- src/backend/optimizer/util/pathnode.c | 5 +++++ src/include/optimizer/pathnode.h | 17 ----------------- src/include/optimizer/paths.h | 24 ++++++++++++++++++++++++ 3 files changed, 29 insertions(+), 17 deletions(-) diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index ae57932862..5f91ead226 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -41,6 +41,7 @@ /* Hook for plugins to get control over the add_path decision */ compare_path_hook_type compare_path_hook = NULL; +compare_partial_path_hook_type compare_partial_path_hook = NULL; static List *translate_sub_tlist(List *tlist, int relid); static int append_total_cost_compare(const ListCell *a, const ListCell *b); @@ -870,6 +871,10 @@ add_partial_path(RelOptInfo *parent_rel, Path *new_path) } } + if (compare_partial_path_hook) + (*compare_partial_path_hook)(parent_rel, new_path, old_path, + &accept_new, &remove_old); + /* * Remove current element from partial_pathlist if dominated by new. */ diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h index 4b95d85e1a..1035e6560c 100644 --- a/src/include/optimizer/pathnode.h +++ b/src/include/optimizer/pathnode.h @@ -18,28 +18,11 @@ #include "nodes/pathnodes.h" -typedef enum -{ - COSTS_EQUAL, /* path costs are fuzzily equal */ - COSTS_BETTER1, /* first path is cheaper than second */ - COSTS_BETTER2, /* second path is cheaper than first */ - COSTS_DIFFERENT, /* neither path dominates the other on cost */ -} PathCostComparison; - -/* Hook for plugins to get control when grouping_planner() plans upper rels */ -typedef PathCostComparison (*compare_path_hook_type) (RelOptInfo *parent_rel, - Path *new_path, - Path *old_path, - double fuzz_factor); -extern PGDLLIMPORT compare_path_hook_type compare_path_hook; - /* * prototypes for pathnode.c */ extern int compare_path_costs(Path *path1, Path *path2, CostSelector criterion); -extern PathCostComparison -compare_path_costs_fuzzily(Path *path1, Path *path2, double fuzz_factor); extern int compare_fractional_path_costs(Path *path1, Path *path2, double fraction); extern void set_cheapest(RelOptInfo *parent_rel); diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h index 54869d4401..47ae26033c 100644 --- a/src/include/optimizer/paths.h +++ b/src/include/optimizer/paths.h @@ -271,4 +271,28 @@ extern PathKey *make_canonical_pathkey(PlannerInfo *root, extern void add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel, List *live_childrels); + +typedef enum +{ + COSTS_EQUAL, /* path costs are fuzzily equal */ + COSTS_BETTER1, /* first path is cheaper than second */ + COSTS_BETTER2, /* second path is cheaper than first */ + COSTS_DIFFERENT, /* neither path dominates the other on cost */ +} PathCostComparison; + +extern PathCostComparison compare_path_costs_fuzzily(Path *path1, Path *path2, + double fuzz_factor); +/* Hook for plugins to get control when grouping_planner() plans upper rels */ +typedef PathCostComparison (*compare_path_hook_type) (RelOptInfo *rel, + Path *new_path, + Path *old_path, + double fuzz_factor); +typedef void (*compare_partial_path_hook_type) (RelOptInfo *rel, + Path *new_path, + Path *old_path, + bool *accept_new, + bool *remove_old); +extern PGDLLIMPORT compare_path_hook_type compare_path_hook; +extern PGDLLIMPORT compare_partial_path_hook_type compare_partial_path_hook; + #endif /* PATHS_H */ -- 2.46.2 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: allowing extensions to control planner behavior @ 2024-10-03 18:35 Robert Haas <[email protected]> parent: Andrei Lepikhov <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Robert Haas @ 2024-10-03 18:35 UTC (permalink / raw) To: Andrei Lepikhov <[email protected]>; +Cc: pgsql-hackers; Tom Lane <[email protected]>; Michael Paquier <[email protected]> On Mon, Sep 30, 2024 at 5:50 AM Andrei Lepikhov <[email protected]> wrote: > Being flexible, this approach is less invasive. Now, I use it to > implement heuristics demanded by clients for cases when the estimator > predicts only one row - usually, it means that the optimiser > underestimates cardinality. For example, in-place switch-off of NestLoop > if it uses too many clauses, or rules to pick up index scan if we have > alternative scans, each of them predicts only one tuple. > > Positive outcomes includes: we don't alter path costs; extension may be > sure that core doesn't remove path from the list if the extension > forbids it. > > In attachment - hooks for add_path and add_partial_path. As you can see, > because of differences in these routines hooks also implemented > differently. Also the compare_path_costs_fuzzily is exported, but it is > really good stuff for an extension. I agree that this is more flexible, but it also seems like it would be a lot more expensive. For every add_path() or add_partial_path() call, you'll have to examine the input path and decide what you want to do with it. If you want to do something like avoid nested loops with materialization, you'll need to first check the top-level node, and then if it's a nested loop, you have to check the inner subpath to see if it's a Materialize node. I'm not completely against having something like this; I think there are cases where something along these lines is the only way to achieve some desired objective. But I don't think this kind of hook should be the primary way for extensions to control the planner; it seems too low-level to me. -- Robert Haas EDB: http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-10-03 18:35 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-12-18 16:19 [PATCH v42 1/2] Subscripting for jsonb Dmitrii Dolgov <[email protected]> 2024-09-18 15:48 Re: allowing extensions to control planner behavior Robert Haas <[email protected]> 2024-09-30 09:50 ` Re: allowing extensions to control planner behavior Andrei Lepikhov <[email protected]> 2024-10-03 18:35 ` Re: allowing extensions to control planner behavior Robert Haas <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox