public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v33 3/5] Subscripting for jsonb 6+ messages / 3 participants [nested] [flat]
* [PATCH v33 3/5] Subscripting for jsonb @ 2019-02-01 10:41 erthalion <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: erthalion @ 2019-02-01 10:41 UTC (permalink / raw) Subscripting implementation for jsonb. 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. Reviewed-by: Tom Lane, Arthur Zakirov --- src/backend/utils/adt/jsonb.c | 27 ++- src/backend/utils/adt/jsonb_util.c | 76 ++++++- src/backend/utils/adt/jsonfuncs.c | 325 ++++++++++++++++++++-------- src/include/catalog/pg_proc.dat | 8 + src/include/catalog/pg_type.dat | 3 +- src/include/utils/jsonb.h | 2 + src/test/regress/expected/jsonb.out | 233 +++++++++++++++++++- src/test/regress/sql/jsonb.sql | 68 +++++- 8 files changed, 632 insertions(+), 110 deletions(-) diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c index 1e9ca046c6..f66642def3 100644 --- a/src/backend/utils/adt/jsonb.c +++ b/src/backend/utils/adt/jsonb.c @@ -1134,23 +1134,34 @@ to_jsonb(PG_FUNCTION_ARGS) { Datum val = PG_GETARG_DATUM(0); Oid val_type = get_fn_expr_argtype(fcinfo->flinfo, 0); - JsonbInState result; - JsonbTypeCategory tcategory; - Oid outfuncoid; + JsonbValue *res = to_jsonb_worker(val, val_type, false); + PG_RETURN_POINTER(JsonbValueToJsonb(res)); +} - if (val_type == InvalidOid) +/* + * Do the actual conversion to jsonb for to_jsonb function. This logic is + * separated because it can be useful not only in here (e.g. we use it in + * jsonb subscripting) + */ +JsonbValue * +to_jsonb_worker(Datum source, Oid source_type, bool is_null) +{ + JsonbInState result; + JsonbTypeCategory tcategory; + Oid outfuncoid; + + if (source_type == InvalidOid) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("could not determine input data type"))); - jsonb_categorize_type(val_type, + jsonb_categorize_type(source_type, &tcategory, &outfuncoid); memset(&result, 0, sizeof(JsonbInState)); - datum_to_jsonb(val, false, &result, tcategory, outfuncoid, false); - - PG_RETURN_POINTER(JsonbValueToJsonb(result.res)); + datum_to_jsonb(source, is_null, &result, tcategory, outfuncoid, false); + return result.res; } /* 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/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c index 5a09d65fdc..f46a2828b3 100644 --- a/src/backend/utils/adt/jsonfuncs.c +++ b/src/backend/utils/adt/jsonfuncs.c @@ -21,12 +21,16 @@ #include "common/jsonapi.h" #include "fmgr.h" #include "funcapi.h" +#include "executor/execExpr.h" #include "lib/stringinfo.h" #include "mb/pg_wchar.h" #include "miscadmin.h" +#include "nodes/nodeFuncs.h" +#include "parser/parse_coerce.h" #include "utils/array.h" #include "utils/builtins.h" #include "utils/hsearch.h" +#include "utils/fmgroids.h" #include "utils/json.h" #include "utils/jsonb.h" #include "utils/jsonfuncs.h" @@ -460,18 +464,22 @@ 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 Datum jsonb_set_element(Datum datum, Datum *path, int path_len, + Datum sourceData, Oid source_type, bool is_null); +static Datum jsonb_get_element(Jsonb *jb, Datum *path, int npath, + bool *isnull, bool as_text); static 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); @@ -486,6 +494,15 @@ static void transform_string_values_object_field_start(void *state, char *fname, static void transform_string_values_array_element_start(void *state, bool isnull); static void transform_string_values_scalar(void *state, char *token, JsonTokenType tokentype); +static SubscriptingRef *jsonb_subscript_prepare(bool isAssignment, + SubscriptingRef *sbsref); + +static SubscriptingRef *jsonb_subscript_validate(bool isAssignment, + SubscriptingRef *sbsref, + ParseState *pstate); +static Datum jsonb_subscript_fetch(Datum containerSource, SubscriptingRefState *sbstate); +static Datum jsonb_subscript_assign(Datum containerSource, SubscriptingRefState *sbstate); + /* * pg_parse_json_or_ereport * @@ -1447,13 +1464,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 @@ -1468,9 +1481,28 @@ 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); +} + +static Datum +jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text) +{ + Jsonb *res; + JsonbContainer *container = &jb->root; + JsonbValue *jbvp = NULL; + JsonbValue tv; + 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)) @@ -1495,7 +1527,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)))); } @@ -1511,22 +1543,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) { @@ -1544,7 +1579,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; } @@ -1554,11 +1592,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; @@ -1580,9 +1622,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 { @@ -1593,6 +1638,32 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) } } +Datum +jsonb_set_element(Datum jsonbdatum, Datum *path, int path_len, + Datum sourceData, Oid source_type, bool is_null) +{ + Jsonb *jb = DatumGetJsonbP(jsonbdatum); + JsonbValue *newval, + *res; + JsonbParseState *state = NULL; + JsonbIterator *it; + bool *path_nulls = palloc0(path_len * sizeof(bool)); + + newval = to_jsonb_worker(sourceData, source_type, is_null); + + 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. */ @@ -4152,58 +4223,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) * @@ -4475,7 +4494,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; @@ -4633,7 +4653,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; @@ -4796,7 +4817,7 @@ IteratorConcat(JsonbIterator **it1, JsonbIterator **it2, static 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; @@ -4849,11 +4870,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]) @@ -4870,7 +4891,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++) @@ -4901,7 +4922,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; } @@ -4924,7 +4945,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); @@ -4956,7 +4977,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, @@ -5004,7 +5025,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; } @@ -5020,7 +5041,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 @@ -5031,7 +5052,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; } @@ -5065,12 +5086,138 @@ 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); } } } } +/* + * Perform an actual data extraction or modification for the jsonb + * subscripting. As a result the extracted Datum or the modified containers + * value will be returned. + */ +Datum +jsonb_subscript_fetch(Datum containerSource, SubscriptingRefState *sbstate) +{ + return jsonb_get_element(DatumGetJsonbP(containerSource), + sbstate->upperindex, + sbstate->numupper, + &sbstate->resnull, + false); +} + + + +/* + * Perform an actual data extraction or modification for the jsonb + * subscripting. As a result the extracted Datum or the modified containers + * value will be returned. + */ +Datum +jsonb_subscript_assign(Datum containerSource, SubscriptingRefState *sbstate) +{ + /* + * the original jsonb must be non-NULL, else we punt and return the + * original array. + */ + if (sbstate->resnull) + return containerSource; + + return jsonb_set_element(containerSource, + sbstate->upperindex, + sbstate->numupper, + sbstate->replacevalue, + sbstate->refelemtype, + sbstate->replacenull); +} + +/* + * Perform preparation for the jsonb subscripting. Since there are not any + * particular restrictions for this kind of subscripting, we will verify that + * it is not a slice operation. This function produces an expression that + * represents the result of extracting a single container element or the new + * container value with the source data inserted into the right part of the + * container. If you have read until this point, and will submit a meaningful + * review of this patch series, I'll owe you a beer at the next PGConfEU. + */ + +/* + * Handle jsonb-type subscripting logic. + */ +Datum +jsonb_subscript_handler(PG_FUNCTION_ARGS) +{ + SubscriptRoutines *sbsroutines = (SubscriptRoutines *) + palloc(sizeof(SubscriptRoutines)); + + sbsroutines->prepare = jsonb_subscript_prepare; + sbsroutines->validate = jsonb_subscript_validate; + sbsroutines->fetch = jsonb_subscript_fetch; + sbsroutines->assign = jsonb_subscript_assign; + + PG_RETURN_POINTER(sbsroutines); +} + +SubscriptingRef * +jsonb_subscript_prepare(bool isAssignment, SubscriptingRef *sbsref) +{ + if (isAssignment) + { + sbsref->refelemtype = exprType((Node *) sbsref->refassgnexpr); + sbsref->refassgntype = exprType((Node *) sbsref->refassgnexpr); + } + else + sbsref->refelemtype = JSONBOID; + + return sbsref; +} + +SubscriptingRef * +jsonb_subscript_validate(bool isAssignment, SubscriptingRef *sbsref, + ParseState *pstate) +{ + List *upperIndexpr = NIL; + ListCell *l; + + if (sbsref->reflowerindexpr != NIL) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("jsonb subscript does not support slices"), + parser_errposition(pstate, exprLocation( + ((Node *) linitial(sbsref->reflowerindexpr)))))); + + foreach(l, sbsref->refupperindexpr) + { + Node *subexpr = (Node *) lfirst(l); + + if (subexpr == NULL) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("jsonb subscript does not support slices"), + parser_errposition(pstate, exprLocation( + ((Node *) linitial(sbsref->refupperindexpr)))))); + + subexpr = coerce_to_target_type(pstate, + subexpr, exprType(subexpr), + TEXTOID, -1, + COERCION_ASSIGNMENT, + 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)))); + + upperIndexpr = lappend(upperIndexpr, subexpr); + } + + sbsref->refupperindexpr = upperIndexpr; + + return sbsref; +} + /* * Parse information about what elements of a jsonb document we want to iterate * in functions iterate_json(b)_values. This information is presented in jsonb diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 8761597e12..bf0d4ef743 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10879,6 +10879,14 @@ proargnames => '{max_data_alignment,database_block_size,blocks_per_segment,wal_block_size,bytes_per_wal_segment,max_identifier_length,max_index_columns,max_toast_chunk_size,large_object_chunk_size,float8_pass_by_value,data_page_checksum_version}', prosrc => 'pg_control_init' }, +# type subscripting support +{ oid => '6098', + descr => 'Jsonb subscripting logic', + proname => 'jsonb_subscript_handler', + prorettype => 'internal', + proargtypes => 'internal', + prosrc => 'jsonb_subscript_handler' }, + { oid => '6099', descr => 'Array subscripting logic', proname => 'array_subscript_handler', diff --git a/src/include/catalog/pg_type.dat b/src/include/catalog/pg_type.dat index 21489a02ae..fa7e1d22a5 100644 --- a/src/include/catalog/pg_type.dat +++ b/src/include/catalog/pg_type.dat @@ -456,7 +456,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', + typsubshandler => '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..6e3b75d56a 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,6 @@ extern char *JsonbToCStringIndent(StringInfo out, JsonbContainer *in, extern bool JsonbExtractScalar(JsonbContainer *jbc, JsonbValue *res); extern const char *JsonbTypeName(JsonbValue *jb); +extern JsonbValue *to_jsonb_worker(Datum source, Oid source_type, bool is_null); #endif /* __JSONB_H__ */ diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out index a70cd0b7c1..04a146a7d0 100644 --- a/src/test/regress/expected/jsonb.out +++ b/src/test/regress/expected/jsonb.out @@ -4567,7 +4567,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 @@ -4697,6 +4697,237 @@ 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]; + jsonb +------- + +(1 row) + +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) + +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: 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) + -- 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 3e2b8f66df..12541e7e50 100644 --- a/src/test/regress/sql/jsonb.sql +++ b/src/test/regress/sql/jsonb.sql @@ -1172,7 +1172,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"'); @@ -1203,6 +1203,72 @@ 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]; + +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; + -- jsonb to tsvector select to_tsvector('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::jsonb); -- 2.21.0 --u66dlpg6gaevsofr Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v33-0004-Subscripting-documentation.patch" ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: allow changing autovacuum_max_workers without restarting @ 2024-04-11 14:24 Imseih (AWS), Sami <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Imseih (AWS), Sami @ 2024-04-11 14:24 UTC (permalink / raw) To: Nathan Bossart <[email protected]>; pgsql-hackers > I frequently hear about scenarios where users with thousands upon thousands > of tables realize that autovacuum is struggling to keep up. When they > inevitably go to bump up autovacuum_max_workers, they discover that it > requires a server restart (i.e., downtime) to take effect, causing further > frustration. For this reason, I think $SUBJECT is a desirable improvement. > I spent some time looking for past discussions about this, and I was > surprised to not find any, so I thought I'd give it a try. I did not review the patch in detail yet, but +1 to the idea. It's not just thousands of tables that suffer from this. If a user has a few large tables hogging the autovac workers, then other tables don't get the autovac cycles they require. Users are then forced to run manual vacuums, which adds complexity to their operations. > The attached proof-of-concept patch demonstrates what I have in mind. > Instead of trying to dynamically change the global process table, etc., I'm > proposing that we introduce a new GUC that sets the effective maximum > number of autovacuum workers that can be started at any time. max_worker_processes defines a pool of max # of background workers allowed. parallel workers and extensions that spin up background workers all utilize from this pool. Should autovacuum_max_workers be able to utilize from max_worker_processes also? This will allow autovacuum_max_workers to be dynamic while the user only has to deal with an already existing GUC. We may want to increase the default value for max_worker_processes as part of this. Regards, Sami Amazon Web Services (AWS) ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: allow changing autovacuum_max_workers without restarting @ 2024-04-11 14:42 Nathan Bossart <[email protected]> parent: Imseih (AWS), Sami <[email protected]> 0 siblings, 2 replies; 6+ messages in thread From: Nathan Bossart @ 2024-04-11 14:42 UTC (permalink / raw) To: Imseih (AWS), Sami <[email protected]>; +Cc: pgsql-hackers On Thu, Apr 11, 2024 at 02:24:18PM +0000, Imseih (AWS), Sami wrote: > max_worker_processes defines a pool of max # of background workers allowed. > parallel workers and extensions that spin up background workers all utilize from > this pool. > > Should autovacuum_max_workers be able to utilize from max_worker_processes also? > > This will allow autovacuum_max_workers to be dynamic while the user only has > to deal with an already existing GUC. We may want to increase the default value > for max_worker_processes as part of this. My concern with this approach is that other background workers could use up all the slots and prevent autovacuum workers from starting, unless of course we reserve autovacuum_max_workers slots for _only_ autovacuum workers. I'm not sure if we want to get these parameters tangled up like this, though... -- Nathan Bossart Amazon Web Services: https://aws.amazon.com ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: allow changing autovacuum_max_workers without restarting @ 2024-04-11 14:58 Nathan Bossart <[email protected]> parent: Nathan Bossart <[email protected]> 1 sibling, 0 replies; 6+ messages in thread From: Nathan Bossart @ 2024-04-11 14:58 UTC (permalink / raw) To: Imseih (AWS), Sami <[email protected]>; +Cc: pgsql-hackers On Thu, Apr 11, 2024 at 09:42:40AM -0500, Nathan Bossart wrote: > On Thu, Apr 11, 2024 at 02:24:18PM +0000, Imseih (AWS), Sami wrote: >> max_worker_processes defines a pool of max # of background workers allowed. >> parallel workers and extensions that spin up background workers all utilize from >> this pool. >> >> Should autovacuum_max_workers be able to utilize from max_worker_processes also? >> >> This will allow autovacuum_max_workers to be dynamic while the user only has >> to deal with an already existing GUC. We may want to increase the default value >> for max_worker_processes as part of this. > > My concern with this approach is that other background workers could use up > all the slots and prevent autovacuum workers from starting, unless of > course we reserve autovacuum_max_workers slots for _only_ autovacuum > workers. I'm not sure if we want to get these parameters tangled up like > this, though... I see that the logical replication launcher process uses this pool, but we take special care to make sure it gets a slot: /* * Register the apply launcher. It's probably a good idea to call this * before any modules had a chance to take the background worker slots. */ ApplyLauncherRegister(); I'm not sure there's another way to effectively reserve slots that would work for the autovacuum workers (which need to restart to connect to different databases), so that would need to be invented. We'd probably also want to fail startup if autovacuum_max_workers < max_worker_processes, which seems like it has the potential to cause problems when folks first upgrade to v18. Furthermore, we might have to convert autovacuum workers to background worker processes for this to work. I've admittedly wondered about whether we should do that eventually, anyway, but it'd expand the scope of this work quite a bit. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: allow changing autovacuum_max_workers without restarting @ 2024-04-11 15:37 Imseih (AWS), Sami <[email protected]> parent: Nathan Bossart <[email protected]> 1 sibling, 1 reply; 6+ messages in thread From: Imseih (AWS), Sami @ 2024-04-11 15:37 UTC (permalink / raw) To: Nathan Bossart <[email protected]>; +Cc: pgsql-hackers > My concern with this approach is that other background workers could use up > all the slots and prevent autovacuum workers from starting That's a good point, the current settings do not guarantee that you get a worker for the purpose if none are available, i.e. max_parallel_workers_per_gather, you may have 2 workers planned and 0 launched. > unless of > course we reserve autovacuum_max_workers slots for _only_ autovacuum > workers. I'm not sure if we want to get these parameters tangled up like > this, though... This will be confusing to describe and we will be reserving autovac workers implicitly, rather than explicitly with a new GUC. Regards, Sami ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: allow changing autovacuum_max_workers without restarting @ 2024-04-11 19:24 Nathan Bossart <[email protected]> parent: Imseih (AWS), Sami <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Nathan Bossart @ 2024-04-11 19:24 UTC (permalink / raw) To: Imseih (AWS), Sami <[email protected]>; +Cc: pgsql-hackers On Thu, Apr 11, 2024 at 03:37:23PM +0000, Imseih (AWS), Sami wrote: >> My concern with this approach is that other background workers could use up >> all the slots and prevent autovacuum workers from starting > > That's a good point, the current settings do not guarantee that you > get a worker for the purpose if none are available, > i.e. max_parallel_workers_per_gather, you may have 2 workers planned > and 0 launched. > >> unless of >> course we reserve autovacuum_max_workers slots for _only_ autovacuum >> workers. I'm not sure if we want to get these parameters tangled up like >> this, though... > > This will be confusing to describe and we will be reserving autovac workers > implicitly, rather than explicitly with a new GUC. Yeah, that's probably a good reason to give autovacuum its own worker pool. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-04-11 19:24 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-02-01 10:41 [PATCH v33 3/5] Subscripting for jsonb erthalion <[email protected]> 2024-04-11 14:24 Re: allow changing autovacuum_max_workers without restarting Imseih (AWS), Sami <[email protected]> 2024-04-11 14:42 ` Re: allow changing autovacuum_max_workers without restarting Nathan Bossart <[email protected]> 2024-04-11 14:58 ` Re: allow changing autovacuum_max_workers without restarting Nathan Bossart <[email protected]> 2024-04-11 15:37 ` Re: allow changing autovacuum_max_workers without restarting Imseih (AWS), Sami <[email protected]> 2024-04-11 19:24 ` Re: allow changing autovacuum_max_workers without restarting Nathan Bossart <[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