Re: Tackling JsonPath support

On Fri, Dec 2, 2016 at 4:32 PM, Nico Williams <nico@cryptonector.com> wrote:
> On Fri, Dec 02, 2016 at 08:53:33AM -0500, Robert Haas wrote:
>> On Tue, Nov 29, 2016 at 11:50 AM, Christian Convey
>> <christian.convey@gmail.com> wrote:
>> > I think I can satisfy (3) with a PG extension which provides a function that
>> > approximately implements JSONPath.  My short-term plans are to submit such a
>> > patch.
>>
>> FWIW, I think that's a fine plan.  I don't really know whether
>> JSONPath is the right standard to pick for the task of extracting bits
>
> It's not even a standard.  Are there particular proposals that the ANSI
> SQL working group is considering?

I don't know.

>> of JSON from other bits of JSON, but I think there's some value in
>> picking something is simple enough that we can implement it in our own
>> code and not have to rely on a third-party library.  Of course, if
>> somebody feels like adding a configure option for --with-jq and
>
> Sure.  My main concern is that I don't want to have to parse/format JSON
> around every such call.  I'd rather parsed JSON remain in an internal
> form for as long as possible.

Sure, but that seems like an orthogonal consideration.

> Speaking of which, you could use libjq's jv API and not support the jq
> language itself.

You can submit a patch for that if you like, but I don't think that's
a good reason to block what Christian wants to do, because it's a much
bigger change.  Now if you whip that patch up in a short period of
time and everybody agrees that it gives us everything Christian wanted
to implement and other good stuff too, fine; we can reject Christian's
approach then.  Or if Christian's patch is committed, we can rip it
back out again if and when somebody does this (or any other thing we
all agree is better).  But I object strenuously to the idea that we
should reject the idea of drinking the bottle of beer we have in the
house because there's a liquor store down the road where we can buy an
entire keg.  The possibility of awesome (with enough work) is not a
reason to reject good (via a straightforward approach).

>> appropriate interfaces to integrate with JQ, we could consider that,
>> too, but that imposes a packaging requirement that a home-grown
>> implementation doesn't.  I'd want to hear more than one vote for such
>
> What we do in Heimdal, OpenAFS, and other open source projects, some
> times, is include a copy / git submodule / similar of some such external
> dependencies.  Naturally it's not possible to do this for all external
> dependencies, but it works well enough.  The jv API part of jq is small
> and simple, and could be ripped out into a library that could be
> included in PostgreSQL.

We are typically avoid copying things into our repository because then
we become responsible for pulling in any subsequent fixes.  The few
instances that we have right now (zic, snowball, Harry Spencer's
regexp stuff) have imposed a significant code maintenance burden.  The
git submodules approach might avoid that problem, but it would still
be the case that any compile-breaking bugs in the upstream repository
immediately become compile breaks for all PostgreSQL developers, and
that any critical defects that force emergency releases by the
upstream project now force emergency releases of PostgreSQL as well.
If we merely link against the external project, then we avoid that.
So if we're going to use JQ at all, I think that's how we should do
it.  And again, I'm not trying to prevent you or anybody else from
pursuing that.  All I'm saying is that Christian can do what he wants
to do, too.

> Other projects, like, say, nginx or similar where there is a per-client
> or per-connection memory pool to limit memory footprint, might want
> libjq to get an allocator hook, so that's another enhancement to
> consider.  If that's something that PostgreSQL would need, please let me
> know.

The overall need is that it needs to be possible for PostgreSQL to
throw an ERROR, and thus longjmp, without leaking resources.
Sometimes those errors happen asynchronously due to query cancel or
replication conflicts or similar, and those don't have to be processed
at once but the delay can't be unbounded or more than some modest
fraction of a second.  Integrating with PostgreSQL's memory-context
stuff might make that or other resource leak problems easier, or it
might not.  To really get a clear notion of what would be involved, I
think you'd probably need to produce a prototype patch and submit it
here for review to really find out what issues people see with it.
Library integrations are tricky but, since you wrote JQ and seem
potentially willing to modify it to work with PostgreSQL better, this
one might be a lot less painful than some.  I can't promise we'd
accept an integration even if you came up with a patch, but I see
looking back over this thread that there are several people cautiously
supporting the idea of using JQ in some form, which is promising.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company