public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v2] Avoid spurious CREATE INDEX CONCURRENTLY waits 2+ messages / 2 participants [nested] [flat]
* [PATCH v2] Avoid spurious CREATE INDEX CONCURRENTLY waits @ 2020-08-05 02:04 Alvaro Herrera <[email protected]> 0 siblings, 0 replies; 2+ messages in thread From: Alvaro Herrera @ 2020-08-05 02:04 UTC (permalink / raw) --- src/backend/commands/indexcmds.c | 50 ++++++++++++++++++++++++++++++-- src/include/storage/proc.h | 6 +++- 2 files changed, 52 insertions(+), 4 deletions(-) diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 254dbcdce5..459f6fa5db 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -372,7 +372,10 @@ CompareOpclassOptions(Datum *opts1, Datum *opts2, int natts) * lazy VACUUMs, because they won't be fazed by missing index entries * either. (Manual ANALYZEs, however, can't be excluded because they * might be within transactions that are going to do arbitrary operations - * later.) + * later.) Processes running CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY + * on indexes that are neither expressional nor partial are also safe to + * ignore, since we know that those processes won't examine any data + * outside the table they're indexing. * * Also, GetCurrentVirtualXIDs never reports our own vxid, so we need not * check for that. @@ -393,7 +396,8 @@ WaitForOlderSnapshots(TransactionId limitXmin, bool progress) VirtualTransactionId *old_snapshots; old_snapshots = GetCurrentVirtualXIDs(limitXmin, true, false, - PROC_IS_AUTOVACUUM | PROC_IN_VACUUM, + PROC_IS_AUTOVACUUM | PROC_IN_VACUUM + | PROC_IN_SAFE_CIC, &n_old_snapshots); if (progress) pgstat_progress_update_param(PROGRESS_WAITFOR_TOTAL, n_old_snapshots); @@ -413,7 +417,8 @@ WaitForOlderSnapshots(TransactionId limitXmin, bool progress) newer_snapshots = GetCurrentVirtualXIDs(limitXmin, true, false, - PROC_IS_AUTOVACUUM | PROC_IN_VACUUM, + PROC_IS_AUTOVACUUM | PROC_IN_VACUUM + | PROC_IN_SAFE_CIC, &n_newer_snapshots); for (j = i; j < n_old_snapshots; j++) { @@ -506,6 +511,7 @@ DefineIndex(Oid relationId, bool amcanorder; amoptions_function amoptions; bool partitioned; + bool safe_index; Datum reloptions; int16 *coloptions; IndexInfo *indexInfo; @@ -1033,6 +1039,17 @@ DefineIndex(Oid relationId, } } + /* + * When doing concurrent index builds, we can set a PGPROC flag to tell + * concurrent VACUUM, CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY + * to ignore us when waiting for concurrent snapshots. That can only be + * done for indexes that don't execute any expressions. Determine that. + * (The flag is reset automatically at transaction end, so it must be + * set for each transaction.) + */ + safe_index = indexInfo->ii_Expressions == NIL && + indexInfo->ii_Predicate == NIL; + /* * Report index creation if appropriate (delay this till after most of the * error checks) @@ -1419,6 +1436,15 @@ DefineIndex(Oid relationId, CommitTransactionCommand(); StartTransactionCommand(); + /* Tell concurrent index builds to ignore us, if index qualifies */ + if (safe_index) + { + LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE); + MyProc->vacuumFlags |= PROC_IN_SAFE_CIC; + ProcGlobal->vacuumFlags[MyProc->pgxactoff] = MyProc->vacuumFlags; + LWLockRelease(ProcArrayLock); + } + /* * The index is now visible, so we can report the OID. */ @@ -1478,6 +1504,15 @@ DefineIndex(Oid relationId, CommitTransactionCommand(); StartTransactionCommand(); + /* Tell concurrent index builds to ignore us, if index qualifies */ + if (safe_index) + { + LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE); + MyProc->vacuumFlags |= PROC_IN_SAFE_CIC; + ProcGlobal->vacuumFlags[MyProc->pgxactoff] = MyProc->vacuumFlags; + LWLockRelease(ProcArrayLock); + } + /* * Phase 3 of concurrent index build * @@ -1534,6 +1569,15 @@ DefineIndex(Oid relationId, CommitTransactionCommand(); StartTransactionCommand(); + /* Tell concurrent index builds to ignore us, if index qualifies */ + if (safe_index) + { + LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE); + MyProc->vacuumFlags |= PROC_IN_SAFE_CIC; + ProcGlobal->vacuumFlags[MyProc->pgxactoff] = MyProc->vacuumFlags; + LWLockRelease(ProcArrayLock); + } + /* We should now definitely not be advertising any xmin. */ Assert(MyProc->xmin == InvalidTransactionId); diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h index 9c9a50ae45..d91e199a60 100644 --- a/src/include/storage/proc.h +++ b/src/include/storage/proc.h @@ -53,13 +53,17 @@ struct XidCache */ #define PROC_IS_AUTOVACUUM 0x01 /* is it an autovac worker? */ #define PROC_IN_VACUUM 0x02 /* currently running lazy vacuum */ +#define PROC_IN_SAFE_CIC 0x04 /* currently running CREATE INDEX + * CONCURRENTLY or REINDEX + * CONCURRENTLY on non-expressional, + * non-partial index */ #define PROC_VACUUM_FOR_WRAPAROUND 0x08 /* set by autovac only */ #define PROC_IN_LOGICAL_DECODING 0x10 /* currently doing logical * decoding outside xact */ /* flags reset at EOXact */ #define PROC_VACUUM_STATE_MASK \ - (PROC_IN_VACUUM | PROC_VACUUM_FOR_WRAPAROUND) + (PROC_IN_VACUUM | PROC_IN_SAFE_CIC | PROC_VACUUM_FOR_WRAPAROUND) /* * We allow a small number of "weak" relation locks (AccessShareLock, -- 2.20.1 --nFreZHaLTZJo0R7j-- ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: SQL:2023 JSON simplified accessor support @ 2024-09-23 19:22 Alexandra Wang <[email protected]> 0 siblings, 0 replies; 2+ messages in thread From: Alexandra Wang @ 2024-09-23 19:22 UTC (permalink / raw) To: Peter Eisentraut <[email protected]>; +Cc: pgsql-hackers; Andrew Dunstan <[email protected]> Hi Peter, Thank you so much for helping! On Mon, Sep 16, 2024 at 12:44 PM Peter Eisentraut <[email protected]> wrote: > > On 29.08.24 18:33, Alexandra Wang wrote: > > I’ve implemented the member and array accessors and attached two > > alternative patches: > > > > 1. v1-0001-Add-JSON-JSONB-simplified-accessor.patch: This patch > > enables dot access to JSON object fields and subscript access to > > indexed JSON array elements by converting "." and "[]" indirection > > into a JSON_QUERY JsonFuncExpr node. > > > > 2. v2-0001-Transform-JSON-dot-access-to-arrow-operator.txt: This > > alternative patch implements dot access to JSON object fields by > > transforming the "." indirection into a "->" operator. > > > > The upside of the v1 patch is that it strictly aligns with the SQL > > standard, which specifies that the simplified access is equivalent to: > > > > JSON_QUERY (VEP, 'lax $.JC' WITH CONDITIONAL ARRAY WRAPPER NULL ON > > EMPTY NULL ON ERROR) > > > > However, the performance of JSON_QUERY might be suboptimal due to > > function call overhead. Therefore, I implemented the v2 alternative > > using the "->" operator. > Using the operator approach would also allow taking advantage of > optimizations such as > <https://www.postgresql.org/message-id/flat/CAKU4AWoqAVya6PBhn%2BBCbFaBMt3z-2%3Di5fKO3bW%3D6HPhbid2Dw...;. OK, that makes sense. > > There is some uncertainty about the semantics of conditional array > > wrappers. Currently, there is at least one subtle difference between > > the "->" operator and JSON_QUERY, as shown: > > That JSON_QUERY bug has been fixed. > > I suggest you rebase both of your patches over this, just to double > check everything. But then I think you can drop the v1 patch and just > submit a new version of v2. Done. I rebased both patches and confirmed they have the same test outputs. I attached v3, which also adds JSON subscript support on top of v2. > The patch should eventually contain some documentation. It might be > good starting to look for a good spot where to put that documentation. > It might be either near the json types documentation or near the general > qualified identifier syntax, not sure. Right, I’m not sure either. A third option, I think, would be to include it in the JSON Functions and Operators section [1]. [1] https://www.postgresql.org/docs/devel/functions-json.html Best, Alex Attachments: [application/octet-stream] v3-0001-Add-JSON-JSONB-simplified-accessor.patch (21.1K, ../../CAK98qZ2grHAug2qVAH808drYLbSkdVM4KaM9wHxngdFgdFy1zg@mail.gmail.com/2-v3-0001-Add-JSON-JSONB-simplified-accessor.patch) download | inline diff: From 64a8659d819fef5abc2e5b2c562d732ae6cfdd20 Mon Sep 17 00:00:00 2001 From: Alexandra Wang <[email protected]> Date: Thu, 15 Aug 2024 02:11:33 -0700 Subject: [PATCH v3] Add JSON/JSONB simplified accessor This patch implements JSON/JSONB member accessor and JSON/JSONB array accessor as specified in SQL 2023. Specifically, the following sytaxes are added: 1. Simple dot-notation access to JSON and JSONB object fields 2. Subscripting for indexed access to JSON array elements Examples: -- Setup create table t(x int, y json); insert into t select 1, '{"a": 1, "b": 42}'::json; insert into t select 1, '{"a": 2, "b": {"c": 42}}'::json; insert into t select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::json; -- Existing syntax predates the SQL standard: select (t.y)->'b' from t; select (t.y)->'b'->'c' from t; select (t.y)->'d'->0 from t; -- JSON simplified accessor specified by the SQL standard: select (t.y).b from t; select (t.y).b.c from t; select (t.y).d[0] from t; The SQL standard states that simplified access is equivalent to: JSON_QUERY (VEP, 'lax $.JC' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) where VEP is the <value expression primary> and JC is the <JSON simplified accessor op chain>. For example, the JSON_QUERY equalalence of the above queries is: select json_query(y, 'lax $.b' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from t; select json_query(y, 'lax $.b.c' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from t; select json_query(y, 'lax $.d[0]' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from t; This implementation enables dot-notation access to JSON/JSONB object by making a syntatic sugar for the json_object_field "->" operator in ParseFuncOrColumn() for arg of JSON/JSONB type. Similarly, JSON array access via subscripting is enabled by creating an OpExpr for the existing "->" operator. Note that the JSON subscripting implementation is different from the JSONB subscripting counterpart, as the former leverages the "->" operator directly, while the latter uses the more generic SubscriptingRef interface. --- src/backend/parser/parse_expr.c | 8 ++- src/backend/parser/parse_func.c | 93 ++++++++++++++++++++++-- src/include/catalog/pg_operator.dat | 6 +- src/include/parser/parse_func.h | 3 + src/include/parser/parse_type.h | 1 + src/test/regress/expected/json.out | 107 ++++++++++++++++++++++++++++ src/test/regress/expected/jsonb.out | 86 ++++++++++++++++++++++ src/test/regress/sql/json.sql | 25 +++++++ src/test/regress/sql/jsonb.sql | 22 ++++++ 9 files changed, 342 insertions(+), 9 deletions(-) diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 36c1b7a88f..c55582cde5 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -453,7 +453,13 @@ transformIndirection(ParseState *pstate, A_Indirection *ind) Node *n = lfirst(i); if (IsA(n, A_Indices)) - subscripts = lappend(subscripts, n); + if (exprType(result) == JSONOID) + result = ParseJsonSimplifiedAccessorArrayElement(pstate, + castNode(A_Indices, n), + result, + location); + else + subscripts = lappend(subscripts, n); else if (IsA(n, A_Star)) { ereport(ERROR, diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index 9b23344a3b..4c054343f2 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -33,6 +33,8 @@ #include "utils/builtins.h" #include "utils/lsyscache.h" #include "utils/syscache.h" +#include "parser/parse_oper.h" +#include "catalog/pg_operator_d.h" /* Possible error codes from LookupFuncNameInternal */ @@ -48,6 +50,8 @@ static void unify_hypothetical_args(ParseState *pstate, static Oid FuncNameAsType(List *funcname); static Node *ParseComplexProjection(ParseState *pstate, const char *funcname, Node *first_arg, int location); +static Node *ParseJsonSimplifiedAccessorObjectField(ParseState *pstate, const char *funcname, + Node *first_arg, int location); static Oid LookupFuncNameInternal(ObjectType objtype, List *funcname, int nargs, const Oid *argtypes, bool include_out_arguments, bool missing_ok, @@ -226,17 +230,24 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, !func_variadic && argnames == NIL && list_length(funcname) == 1 && (actual_arg_types[0] == RECORDOID || - ISCOMPLEX(actual_arg_types[0]))); + ISCOMPLEX(actual_arg_types[0]) || + ISJSON(actual_arg_types[0]))); /* * If it's column syntax, check for column projection case first. */ if (could_be_projection && is_column) { - retval = ParseComplexProjection(pstate, - strVal(linitial(funcname)), - first_arg, - location); + if (ISJSON(actual_arg_types[0])) + retval = ParseJsonSimplifiedAccessorObjectField(pstate, + strVal(linitial(funcname)), + first_arg, + location); + else + retval = ParseComplexProjection(pstate, + strVal(linitial(funcname)), + first_arg, + location); if (retval) return retval; @@ -1902,6 +1913,78 @@ FuncNameAsType(List *funcname) return result; } +/* + * ParseJsonSimplifiedAccessorArrayElement - + * transform json subscript into json_array_element operator. + */ +Node * +ParseJsonSimplifiedAccessorArrayElement(ParseState *pstate, A_Indices *subscript, + Node *first_arg, int location) +{ + OpExpr *result; + Node *index; + + Assert(exprType(first_arg) == JSONOID); + + if (subscript->is_slice) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("json subscript does not support slices")), + parser_errposition(pstate, location)); + + index = transformExpr(pstate, subscript->uidx, pstate->p_expr_kind); + if (!IsA(index, Const) || + castNode(Const, index)->consttype != INT4OID) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("json subscript must be coercible to integer")), + parser_errposition(pstate, exprLocation(index))); + + result = makeNode(OpExpr); + result->opno = OID_JSON_ARRAY_ELEMENT_OP; + result->opresulttype = JSONOID; + result->opfuncid = get_opcode(result->opno); + result->args = list_make2(first_arg, index); + result->location = exprLocation(index); + + return (Node *)result; +} +/* + * ParseJsonSimplifiedAccessorObjectField - + * handles function calls with a single argument that is of json type. + * If the function call is actually a column projection, return a suitably + * transformed expression tree. If not, return NULL. + */ +Node * +ParseJsonSimplifiedAccessorObjectField(ParseState *pstate, const char *funcname, + Node *first_arg, int location) +{ + OpExpr *result; + Node *rexpr; + rexpr = (Node *) makeConst( + TEXTOID, + -1, + InvalidOid, + -1, + CStringGetTextDatum(funcname), + false, + false); + + result = makeNode(OpExpr); + if (exprType(first_arg) == JSONOID) { + result->opno = OID_JSON_OBJECT_FIELD_OP; + result->opresulttype = JSONOID; + } else { + Assert(exprType(first_arg) == JSONBOID); + result->opno = OID_JSONB_OBJECT_FIELD_OP; + result->opresulttype = JSONBOID; + } + result->opfuncid = get_opcode(result->opno); + result->args = list_make2(first_arg, rexpr); + result->location = location; + return (Node *) result; +} + /* * ParseComplexProjection - * handles function calls with a single argument that is of complex type. diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_operator.dat index 0e7511dde1..e375c49252 100644 --- a/src/include/catalog/pg_operator.dat +++ b/src/include/catalog/pg_operator.dat @@ -3154,13 +3154,13 @@ oprname => '*', oprleft => 'anyrange', oprright => 'anyrange', oprresult => 'anyrange', oprcom => '*(anyrange,anyrange)', oprcode => 'range_intersect' }, -{ oid => '3962', descr => 'get json object field', +{ oid => '3962', oid_symbol => 'OID_JSON_OBJECT_FIELD_OP', descr => 'get json object field', oprname => '->', oprleft => 'json', oprright => 'text', oprresult => 'json', oprcode => 'json_object_field' }, { oid => '3963', descr => 'get json object field as text', oprname => '->>', oprleft => 'json', oprright => 'text', oprresult => 'text', oprcode => 'json_object_field_text' }, -{ oid => '3964', descr => 'get json array element', +{ oid => '3964', oid_symbol => 'OID_JSON_ARRAY_ELEMENT_OP', descr => 'get json array element', oprname => '->', oprleft => 'json', oprright => 'int4', oprresult => 'json', oprcode => 'json_array_element' }, { oid => '3965', descr => 'get json array element as text', @@ -3172,7 +3172,7 @@ { oid => '3967', descr => 'get value from json as text with path elements', oprname => '#>>', oprleft => 'json', oprright => '_text', oprresult => 'text', oprcode => 'json_extract_path_text' }, -{ oid => '3211', descr => 'get jsonb object field', +{ oid => '3211', oid_symbol => 'OID_JSONB_OBJECT_FIELD_OP', descr => 'get jsonb object field', oprname => '->', oprleft => 'jsonb', oprright => 'text', oprresult => 'jsonb', oprcode => 'jsonb_object_field' }, { oid => '3477', descr => 'get jsonb object field as text', diff --git a/src/include/parser/parse_func.h b/src/include/parser/parse_func.h index c7ba99dee7..6b7759cbc7 100644 --- a/src/include/parser/parse_func.h +++ b/src/include/parser/parse_func.h @@ -35,6 +35,9 @@ extern Node *ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, Node *last_srf, FuncCall *fn, bool proc_call, int location); +extern Node *ParseJsonSimplifiedAccessorArrayElement(ParseState *pstate, A_Indices *subscript, + Node *first_arg, int location); + extern FuncDetailCode func_get_detail(List *funcname, List *fargs, List *fargnames, int nargs, Oid *argtypes, diff --git a/src/include/parser/parse_type.h b/src/include/parser/parse_type.h index b62e7a6ce9..9c8b3bfb2f 100644 --- a/src/include/parser/parse_type.h +++ b/src/include/parser/parse_type.h @@ -57,5 +57,6 @@ extern bool parseTypeString(const char *str, Oid *typeid_p, int32 *typmod_p, /* true if typeid is composite, or domain over composite, but not RECORD */ #define ISCOMPLEX(typeid) (typeOrDomainTypeRelid(typeid) != InvalidOid) +#define ISJSON(typeid) (typeid == JSONOID || typeid == JSONBOID) #endif /* PARSE_TYPE_H */ diff --git a/src/test/regress/expected/json.out b/src/test/regress/expected/json.out index 96c40911cb..f6b7af3ecd 100644 --- a/src/test/regress/expected/json.out +++ b/src/test/regress/expected/json.out @@ -2716,3 +2716,110 @@ select ts_headline('[]'::json, tsquery('aaa & bbb')); [] (1 row) +-- simple dot notation +drop table if exists test_json_dot; +NOTICE: table "test_json_dot" does not exist, skipping +create table test_json_dot(id int, test_json json); +insert into test_json_dot select 1, '{"a": 1, "b": 42}'::json; +insert into test_json_dot select 1, '{"a": 2, "b": {"c": 42}}'::json; +insert into test_json_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::json; +insert into test_json_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[{"x": [11, 12]}, {"y": [21, 22]}]}'::json; +-- member object access +select (test_json_dot.test_json).b, json_query(test_json, 'lax $.b' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; + b | expected +-------------+------------- + 42 | 42 + {"c": 42} | {"c": 42} + {"c": "42"} | {"c": "42"} + {"c": "42"} | {"c": "42"} +(4 rows) + +select (test_json_dot.test_json).b.c, json_query(test_json, 'lax $.b.c' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; + c | expected +------+---------- + | + 42 | 42 + "42" | "42" + "42" | "42" +(4 rows) + +select (test_json_dot.test_json).d, json_query(test_json, 'lax $.d' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; + d | expected +------------------------------------+------------------------------------ + | + | + [11, 12] | [11, 12] + [{"x": [11, 12]}, {"y": [21, 22]}] | [{"x": [11, 12]}, {"y": [21, 22]}] +(4 rows) + +select (test_json_dot.test_json)."d", json_query(test_json, 'lax $.d' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; + d | expected +------------------------------------+------------------------------------ + | + | + [11, 12] | [11, 12] + [{"x": [11, 12]}, {"y": [21, 22]}] | [{"x": [11, 12]}, {"y": [21, 22]}] +(4 rows) + +select (test_json_dot.test_json).'d' from test_json_dot; +ERROR: syntax error at or near "'d'" +LINE 1: select (test_json_dot.test_json).'d' from test_json_dot; + ^ +select (test_json_dot.test_json)['d'] from test_json_dot; +ERROR: json subscript must be coercible to integer +LINE 1: select (test_json_dot.test_json)['d'] from test_json_dot; + ^ +-- array element access +select (test_json_dot.test_json).d->0 from test_json_dot; + ?column? +----------------- + + + 11 + {"x": [11, 12]} +(4 rows) + +select (test_json_dot.test_json).d[0], json_query(test_json, 'lax $.d[0]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; + d | expected +-----------------+----------------- + | + | + 11 | 11 + {"x": [11, 12]} | {"x": [11, 12]} +(4 rows) + +select (test_json_dot.test_json).d[1], json_query(test_json, 'lax $.d[1]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; + d | expected +-----------------+----------------- + | + | + 12 | 12 + {"y": [21, 22]} | {"y": [21, 22]} +(4 rows) + +select (test_json_dot.test_json).d[0:] from test_json_dot; +ERROR: json subscript does not support slices +LINE 1: select (test_json_dot.test_json).d[0:] from test_json_dot; + ^ +select (test_json_dot.test_json).d[0::int] from test_json_dot; + d +----------------- + + + 11 + {"x": [11, 12]} +(4 rows) + +select (test_json_dot.test_json).d[0::float] from test_json_dot; +ERROR: json subscript must be coercible to integer +LINE 1: select (test_json_dot.test_json).d[0::float] from test_json_... + ^ +select (test_json_dot.test_json).d[0].x[1], json_query(test_json, 'lax $.d[0].x[1]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; + x | expected +----+---------- + | + | + | + 12 | 12 +(4 rows) + diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out index 7d163a156e..954efe67b6 100644 --- a/src/test/regress/expected/jsonb.out +++ b/src/test/regress/expected/jsonb.out @@ -5715,3 +5715,89 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8; 12345 (1 row) +-- simple dot notation +drop table if exists test_jsonb_dot; +NOTICE: table "test_jsonb_dot" does not exist, skipping +create table test_jsonb_dot(id int, test_jsonb jsonb); +insert into test_jsonb_dot select 1, '{"a": 1, "b": 42}'::json; +insert into test_jsonb_dot select 1, '{"a": 2, "b": {"c": 42}}'::json; +insert into test_jsonb_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::json; +-- member object access +select (test_jsonb_dot.test_jsonb).b from test_jsonb_dot; + b +------------- + 42 + {"c": 42} + {"c": "42"} +(3 rows) + +select (test_jsonb_dot.test_jsonb).b.c from test_jsonb_dot; + c +------ + + 42 + "42" +(3 rows) + +select (test_jsonb_dot.test_jsonb).d from test_jsonb_dot; + d +---------- + + + [11, 12] +(3 rows) + +select (test_jsonb_dot.test_jsonb)."d" from test_jsonb_dot; + d +---------- + + + [11, 12] +(3 rows) + +select (test_jsonb_dot.test_jsonb).'d' from test_jsonb_dot; +ERROR: syntax error at or near "'d'" +LINE 1: select (test_jsonb_dot.test_jsonb).'d' from test_jsonb_dot; + ^ +select (test_jsonb_dot.test_jsonb)['d'] from test_jsonb_dot; + test_jsonb +------------ + + + [11, 12] +(3 rows) + +-- array element access +select (test_jsonb_dot.test_jsonb).d[0] from test_jsonb_dot; + d +---- + + + 11 +(3 rows) + +select (test_jsonb_dot.test_jsonb).d[0:] from test_jsonb_dot; +ERROR: jsonb subscript does not support slices +LINE 1: select (test_jsonb_dot.test_jsonb).d[0:] from test_jsonb_dot... + ^ +select (test_jsonb_dot.test_jsonb).d[0::int] from test_jsonb_dot; + d +---- + + + 11 +(3 rows) + +select (test_jsonb_dot.test_jsonb).d[0::float] from test_jsonb_dot; +ERROR: subscript type double precision is not supported +LINE 1: select (test_jsonb_dot.test_jsonb).d[0::float] from test_jso... + ^ +HINT: jsonb subscript must be coercible to either integer or text. +select (test_jsonb_dot.test_jsonb).d[0].x[1] from test_jsonb_dot; + x +--- + + + +(3 rows) + diff --git a/src/test/regress/sql/json.sql b/src/test/regress/sql/json.sql index 8251f4f400..21450c4991 100644 --- a/src/test/regress/sql/json.sql +++ b/src/test/regress/sql/json.sql @@ -869,3 +869,28 @@ select ts_headline('english', '{"a": "aaa bbb", "b": {"c": "ccc ddd fff", "c1": select ts_headline('null'::json, tsquery('aaa & bbb')); select ts_headline('{}'::json, tsquery('aaa & bbb')); select ts_headline('[]'::json, tsquery('aaa & bbb')); + +-- simple dot notation +drop table if exists test_json_dot; +create table test_json_dot(id int, test_json json); +insert into test_json_dot select 1, '{"a": 1, "b": 42}'::json; +insert into test_json_dot select 1, '{"a": 2, "b": {"c": 42}}'::json; +insert into test_json_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::json; +insert into test_json_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[{"x": [11, 12]}, {"y": [21, 22]}]}'::json; + +-- member object access +select (test_json_dot.test_json).b, json_query(test_json, 'lax $.b' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; +select (test_json_dot.test_json).b.c, json_query(test_json, 'lax $.b.c' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; +select (test_json_dot.test_json).d, json_query(test_json, 'lax $.d' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; +select (test_json_dot.test_json)."d", json_query(test_json, 'lax $.d' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; +select (test_json_dot.test_json).'d' from test_json_dot; +select (test_json_dot.test_json)['d'] from test_json_dot; + +-- array element access +select (test_json_dot.test_json).d->0 from test_json_dot; +select (test_json_dot.test_json).d[0], json_query(test_json, 'lax $.d[0]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; +select (test_json_dot.test_json).d[1], json_query(test_json, 'lax $.d[1]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; +select (test_json_dot.test_json).d[0:] from test_json_dot; +select (test_json_dot.test_json).d[0::int] from test_json_dot; +select (test_json_dot.test_json).d[0::float] from test_json_dot; +select (test_json_dot.test_json).d[0].x[1], json_query(test_json, 'lax $.d[0].x[1]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql index 5f0190d5a2..f095dc2bbe 100644 --- a/src/test/regress/sql/jsonb.sql +++ b/src/test/regress/sql/jsonb.sql @@ -1559,3 +1559,25 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8; select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2; select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4; select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8; + +-- simple dot notation +drop table if exists test_jsonb_dot; +create table test_jsonb_dot(id int, test_jsonb jsonb); +insert into test_jsonb_dot select 1, '{"a": 1, "b": 42}'::json; +insert into test_jsonb_dot select 1, '{"a": 2, "b": {"c": 42}}'::json; +insert into test_jsonb_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::json; + +-- member object access +select (test_jsonb_dot.test_jsonb).b from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb).b.c from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb).d from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb)."d" from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb).'d' from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb)['d'] from test_jsonb_dot; + +-- array element access +select (test_jsonb_dot.test_jsonb).d[0] from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb).d[0:] from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb).d[0::int] from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb).d[0::float] from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb).d[0].x[1] from test_jsonb_dot; -- 2.39.5 (Apple Git-154) ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-09-23 19:22 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-08-05 02:04 [PATCH v2] Avoid spurious CREATE INDEX CONCURRENTLY waits Alvaro Herrera <[email protected]> 2024-09-23 19:22 Re: SQL:2023 JSON simplified accessor support Alexandra Wang <[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