Re: Re: SQL/JSON: functions
On 9/27/19 9:42 PM, Nikita Glukhov wrote: Attached 39th version of the patches rebased onto current master. -- Nikita Glukhov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company I am unable to cleanly apply this patch. I've tried applying to the current master (4f4061b2dd) I've also tried apply to commit b81a9c2fc5 back through 709d003fbd, with no success. Can you please tell me to which commit I can apply the patch? Is the following command correct for applying the patch files? `patch -p1 < ~/Downloads/0001-Jsonpath-support-for-json-v39.patch` Thanks for your help, Andrew Alsup
Re: Re: SQL/JSON: functions
On 10/21/19 12:44 PM, Nikita Glukhov wrote: v39 patch is based on 5ee96b3e2221d154ffcb719bd2dee1179c53f821 Use the following git command to apply patches: git am ~/Downloads/0001-Jsonpath-support-for-json-v39.patch Thank you. The patch applied fine, with no errors. Is this the type of testing that would be helpful? I plan to construct a number of separate queries to test more nuanced edge cases. This test simply compares the output of 4 separate sub-queries that should provide the same results. SELECT CASE WHEN jop = jp_expr AND jop = jval AND jop = jval_path THEN 'pass' ELSE 'fail' END FROM ( -- jsonb operator SELECT count(*) FROM testjsonb WHERE j->>'abstract' LIKE 'A%' ) as jop, ( -- jsonpath expression SELECT count(*) FROM testjsonb WHERE j @? '$.abstract ? (@ starts with "A")' ) as jp_expr, ( -- json_value() SELECT count(*) FROM testjsonb WHERE JSON_VAlUE(j, 'lax $.abstract') LIKE 'A%' ) as jval, ( -- json_value(jsonpath) SELECT count(*) FROM testjsonb WHERE JSON_VALUE(j, 'lax $.abstract ? (@ starts with "A")') IS NOT NULL ) as jval_path; If I'm completely off base for how testing is normally conducted, please let me know. Thanks, Andrew Alsup
Re: Re: SQL/JSON: functions
On 3/5/19 5:35 PM, Nikita Glukhov wrote:
Attached 36th version of the patches rebased onto jsonpath v36.
While testing this patch a found a few issues:
[1] I was not able to apply the patch to the current HEAD. However, it
applies cleanly to commit: e988878f85 (NOTE: I did not investigate which
commit between e988878f85 and HEAD caused problems).
[2] JsonPath array slicing does not work. I'm not aware of a
comprehensive list of JsonPath features/syntax that is targeted for
support; however, I did try various forms of array slicing, which don't
currently work.
Here are a few examples:
The input document is the same in each example.
{
"a1": 123,
"b1": "xxx",
"c1": {
"a2": 456,
"b2": "yyy",
"c2": [
{"a3": 777, "b3": "7z"},
{"a3": 888, "b3": "8z"}
]
}
}
array wildcard selector [*] works: $.c1.c2[*].b3
# select json_path_query('{"a1": 123, "b1": "xxx", "c1": {"a2": 456,
"b2": "yyy", "c2": [{"a3": 777, "b3": "7z"},{"a3": 888, "b3":
"8z"}]}}'::json, '$.c1.c2[*].b3'::jsonpath);
json_path_query
-
"7z"
"8z"
(2 rows)
array index selector [0] works: $.c1.c2[0].b3
jsonpatch=# select json_path_query('{"a1": 123, "b1": "xxx", "c1":
{"a2": 456, "b2": "yyy", "c2": [{"a3": 777, "b3": "7z"},{"a3": 888,
"b3": "8z"}]}}'::json, '$.c1.c2[0].b3'::jsonpath);
json_path_query
-
"7z"
(1 row)
array slicing [0:], [:1], and [0:1] do not work:$.c1.c2[0:].b3
# select json_path_query('{"a1": 123, "b1": "xxx", "c1": {"a2": 456,
"b2": "yyy", "c2": [{"a3": 777, "b3": "7z"},{"a3": 888, "b3":
"8z"}]}}'::json, '$.c1.c2[0:].b3'::jsonpath);
2019-05-13 20:47:48.740 EDT [21856] ERROR: bad jsonpath representation
at character 147
2019-05-13 20:47:48.740 EDT [21856] DETAIL: syntax error, unexpected
':', expecting ',' or ']' at or near ":"
2019-05-13 20:47:48.740 EDT [21856] STATEMENT: select
json_path_query('{"a1": 123, "b1": "xxx", "c1": {"a2": 456, "b2": "yyy",
"c2": [{"a3": 777, "b3": "7z"},{"a3": 888, "b3": "8z"}]}}'::json,
'$.c1.c2[0:].b3'::jsonpath);
ERROR: bad jsonpath representation
LINE 1: ...7, "b3": "7z"},{"a3": 888, "b3": "8z"}]}}'::json, '$.c1.c2[0...
^
DETAIL: syntax error, unexpected ':', expecting ',' or ']' at or near ":"
Cell-Level security
Contrary to popular understanding, in classified environments it is common to have data marked with a variety of combinations that make it difficult to create roles and labels that match the various permutations. As a simple example, a document could be classified as follows: Releasable to US citizens with a secret clearance OR Canadian citizens with a top-secret clearance OR Australian citizens with a top-secret clearance. I am developing an extension that exposes a function, callable from a PostgreSQL RLS policy which supports Accumulo-style visibility expressions. Using the example above, the record might contain the following security label expression: (S&US)|(TS&CAN)|(TS&AUS) A little more info on Accumulo-style expressions can be found here: https://accumulo.apache.org/1.6/apidocs/org/apache/accumulo/core/security/ColumnVisibility.html Although I still have more testing to do, things are working well, and there are some options about where the RLS policy function can pull the authorizations from when determining visibility. Currently JWT tokens (via set_config) are supported. I'm wondering how feasible a cell-level security implementation would be. My thought is that by using an hstore column, a table could opt-in to row- and cell-level security, by having the hstore track the visibility of any or all columns in the row. | id | fname | lname | vis(hstore) | ||---|---|-| | 1 | Bob | Umpty | vis[_row_] = 'U'| || | | vis[fname] = 'S'| || | | vis[lname] = 'U&USA'| ||---|---|-| | 2 | Alice | Skwat | vis[_row_] = 'S'| ||---|---|-| For tables that have the vis(hstore) column, a query rewrite could ensure that column references are wrapped in a visibility check, akin to using a CASE statement within a SELECT. I've begun studying the call chain from parser to planner to rewrite to executor. I'm not sure where the best place would be to perform this work. I'm thinking the query-rewrite might work well. Thoughts?
Unable to `make install` on MacOS in the latest master (68a13f28be)
I am unable to `make install` on MacOS in the latest master (68a13f28be). Here are the steps to reproduce. OS: MacOSX 10.14.2 Branch: master:HEAD (68a13f28be) $ git log --pretty=format:'%h' -n 1 68a13f28be $ ./configure --with-bonjour $ make $ sudo make install ... /usr/bin/install -c -m 644 utils/errcodes.h '/usr/local/pgsql/include/server/utils' /usr/bin/install -c -m 644 utils/fmgroids.h '/usr/local/pgsql/include/server/utils' /usr/bin/install -c -m 644 utils/fmgrprotos.h '/usr/local/pgsql/include/server/utils' cp ./*.h '/usr/local/pgsql/include/server'/ cp: ./dynloader.h: No such file or directory make[2]: *** [install] Error 1 make[1]: *** [install-include-recurse] Error 2 make: *** [install-src-recurse] Error 2 FWIW, I've also tried `./configure` without any flags, but that didn't effect the results. I am able to successfully build/install from branch `REL_11_STABLE` (ad425aaf06)
Re: Unable to `make install` on MacOS in the latest master (68a13f28be)
> As a general rule, it's wise to do "make distclean" before "git pull" > when you're tracking master. This saves a lot of grief when someone > rearranges the set of generated files, as happened here. (If things > are really messed up, you might need "git clean -dfx" to get rid of > everything not in git.) > > You might worry that this will greatly increase the rebuild time, > which it will if you don't take precautions. The way to fix that > is (1) use ccache and (2) set the configure script to use a cache > file. > > regards, tom lane Tom and Daniel, Thanks for the help on "make distclean". That did the trick. I will be more careful when pulling master. Somehow, I hadn't been hit with this before, which was just dumb luck. Thanks for helping me out. -- Andy
Re: SQL/JSON: functions
> Attached 21st version of the patches. > > I decided to include here patch with complete jsonpath implementation (it > is a squash of all 6 jsonpath-v21 patches). I hope this will simplify reviewing > and testing in cfbot.cputube.org. I'd like to help in reviewing this patch. Please let me know if there's something in particular I should focus on such as documentation, functionality, or source. If not, I'll probably just proceed in that order. Regards, Andy Alsup
Re: SQL/JSON: functions
> Attached patches implementing all SQL/JSON functions excluding JSON_TABLE: > > JSON_OBJECT() > JSON_OBJECTAGG() > JSON_ARRAY() > JSON_ARRAYAGG() > > JSON_EXISTS() > JSON_VALUE() > JSON_QUERY() Sorry if this is a stupid question, but is this patch intended to implement any SQL/JSON functions? I'm basing this question on the original patch post (quoted above). The patch appears to be focused exclusively on "jsonpath expressions". I only ask because I think I misinterpreted and spent some time wondering if I had missed a patch file. So far, the jsonpath docs are very readable; however, I think a few complete examples (full SELECT statements) of using jsonpath expressions would be helpful to someone new to this technology. Does postgresql provide a sample schema, similar to the Or*cle scott/tiger (emp/dept) schema, we could use for examples? Alternatively, we could reference something mentioned at https://wiki.postgresql.org/wiki/Sample_Databases. I think it would be nice if all the docs referenced the same schema, when possible. For tests, would it be helpful to have some tests that demonstrate/assert equality between "jsonb operators" and "jsonpath expressions"? For example, using the existing regression test data the following should assert equality in operator vs. expression: SELECT CASE WHEN jop_count = expr_count THEN 'pass' ELSE 'fail' END FROM ( -- jsonb operator SELECT count(*) FROM testjsonb WHERE j->>'abstract' LIKE 'A%' ) as jop_count, ( -- jsonpath expression SELECT count(*) FROM testjsonb WHERE j @? '$.abstract ? (@ starts with "A")' ) as expr_count;
Re: UUID v7
Sergey, I took a look at your patch for chapter 9.14 "UUID Functions" docs page. You've added some really good content here. I think section 9.14.4. "Deciding Whether and Which UUID to Use" would be better suited for Chapter 8: "Data Types" -- specifically, 8.12. "UUID Type", since the content seems to deal more with the UUID data type than the UUID functions. Best regards, Andy A. On Sun, Feb 16, 2025 at 8:13 PM Sergey Prokhorenko < sergeyprokhore...@yahoo.com.au> wrote: > On Wednesday 5 February 2025 at 01:07:02 am GMT+3, Masahiko Sawada < > sawada.m...@gmail.com> wrote: > > > On Sun, Feb 2, 2025 at 11:41 AM Sergey Prokhorenko > wrote: > > > > Dear colleagues, > > > > I would like to present for discussion my attached new draft > documentation on UUID functions (Section 9.14. UUID Functions), which > replaces the previously proposed draft at > https://www.postgresql.org/docs/devel/functions-uuid.html. I have > preserved and significantly supplemented the text that was there. > > > > I have the following goals: > > > > 1. State that from now on, the function uuidv7(), rather than > autoincrement, is the default choice for generating primary keys > > > > 2. Describe the advantages of uuidv7() over autoincrement and uuidv4() > > > > 3. Refute the often-cited imaginary disadvantages of UUIDv7 compared to > autoincrement, such as: > > > >- Lower performance (see the refutation in the article "UUID > Benchmark War" https://ardentperf.com/2024/02/03/uuid-benchmark-war/) > > > >- Disclosure of date and time of record creation in the table (in > reality, the timestamp offset parameter distorts this information) > > > > 4. Confirm the fault tolerance of the uuidv7() function in all possible > critical situations, namely: > > > >- System clock failure > > > >- Receiving an invalid value of the offset argument, which would > result in a timestamp overflow or a negative timestamp > > > > Thank you for the proposal. Could you share the proposed document as a > .diff or .patch file? That would be easier to review the updates. > > > Regards, > > > -- > Masahiko Sawada > Amazon Web Services: https://aws.amazon.com > > _ > > Dear colleagues, > > I have attached a file changed.diff containing my proposed changes to the > documentation on UUID functions (Section 9.14. UUID Functions). The text > of the changes in this file is updated, and it is slightly different from > the previously submitted text. > > > Regards, > > Sergey Prokhorenko > sergeyprokhore...@yahoo.com.au > > > > >
