On Tue, Jul 14, 2026 at 6:44 PM Thom Brown <[email protected]> wrote: > > On Mon, 13 Jul 2026 at 14:30, Thom Brown <[email protected]> wrote: > > > > On Sun, 12 Jul 2026 at 18:17, Alexander Korotkov <[email protected]> > > wrote: > > > > > > On Sat, Jul 11, 2026 at 10:30 PM Thom Brown <[email protected]> wrote: > > > > On Sat, 11 Jul 2026 at 13:19, Alexander Korotkov <[email protected]> > > > > wrote: > > > > > On Fri, Jul 10, 2026 at 9:00 PM Thom Brown <[email protected]> wrote: > > > > > > I've tested patches 0001-0003. > > > > > > > > > > Thank you for testing the patchset. > > > > > > > > > > > 0001 - the ERROR ON ERROR query returns a NULL row again, so it now > > > > > > matches the docs. ERROR ON EMPTY is now output in pg_dump > > > > > > too.paragraph is accurate again. > > > > > > > > > > > > 0002 - PLAN (p0 OUTER (p1 INNER p11)) is now preserving parentheses, > > > > > > including a three-level (p1 INNER (p11 INNER p111)) chain and the > > > > > > mixed version (p0 OUTER ((p1 INNER p11) UNION p2)). Schema-only > > > > > > dump/restore of all test views work. > > > > > > > > > > Good. > > > > > > > > > > > 0003 - the silent column-drop now correctly errors with "PLAN clause > > > > > > for nested path json_table_path_1 was not found". > > > > > > > > > > > > However, I am getting a new error: > > > > > > > > > > > > postgres=# SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS > > > > > > (json_table_path_0 int PATH '$')) jt; > > > > > > ERROR: duplicate JSON_TABLE column or path name: json_table_path_0 > > > > > > LINE 1: SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS > > > > > > (json_table_... > > > > > > > > > > > > So I think patch 0003 needs a new version because it claims "a > > > > > > generated name can no longer coincide with a user-supplied one" > > > > > > > > > > Thank you for catching this. 0003 is revised. > > > > > > > > > > > I also did a few performance tests. I've noticed that JSON_TABLE > > > > > > queries using NESTED PATH have become noticeably slower since this > > > > > > commit, even when no PLAN clause is used. > > > > > > > > > > > > Here's what I get prior to this commit: > > > > > > > > > > > > postgres=# \timing > > > > > > Timing is on. > > > > > > postgres=# CREATE TEMP TABLE doc AS > > > > > > SELECT (SELECT jsonb_agg(jsonb_build_object('a', i, > > > > > > 'b', (SELECT jsonb_agg(j) FROM generate_series(1,1000) > > > > > > j))) > > > > > > FROM generate_series(1,1000) i) AS j; > > > > > > SELECT 1 > > > > > > Time: 195.441 ms > > > > > > postgres=# SELECT count(*) FROM doc, JSON_TABLE(doc.j, '$[*]' > > > > > > COLUMNS (a int PATH '$.a', > > > > > > NESTED PATH '$.b[*] ? (@ < 5)' COLUMNS (v int PATH '$'))) jt; > > > > > > count > > > > > > ------- > > > > > > 4000 > > > > > > (1 row) > > > > > > > > > > > > Time: 150.463 ms > > > > > > postgres=# SELECT count(*) FROM doc, JSON_TABLE(doc.j, '$[*]' > > > > > > COLUMNS (a int PATH '$.a', > > > > > > NESTED PATH '$.b[*] ? (@ < 5)' COLUMNS (v int PATH '$'))) jt; > > > > > > count > > > > > > ------- > > > > > > 4000 > > > > > > (1 row) > > > > > > > > > > > > Time: 146.457 ms > > > > > > postgres=# SELECT count(*) FROM doc, JSON_TABLE(doc.j, '$[*]' > > > > > > COLUMNS (a int PATH '$.a', > > > > > > NESTED PATH '$.b[*] ? (@ < 5)' COLUMNS (v int PATH '$'))) jt; > > > > > > count > > > > > > ------- > > > > > > 4000 > > > > > > (1 row) > > > > > > > > > > > > Time: 147.257 ms > > > > > > > > > > > > And post-commit: > > > > > > > > > > > > postgres=# CREATE TEMP TABLE doc AS > > > > > > SELECT (SELECT jsonb_agg(jsonb_build_object('a', i, > > > > > > 'b', (SELECT jsonb_agg(j) FROM generate_series(1,1000) > > > > > > j))) > > > > > > FROM generate_series(1,1000) i) AS j; > > > > > > SELECT 1 > > > > > > postgres=# \timing > > > > > > Timing is on. > > > > > > postgres=# SELECT count(*) FROM doc, JSON_TABLE(doc.j, '$[*]' > > > > > > COLUMNS (a int PATH '$.a', > > > > > > NESTED PATH '$.b[*] ? (@ < 5)' COLUMNS (v int PATH '$'))) jt; > > > > > > count > > > > > > ------- > > > > > > 4000 > > > > > > (1 row) > > > > > > > > > > > > Time: 536.018 ms > > > > > > postgres=# SELECT count(*) FROM doc, JSON_TABLE(doc.j, '$[*]' > > > > > > COLUMNS (a int PATH '$.a', > > > > > > NESTED PATH '$.b[*] ? (@ < 5)' COLUMNS (v int PATH '$'))) jt; > > > > > > count > > > > > > ------- > > > > > > 4000 > > > > > > (1 row) > > > > > > > > > > > > Time: 526.044 ms > > > > > > postgres=# SELECT count(*) FROM doc, JSON_TABLE(doc.j, '$[*]' > > > > > > COLUMNS (a int PATH '$.a', > > > > > > NESTED PATH '$.b[*] ? (@ < 5)' COLUMNS (v int PATH '$'))) jt; > > > > > > count > > > > > > ------- > > > > > > 4000 > > > > > > (1 row) > > > > > > > > > > > > Time: 582.042 ms > > > > > > > > > > > > This gives an average of 148.059 pre-commit vs.548.035ms > > > > > > post-commit, > > > > > > a 3.7 x slowdown. > > > > > > > > > > > > This seems to grow with the size of the nested arrays., With small > > > > > > ones the is only around 10%. > > > > > > > > > > > > Is this expected? > > > > > > > > > > No, it's no expected. There is problem of multiple evaluation of > > > > > nested paths. 0005 fixes that. > > > > > > > > I've tested all patches, and all reported issues now resolved. The > > > > regression is also completely eliminated. Thanks! > > > > > > > > Is there any use for errdetail("PATH name was %s not found in nested > > > > columns list.") anymore? I don't think we need it, but if for some > > > > reason we do, the wording needs fixing. > > > > > > Yes, no need for this ERROR. This is checked before, replaced with an > > > assert in 0006. > > > > > > > Also, in 0001, the dumping half of the fix doesn't seem to have a > > > > test. There's a new test for the runtime side, and json_table_view9 > > > > covers the redundant NULL ON EMPTY, but nothing would fail if a dumped > > > > view started losing an explicit ERROR ON EMPTY again. Maybe something > > > > like this next to the other view tests: > > > > > > > > CREATE VIEW jsonb_table_view_onempty AS > > > > SELECT * FROM JSON_TABLE(jsonb '{}', '$' AS p0 > > > > COLUMNS (a int PATH '$.nosuch' ERROR ON EMPTY) > > > > ERROR ON ERROR); > > > > \sv jsonb_table_view_onempty > > > > DROP VIEW jsonb_table_view_onempty; > > > > > > > > The \sv output should keep the ERROR ON EMPTY clause. > > > > > > New test is added to 0001. > > > > All issues I raised are resolved by these patches, so all good from my side. > > Also, shouldn't this have been marked as committed in the current CF?
Pushed, thank you. And the patch is marked as committed. ------ Regards, Alexander Korotkov Supabase
