Re: [HACKERS] SQL/JSON in PostgreSQL

ns. Attached patch implements this feature for JSON query functions, JSON_TABLE is not supported now because it needs some refactoring. I have pushed this commit to the separate branch because it is not finished yet: https://github.com/postgrespro/sqljson/tree/sqljson_variable_json_path -- Nikita

Re: [HACKERS] SQL/JSON in PostgreSQL

bquery variant of JSON_ARRAY I can only offer to leave transformation into a subselect with JSON_ARRAYAGG(): JSON_ARRAY(query) => (SELECT JSON_ARRAYAGG(bar) FROM (query) foo(bar)) -- Nikita Glukhov Postgres Professional:http://www.postgrespro.com The Russian Postgres Company -- Sent via pgsq

Re: [HACKERS] SQL/JSON in PostgreSQL

On 29.09.2017 12:59, Pavel Stehule wrote: Hi 2017-09-16 1:31 GMT+02:00 Nikita Glukhov <mailto:n.glu...@postgrespro.ru>>: On 15.09.2017 22:36, Oleg Bartunov wrote: On Fri, Sep 15, 2017 at 7:31 PM, Robert Haas mailto:robertmh...@gmail.com>> wrote:

Re: [HACKERS] compress method for spgist - 2

ere was no special processing for NaNs in KNN queue's comparison function. At first I tried just to set recheck flag for NULL distances, but it did not work for index-only scans because they do not support rechecking. So I had to add a special flag for NULL distances. Should I start a separ

Re: [HACKERS] compress method for spgist - 2

ox->low.x to be NaN when circle->center.x is and circle->radius are both +Infinity.  Without this float-order-preserving swapping one regression test for KNN with ORDER BY index will be totally broken (you can try it: https://github.com/glukhovn/postgres/tree/knn). Unfortunately, I

Re: [HACKERS] SQL/JSON in PostgreSQL

On 18.09.2017 00:38, Alvaro Herrera wrote: Nikita Glukhov wrote: 0007-json_table-v02.patch: - JSON_TABLE using XMLTABLE infrastructure 0008-json_table-json-v02.patch: - JSON_TABLE support for json type I'm confused ... why are these two patches and not a single one? As I sad b

Re: [HACKERS] PATCH: recursive json_populate_record()

On 30.05.2017 02:31, Tom Lane wrote: Nikita Glukhov writes: 2. Also I've found a some kind of thinko in JsGetObjectSize() macro, but it seems that this obvious mistake can not lead to incorrect behavior. Hm, I think it actually was wrong for the case of jsonb_cont == NULL, wasn't

Re: [HACKERS] PATCH: recursive json_populate_record()

NULL fields. So I think it would be better to throw an error in jsonb case too, but I'm not sure. 2. Also I've found a some kind of thinko in JsGetObjectSize() macro, but it seems that this obvious mistake can not lead to incorrect behavior. -- Nikita Glukhov Postgres Pr

Re: [HACKERS] Fix freeing of dangling IndexScanDesc.xs_hitup in GiST

On 04.05.2017 22:16, Tom Lane wrote: Nikita Glukhov writes: In gistrescan() IndexScanDesc.xs_hitup is not reset after MemoryContextReset() of so->queueCxt in which xs_hitup was allocated, then getNextNearest() tries to pfree() dangling xs_hitup, which results in the reuse of this poin

[HACKERS] Fix freeing of dangling IndexScanDesc.xs_hitup in GiST

esent in v9.5, v9.6, v10.0. -- Nikita Glukhov Postgres Professional:http://www.postgrespro.com The Russian Postgres Company diff --git a/src/backend/access/gist/gistscan.c b/src/backend/access/gist/gistscan.c index 2526a39..580b6ac 100644 --- a/src/backend/access/gist/gistscan.c +++ b/src/

Re: [HACKERS] PATCH: recursive json_populate_record()

parsing passes for nested json objects/arrays instead of a single parsing pass. -- Nikita Glukhov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c index bf2c91f..da29dab 100644 --- a/sr

Re: [HACKERS] [PATCH]: fix bug in SP-GiST box_ops

ll make sense, but the Kyotaro's test case doesn't work for me and I still don't know how to force SP-GiST to create inner leaves without inserting hundreds of rows. -- Nikita Glukhov Postgres Professional:http://www.postgrespro.com The Russian Postgres Company diff --git a/src/backend

Re: [HACKERS] [PATCH] kNN for SP-GiST

Attached v02 version (rebased to HEAD). -- Nikita Glukhov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company diff --git a/src/backend/access/gist/gistutil.c b/src/backend/access/gist/gistutil.c index f92baed..1dfaba2 100644 --- a/src/backend/access/gist/gistutil.c

Re: [HACKERS] Cast jsonb to numeric, int, float, bool

On 02.02.2017 01:07, Jim Nasby wrote: On 2/1/17 8:26 AM, Nikita Glukhov wrote: Some comments about the code: I think it would be better to * add function for extraction of scalars from pseudo-arrays * iterate until WJB_DONE to pfree iterator I'm not sure what your intent here is, but i

Re: [HACKERS] Cast jsonb to numeric, int, float, bool

PG_RETURN_INT32(DatumGetInt32(DirectFunctionCall1(numeric_int4, NumericGetDatum(v.val.numeric; } -- Nikita Glukhov Postgres Professional:http://www.postgrespro.com The Russian Postgres Company -- Sent via pgsql-hackers mailing list

Re: [HACKERS] [PATCH]: fix bug in SP-GiST box_ops

r_consistent() using only one box because index tree must have at least one inner node (or 157 boxes) in order to spg_box_quad_inner_consistent() began to be called. That's why existing tests for box_ops didn't detect this bug: box_temp table has only 56 rows. -- Nikita Glukhov Pos

[HACKERS] [PATCH] kNN for SP-GiST

thub.com/glukhovn/postgres/commits/knn_spgist Please note that the tests for circle_ops and poly_ops will not work until the а bug found in SP-GiST box_ops will not be fixed (see https://www.postgresql.org/message-id/9ea5b157-478c-8874-bc9b-050076b7d042%40postgrespro.ru). -- Nikita Glukhov Postgres

Re: [HACKERS] [PATCH]: fix bug in SP-GiST box_ops

On 30.01.2017 12:04, Kyotaro HORIGUCHI wrote: Hello, At Mon, 30 Jan 2017 07:12:03 +0300, Nikita Glukhov wrote in <9ea5b157-478c-8874-bc9b-050076b7d...@postgrespro.ru>: Working on the tests for new SP-GiST opclasses for polygons and circles, I've found a bug in the SP-GiST box_op

[HACKERS] [PATCH]: fix bug in SP-GiST box_ops

GiST index scan (see tests in the attached patch, their results were taken from a sequential scan). -- Nikita Glukhov Postgres Professional:http://www.postgrespro.com The Russian Postgres Company diff --git a/src/backend/utils/adt/geo_spgist.c b/src/backend/utils/adt/geo_spgist.c index aacb340..446

Re: [HACKERS] PATCH: recursive json_populate_record()

at; /* try to convert json string to a non-scalar type through input function */ if (JsValueIsString(jsv) && (typcat == TYPECAT_ARRAY || typcat == TYPECAT_COMPOSITE)) typcat = TYPECAT_SCALAR; /* we must perform domain checks for NULLs */ if (*isnul

Re: [HACKERS] PATCH: recursive json_populate_record()

BJECT) != 0) Sorry for this obvious mistake. But macros JB_ROOT_IS_XXX() also contain the same hazard. 7. More generally, the patch is hard to review because it whacks the existing code around so thoroughly that it's tough even to match up old and new code to get a handle on what you chang

[HACKERS] [PATCH] fix typo in commit a4523c5

scan = OFF; ... RESET enable_indexscan; Obviously, the last line should be RESET enable_indexonlyscan; -- Nikita Glukhov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index

Re: [HACKERS] [PATCH] kNN for btree

f btree_gist from 1.3 to 1.4 would fail. 7. Add regression tests for btree kNN. Tests were added only after the built-in distance operators were added. -- Nikita Glukhov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company -- Sent via pgsql-hackers mailing list (p

Re: [HACKERS] PATCH: recursive json_populate_record()

behavior that the second patch brings in (see changes in json.out and jsonb.out). -- Nikita Glukhov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 10e3186..55cacfb 100644 --- a/doc/src/sgml/func.

Re: [HACKERS] PATCH: recursive json_populate_record()

populate_composite() and also in populate_record_worker() (by using populate_composite() instead of populate_record()). These improvements can speed up bulk jsonb conversion by 15-20% in the simple test with two nested records. -- Nikita Glukhov Postgres Professional: http://www.postgrespro.c

[HACKERS] PATCH: recursive json_populate_record()

true" The second patch adds assignment of correct ndims to array fields of RECORD function result types. Without this patch, attndims in tuple descriptors of RECORD types is always 0 and the corresponding assertion fails in the next test: [patched]=# select json_to_record('{"a": [

Re: [HACKERS] Bug in comparison of empty jsonb arrays to scalars

#x27;), ('[true]'), ('[[]]'), ('[{}]'), ('{"a": null}'), ('{"a": 0}'), ('{"a": ""}'), ('{"a": true}'), ('{"a": []}'), ('{"a": {}}') ) va

[HACKERS] Bug in comparison of empty jsonb arrays to scalars

ached patch contains: 1. bug fix (added the missing "else" in compareJsonbContainers()) 2. regression test 3. pg_upgrade: invalidation of btree indexes on jsonb columns and REINDEX-script generation -- Nikita Glukhov Postgres Professional: http://www.postgrespro.com The Russian Po

Re: [HACKERS] BUG #12330: ACID is broken for unique constraints

I believe, the objections expressed in this thread miss a very important point of all this: the isolation property (the "I" in ACID) is violated. Here’s a quote from the Wikipedia article on ACID : The isolation property ensures that the concurrent execution of

Re: [HACKERS] BUG #12330: ACID is broken for unique constraints

> [combining replies -- nikita, better not to top-post (FYI)] I'm sorry. I don't know what you mean. I just replied to an email. > To prove your statement, you need to demonstrate how a transaction left the database in a bad state given concurrent activity without count

Re: [HACKERS] BUG #12330: ACID is broken for unique constraints

I'll repost my (OP) case, for the references to it to make more sense to the others. Having the following table: CREATE TABLE "song_artist" ( "song_id" INT8 NOT NULL, "artist_id" INT8 NOT NULL, PRIMARY KEY ("song_id", "artist_id") ); Even trying to protect from this wit

Re: [HACKERS] PostgreSQL - 'SKYLINE OF' clause added!

Few things from our side: 1. 'Skyline Of' is a new operator proposed in ICDE 2003, one of the topmost conferences of Data Engineering. Skyline operation is a hot area of research in query processing. Many of the database community people do know about this operator, and it is fast catching the at