Attached 34th version of the patches.
1. Partial jsonpath support: - Fixed copying of jsonb with vars jsonb_path_query() into SRF context - Fixed error message for jsonpath vars - Fixed file-level comment in jsonpath.c
2. Suppression of numeric errors: Now error handling is done without PG_TRY/PG_CATCH using a bunch of internal numeric functions with 'bool *error' flag.
3. Datetime support: Problems with timzeones still exist. Comparison of tz types with non-tz types is simply disallowed. Default integer timezone offset (not abbreviation) can be specified with the second .datetime() argument. Error handling also is done using internal functions.
4. Json type support: Json support was completely refactored since v23: double compilation with function redefinitions was replaced with passing 'isJsonb' flag to low-level json/jsonb access functions. Also major refactoring with introduction of struct JsonItem was made. JsonItem is used in executor instead of raw JsonbValue. This helps to avoid extending of JsonbValue for datetime types and also other numeric types (integers and floats) required by standard.
5. GIN support: Nothing was changed since v23.
Patch 1 is what we are going to commit in PG12.
Patches 2 and 3 add code that was removed in the previous v33 version.
On 24.02.2019 15:34, Alexander Korotkov wrote:
On Sun, Feb 24, 2019 at 2:44 PM Tomas Vondra
<tomas.vondra@2ndquadrant.com> wrote:
On 2/24/19 10:03 AM, Alexander Korotkov wrote:
Attached is revised version of jsonpath. Assuming that jsonpath have
problem places, I decided to propose partial implementation.
Following functionality was cut from jsonpath:
1) Support of datetime datatypes. Besides error suppression, this
functionality have troubles with timezones. According to standard we
should support timezones in jsonpath expressions. But that would
prevent jsonpath functions from being immutable, that in turn limits
their usage in expression indexes.
Assuming we get the patch committed without the datetime stuff now, what
does that mean for the future? Does that mean we'll be unable to extend
it to support datetime in the future, or what? If we define the jsonpath
functions as immutable now, people may create indexes - which means we
won't be able to downgrade it to stable later.
So, what's the plan here? The only thing I can think of is having two
sets of functions - an immutable one, prohibiting datetime expressions,
and stable that can't be used for indexes etc.
Reasonable question. As I understand, not datetime support itself
making jsonpath functions not immutable, but implicit cast happening
during comparison timestamp vs. timestamptz (and time vs. timetz).
So, in future immutable functions can have limited support of
datetime, where comparison of non-tz vs. tz types is restricted. And
stable versions of functions (for instance, with '_tz' prefix) with
full datetime support.
I can also offset to explicitly pass timezone info into jsonpath function using
the special user dataype encapsulating struct pg_tz.
But simple integer timezone offset can be passed now using jsonpath variables
(standard says only about integer timezone offsets; also it requires presence
of timezone offset it in the input string if the format string contain timezone
components):
=# SELECT jsonb_path_query( '"28-02-2019 12:34"', '$.datetime("DD-MM-YYYY HH24:MI TZH", $tz)', jsonb_build_object('tz', EXTRACT(TIMEZONE FROM now())) ); jsonb_path_query
-----------------------------"2019-02-28T12:34:00+03:00"
(1 row)
2) Suppression of numeric errors. I will post it as a separate patch.
Pushing this even this partial implementation of jsonpath to PG 12 is
still very useful. Also it will simplify a lot pushing other parts of
SQL/JSON to future releases.
+1 to push at least partial (but still useful) subset, instead of just
bumping the patch to PG13
See patch #2.
Thank you for support!
