Re: making the backend's json parser work in frontend code

> On Jan 22, 2020, at 7:00 PM, Mark Dilger <mark(dot)dilger(at)enterprisedb(dot)com> wrote:
>
> I have this done in my local repo to the point that I can build frontend tools against the json parser that is now in src/common and also run all the check-world tests without failure. I’m planning to post my work soon, possibly tonight if I don’t run out of time, but more likely tomorrow.

Ok, I finished merging with Robert’s patches. The attached follow his numbering, with my patches intended to by applied after his.

I tried not to change his work too much, but I did a bit of refactoring in 0010, as explained in the commit comment.

0011 is just for verifying the linking works ok and the json parser can be invoked from a frontend tool without error — I don’t really see the point in committing it.

I ran some benchmarks for json parsing in the backend both before and after these patches, with very slight changes in runtime. The setup for the benchmark creates an unlogged table with a single text column and loads rows of json formatted text:

CREATE UNLOGGED TABLE benchmark (
j text
);
COPY benchmark (j) FROM '/Users/mark.dilger/bench/json.csv’;

FYI:

wc ~/bench/json.csv
107 34465023 503364244 /Users/mark.dilger/bench/json.csv

The benchmark itself casts the text column to jsonb, as follows:

SELECT jsonb_typeof(j::jsonb) typ, COUNT(*) FROM benchmark GROUP BY typ;

In summary, the times are:

pristine patched
————— —————
11.985 12.237
12.200 11.992
11.691 11.896
11.847 11.833
11.722 11.936

The full output for the runtimes without the patch over five iterations:

CREATE TABLE
COPY 107
typ | count
--------+-------
object | 107
(1 row)

real 0m11.985s
user 0m0.002s
sys 0m0.003s
typ | count
--------+-------
object | 107
(1 row)

real 0m12.200s
user 0m0.002s
sys 0m0.004s
typ | count
--------+-------
object | 107
(1 row)

real 0m11.691s
user 0m0.002s
sys 0m0.003s
typ | count
--------+-------
object | 107
(1 row)

real 0m11.847s
user 0m0.002s
sys 0m0.004s
typ | count
--------+-------
object | 107
(1 row)

real 0m11.722s
user 0m0.002s
sys 0m0.003s

An with the patch, also five iterations:

CREATE TABLE
COPY 107
typ | count
--------+-------
object | 107
(1 row)

real 0m12.237s
user 0m0.002s
sys 0m0.004s
typ | count
--------+-------
object | 107
(1 row)

real 0m11.992s
user 0m0.002s
sys 0m0.004s
typ | count
--------+-------
object | 107
(1 row)

real 0m11.896s
user 0m0.002s
sys 0m0.004s
typ | count
--------+-------
object | 107
(1 row)

real 0m11.833s
user 0m0.002s
sys 0m0.004s
typ | count
--------+-------
object | 107
(1 row)

real 0m11.936s
user 0m0.002s
sys 0m0.004s

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company