Re: SQL/JSON revisited

From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: Alexander Lakhin <exclusion(at)gmail(dot)com>
Cc: Erik Rijkers <er(at)xs4all(dot)nl>, Andrew Dunstan <andrew(at)dunslane(dot)net>, Amit Langote <amitlangote09(at)gmail(dot)com>, Peter Eisentraut <peter(dot)eisentraut(at)enterprisedb(dot)com>, Andres Freund <andres(at)anarazel(dot)de>, vignesh C <vignesh21(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, e(dot)indrupskaya(at)postgrespro(dot)ru
Subject: Re: SQL/JSON revisited
Date: 2023-04-03 17:16:47
Message-ID: 20230403171647.5xonog3myjdgnad7@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 2023-Mar-29, Alvaro Herrera wrote:

> In the meantime, here's the next two patches of the series: IS JSON and
> the "query" functions. I think this is as much as I can get done for
> this release, so the last two pieces of functionality would have to wait
> for 17. I still need to clean these up some more. These are not
> thoroughly tested either; 0001 compiles and passes regression tests, but
> I didn't verify 0003 other than there being no Git conflicts and bison
> doesn't complain.
> Also, notable here is that I realized that I need to backtrack on my
> change of the WITHOUT_LA: the original patch had it for TIME (in WITHOUT
> TIME ZONE), and I changed to be for UNIQUE. But now that I've done
> "JSON query functions" I realize that it needed to be the other way for
> the WITHOUT ARRAY WRAPPER clause too. So 0002 reverts that choice.

So I pushed 0001 on Friday, and here are 0002 (which I intend to push
shortly, since it shouldn't be controversial) and the "JSON query
functions" patch as 0003. After looking at it some more, I think there
are some things that need to be addressed by one of the authors:

- the gram.y solution to the "ON ERROR/ON EMPTY" clauses is quite ugly.
I think we could make that stuff use something similar to
ConstraintAttributeSpec with an accompanying post-processing function.
That would reduce the number of ad-hoc hacks, which seem excessive.

- the changes in formatting.h have no explanation whatsoever. At the
very least, the new function should have a comment in the .c file.
(And why is it at end of file? I bet there's a better location)

- some nasty hacks are being used in the ECPG grammar with no tests at
all. It's easy to add a few lines to the .pgc file I added in prior

- Some functions in jsonfuncs.c have changed from throwing hard errors
into soft ones. I think this deserves more commentary.

- func.sgml: The new functions are documented in a separate table for no
reason that I can see. Needs to be merged into one of the existing
tables. I didn't actually review the docs.

Álvaro Herrera 48°01'N 7°57'E —
"I'm impressed how quickly you are fixing this obscure issue. I came from
MS SQL and it would be hard for me to put into words how much of a better job
you all are doing on [PostgreSQL]."
Steve Midgley,

Attachment Content-Type Size
v15-0001-backtrack-on-making-WITHOUT_LA-be-for-UNIQUE-rat.patch text/x-diff 2.7 KB
v15-0002-SQL-JSON-query-functions.patch text/x-diff 206.8 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2023-04-03 17:37:31 Re: possible proposal plpgsql GET DIAGNOSTICS oid = PG_ROUTINE_OID
Previous Message Tom Lane 2023-04-03 16:43:41 Re: [BUG] pg_stat_statements and extended query protocol