Subject: Re: SQL/JSON: JSON_TABLE
To: Erik Rijkers <er@xs4all.nl>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: Andrew Dunstan <andrew@dunslane.net>, David Steele <david@pgmasters.net>,
 Michael Paquier <michael@paquier.xyz>, Justin Pryzby <pryzby@telsasoft.com>,
 Pavel Stehule <pavel.stehule@gmail.com>, Robert Haas
 <robertmhaas@gmail.com>, Dmitry Dolgov <9erthalion6@gmail.com>,
 Oleg Bartunov <obartunov@gmail.com>,
 Andrew Dunstan <andrew.dunstan@2ndquadrant.com>,
 Zhihong Yu <zyu@yugabyte.com>
References: 
 <CAFj8pRAyMkNDJ=aGQFw2eai=FA6tr-asv2-dvUhveq=Ek5Vk8w@mail.gmail.com>
 <CAFj8pRCCyACYVA4DHCNVTQteRrWmE_BiPGKdWJfiN3i0jj0TjQ@mail.gmail.com>
 <df16e6d8-4de6-f228-5109-60aefed5d180@postgrespro.ru>
 <CAFj8pRBaSA06w9WzG52Qu7r10MT2Qxd9Pzg0bbiZCCS_QKhgkg@mail.gmail.com>
 <CAFj8pRC8MU2VxY=zYMnG1haM=pfvrRMYJfpt+-C2ACG1L4QdOQ@mail.gmail.com>
 <bfcb411e-8dab-1fae-ed22-2aebaf584a5f@postgrespro.ru>
 <20200705171558.GP4107@telsasoft.com> <20200803075558.GQ3317@paquier.xyz>
 <7e2cb85d-24cf-4abb-30a5-1a33715959bd@postgrespro.ru>
 <CALNJ-vTXxYOy9baMhFRQH9S5KUkuy8UfO4EpjQv77H7ymcN6nA@mail.gmail.com>
 <cf448269-f62f-b0ac-14d0-1972ac3a8c3a@postgrespro.ru>
 <775a7d16-481c-978f-a6cf-29604a38102b@pgmasters.net>
 <dacf209e-b098-41bf-2d1c-4cf6e64ad511@dunslane.net>
 <1238462749.310979.1616791680679@webmailclassic.xs4all.nl>
 <f51e6af8-b607-dae4-44e8-54a89de009bd@dunslane.net>
 <2d8dcd2f-fe6e-bf31-a21a-90a843edfafc@postgrespro.ru>
 <2101814418.20240.1617123418368@webmailclassic.xs4all.nl>
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
Organization: Postgres Professional
Message-ID: <69eefc5a-cabc-8dd3-c689-93da038c0d6a@postgrespro.ru>
Date: Tue, 30 Mar 2021 23:25:10 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
In-Reply-To: <2101814418.20240.1617123418368@webmailclassic.xs4all.nl>
Content-Type: multipart/alternative;
 boundary="------------CD88F8DBFF385810F3147BFC"
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/69eefc5a-cabc-8dd3-c689-93da038c0d6a%40postgrespro.ru>
Precedence: bulk

This is a multi-part message in MIME format.
--------------CD88F8DBFF385810F3147BFC
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

On 30.03.2021 19:56, Erik Rijkers wrote:

>> On 2021.03.27. 02:12 Nikita Glukhov <n.gluhov@postgrespro.ru> wrote:
>>
>> Attached 47th version of the patches.
> Hi,
>
> Apply, build all fine.  It also works quite well, and according to specification, as far as I can tell.
>
> But today I ran into:
>
> ERROR:  function ExecEvalJson not in llvmjit_types.c
>
> I think that it is caused by:
>
> set enable_bitmapscan = off;
>
> (I installed llvm a few days ago. llvm-3.9-dev on this debian stretch).
>
>
> This is the test sql I concocted, which runs fine with enable_bitmapscan on (the default):
>
> select jt1.*
> from myjsonfile100k as t(js, id)
>    , json_table(
>        t.js
>     , '$' columns (
>          "lastname"   text    path  '$. "lastname"     '
>        , "firstname"  text    path  '$. "firstname"    '
>        , "date"       text    path  '$. "date"         '
>        , "city"       text    path  '$. "city"         '
>        , "country"    text    path  '$. "country"      '
>        , "name 0(1)"  text    path  '$. "array"[0]     '
>        , "name 4(5)"  text    path  '$. "array"[4]     '
>        , "names"      text[]  path  '$. "array"        '
>        , "randfloat"  float   path  '$. "random float" '
>      )
> ) as jt1
> where  js @> ('[ { "city": "Santiago de Cuba" } ]')
>     and js[0]->>'firstname' = 'Gilda'
> ;
> ERROR:  function ExecEvalJson not in llvmjit_types.c
>
> That statement only errors out if the table is large enough. I have no time now to make a sample table but if no-one understands the problem off-hand, I'll try to construct such a table later this week (the one I'm using is large, 1.5 GB).

Thank you for testing.


I think you can try to add 3 missing functions references to the end of
src/backend/jit/llvm/llvmjit_types.c:

  void       *referenced_functions[] =
  {
      ...
      ExecEvalXmlExpr,
+    ExecEvalJsonConstructor,
+    ExecEvalIsJsonPredicate,
+    ExecEvalJson,
      MakeExpandedObjectReadOnlyInternal,
      ...
  };


If this fixes problem, I will add this to the new version of the patches.


-- 
Nikita Glukhov
Postgres Professional:http://www.postgrespro.co   <http://www.postgrespro.com>The Russian Postgres Company


--------------CD88F8DBFF385810F3147BFC
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>On 30.03.2021 19:56, Erik Rijkers wrote:<br>
    </p>
    <blockquote type="cite"
      cite="mid:2101814418.20240.1617123418368@webmailclassic.xs4all.nl">
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">On 2021.03.27. 02:12 Nikita Glukhov <a class="moz-txt-link-rfc2396E" href="mailto:n.gluhov@postgrespro.ru">&lt;n.gluhov@postgrespro.ru&gt;</a> wrote:

Attached 47th version of the patches.
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
Hi,

Apply, build all fine.  It also works quite well, and according to specification, as far as I can tell.

But today I ran into:

ERROR:  function ExecEvalJson not in llvmjit_types.c

I think that it is caused by:

set enable_bitmapscan = off;

(I installed llvm a few days ago. llvm-3.9-dev on this debian stretch).


This is the test sql I concocted, which runs fine with enable_bitmapscan on (the default):

select jt1.* 
from myjsonfile100k as t(js, id) 
  , json_table(
      t.js
   , '$' columns (
        "lastname"   text    path  '$. "lastname"     '
      , "firstname"  text    path  '$. "firstname"    '
      , "date"       text    path  '$. "date"         '
      , "city"       text    path  '$. "city"         '
      , "country"    text    path  '$. "country"      '
      , "name 0(1)"  text    path  '$. "array"[0]     '
      , "name 4(5)"  text    path  '$. "array"[4]     '
      , "names"      text[]  path  '$. "array"        '
      , "randfloat"  float   path  '$. "random float" '
    )
) as jt1
where  js @&gt; ('[ { "city": "Santiago de Cuba" } ]')
   and js[0]-&gt;&gt;'firstname' = 'Gilda'
;
ERROR:  function ExecEvalJson not in llvmjit_types.c

That statement only errors out if the table is large enough. I have no time now to make a sample table but if no-one understands the problem off-hand, I'll try to construct such a table later this week (the one I'm using is large, 1.5 GB).
</pre>
    </blockquote>
    <pre>
Thank you for testing. 


I think you can try to add 3 missing functions references to the end of 
src/backend/jit/llvm/llvmjit_types.c:</pre>
    <pre class="moz-quote-pre" wrap=""> void       *referenced_functions[] =
 {
     ...
     ExecEvalXmlExpr,
+    ExecEvalJsonConstructor,
+    ExecEvalIsJsonPredicate,
+    ExecEvalJson,
     MakeExpandedObjectReadOnlyInternal,
     ...
 };


If this fixes problem, I will add this to the new version of the patches.


-- 
Nikita Glukhov
Postgres Professional: <a href="http://www.postgrespro.com">http://www.postgrespro.co
</a>The Russian Postgres Company
</pre>
  </body>
</html>

--------------CD88F8DBFF385810F3147BFC--