Horton

Re: [HACKERS] [PATCH] Generic type subscripting

Dmitry Dolgov <9erthalion6@gmail.com>

View thread View on postgr.es
From: Dmitry Dolgov <9erthalion6@gmail.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Oleksandr Shulgin <oleksandr.shulgin@zalando.de>, Artur Zakirov <a.zakirov@postgrespro.ru>, Tom Lane <tgl@sss.pgh.pa.us>, Robert Haas <robertmhaas@gmail.com>, Andres Freund <andres@anarazel.de>, Michael Paquier <michael.paquier@gmail.com>, Oleg Bartunov <obartunov@gmail.com>, Peter Eisentraut <peter.eisentraut@2ndquadrant.com>, David Steele <david@pgmasters.net>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2018-10-11T20:49:04Z
Lists: pgsql-hackers 
> On Wed, 10 Oct 2018 at 14:26, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
> I am playing with this feature little bit

Thanks a lot!

> I have one idea - can be possible to use integer subscript for record fields? It can helps with iteration over record.
>
> example:
>
> select ('{"a":{"a":[10,20]}}'::jsonb)[0];--> NULL, but can be more practical if it returns same like select ('{"a":{"a":[10,"20"]}}'::jsonb)['a'];

Sounds interesting, but I'm not sure how consistent it would be with the rest
of jsonb functionality, and someone may want to get an error in this case. At
the same time I believe that this can be achieved quite nicely with json_query
or json_table from SQL/JSON patch (see examples here [1]). What do you think
about this approach?

> I don't like quite ignoring bad subsript in update

Can you show an example of such ignoring of a bad subsript in an update?

> postgres=# insert into test(v) values( '[]');
> INSERT 0 1
> postgres=# update test set v[1000] = 'a';
> UPDATE 1
> postgres=# update test set v[1000] = 'a';
> UPDATE 1
> postgres=# update test set v[1000] = 'a';
> UPDATE 1
> postgres=# select * from test;
> ┌────┬─────────────────┐
> │ id │        v        │
> ╞════╪═════════════════╡
> │    │ ["a", "a", "a"] │
> └────┴─────────────────┘
> (1 row)
>
> It should to raise exception in this case. Current behave allows append simply, but can be source of errors. For this case we can introduce some special symbol - some like -0 :)

Yeah, it may look strange, but there is a reason behind it. I tried to keep the
behaviour of this feature consistent with jsonb_set function (and in fact
they're sharing the same functionality). And for jsonb_set it's documented:

    If the item (of a path, in our case an index) is out of the range
    -array_length .. array_length -1, and create_missing is true, the new value
    is added at the beginning of the array if the item is negative, and at the
    end of the array if it is positive.

So, the index 1000 is way above the end of the array v, and every new item has
being appended at the end.

Of course no one said that they should behave similarly, but I believe it's
quite nice to have consistency here. Any other opinions?

> It is maybe strange, but I prefer less magic syntax like
>
> update test set v['a']['a'] =  v['a']['a'] || '1000';
>
> more readable than
>
> update test set v['a']['a'][1000000] = 1000;

Yep, with this patch it's possible to use both ways:

    =# table test;
    v
    -------------------------
     {"a": {"a": [1, 2, 3]}}
    (1 row)

    =# update test set v['a']['a'] = v['a']['a'] || '1000';
    UPDATE 1

    =# table test;
       v
    -------------------------------
     {"a": {"a": [1, 2, 3, 1000]}}
    (1 row)

> My first impression is very good - update jsonb, xml documents can be very friendly.

Thanks!

1: https://www.postgresql.org/message-id/flat/732208d3-56c3-25a4-8f08-3be1d54ad51b@postgrespro.ru

Commits

  1. Throw error when assigning jsonb scalar instead of a composite object

  2. Filling array gaps during jsonb subscripting

  3. Implementation of subscripting for jsonb

  4. Allow ALTER TYPE to update an existing type's typsubscript value.

  5. Allow subscripting of hstore values.

  6. Support subscripting of arbitrary types, not only arrays.

  7. jit: Reference function pointer types via llvmjit_types.c.

  8. Teach contain_leaked_vars that assignment SubscriptingRefs are leaky.

  9. jit: Correct parameter type for generated expression evaluation functions.

  10. Renaming for new subscripting mechanism

  11. Fix assertion failure for SSL connections.

  12. Teach eval_const_expressions() to handle some more cases.