Re: jsonb and nested hstore - Mailing list pgsql-hackers

On Mon, Mar 3, 2014 at 6:54 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
> My aim for 9.4, given constraints of both the development cycle and my time
> budget, has been to get jsonb to a point where it has equivalent
> functionality to json, so that nobody is forced to say "well I'll have to
> use json because it lacks function x." For the processing functions, i.e.
> those that don't generate json from non-json, this should be true with
> what's proposed. The jsonb processing functions should be about as fast as,
> or in some cases significantly faster than, their json equivalents. Parsing
> text input takes a little longer (surprisingly little, actually), and
> reserializing takes significantly longer - I haven't had a chance to look
> and see if we can improve that. Both of these are more or less expected
> results.

Okay, that's fine. I'm sure that jsonb has some value without
hstore-style indexing. That isn't really in question. What is in
question is why you would choose to give up on those capabilities.

> For 9.5 I would hope that we have at least the equivalent of the proposed
> hstore classes.

But the equivalent code to the proposed hstore operator classes is
*exactly the same* C code. The two types are fully binary coercible in
the patch, so why delay? Why is that additional step appreciably
riskier than adopting jsonb? I don't see why the functions associated
with the operators that comprise, say, the gin_hstore_ops operator
class represent much additional risk, assuming that jsonb is itself in
good shape. For example, the new hstore_contains() looks fairly
innocuous compared to much of the code you are apparently intent on
including in the first cut at jsonb. Have I missed something? Why are
those operators riskier than the operators you are intent on
including?

If it is true that you think that's a significant additional risk, a
risk too far, then it makes sense that you'd defer doing this. I would
like to know why that is, though, since I don't see it. Speaking of
missing operator classes, I'm pretty sure that it's ipso facto
unacceptable that there is no default btree operator class for the
type jsonb:

[local]/postgres=# \d+ bar                        Table "public.bar"Column | Type  | Modifiers | Storage  | Stats
target| Description
 
--------+-------+-----------+----------+--------------+-------------i      | jsonb |           | extended |
|
 
Has OIDs: no

[local]/postgres=# select * from bar order by i;
ERROR:  42883: could not identify an ordering operator for type jsonb
LINE 1: select * from bar order by i;                                  ^
HINT:  Use an explicit ordering operator or modify the query.
LOCATION:  get_sort_group_operators, parse_oper.c:221
Time: 6.424 ms
[local]/postgres=# select distinct i from bar;
ERROR:  42883: could not identify an equality operator for type jsonb
LINE 1: select distinct i from bar;                       ^
LOCATION:  get_sort_group_operators, parse_oper.c:226
Time: 6.457 ms

> But that's really just a start. Frankly, I think we need to
> think a lot harder about how we want to be able to index this sort of data.
> The proposed hstore operators appear to me to be at best just scratching the
> surface of that. I'd like to be able to index jsonb's #> and #>> operators,
> for example. Unanchored subpath operators could be an area that's
> interesting to implement and index.

I'm sure that's true, but it's not our immediate concern. We need to
think very hard about it to get everything we want, but we also need
to think somewhat harder about it in order to get even a basic jsonb
type committed.

-- 
Peter Geoghegan