Re: [HACKERS] [PATCH] Generic type subscripting

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Dmitry Dolgov <9erthalion6(at)gmail(dot)com>
Cc: Oleksandr Shulgin <oleksandr(dot)shulgin(at)zalando(dot)de>, Artur Zakirov <a(dot)zakirov(at)postgrespro(dot)ru>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Robert Haas <robertmhaas(at)gmail(dot)com>, Andres Freund <andres(at)anarazel(dot)de>, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Oleg Bartunov <obartunov(at)gmail(dot)com>, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, David Steele <david(at)pgmasters(dot)net>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] [PATCH] Generic type subscripting
Date: 2018-10-12 05:51:50
Message-ID: CAFj8pRBjVzqrz1vsbzN7WeQ_tbO881GbaTUiMyZaSjk0xo6GBg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

čt 11. 10. 2018 v 22:48 odesílatel Dmitry Dolgov <9erthalion6(at)gmail(dot)com>
napsal:

> > On Wed, 10 Oct 2018 at 14:26, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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?
>

In this case, I don't see any problem - the array or multidimensional array
can be indexed by numbers or by special keys. But numbers are natural every
time.

For me, SQL/JSON, JSONPath support is different topic. More - the generic
support can be used for other types than Jsonb. I can imagine integrated
dictionary type - and the SQL/JSON support doesn't help here.

This is not too strong theme for me - just I don't see a reason for strong
restrictivity there.

> > 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?
>

Aha - although I understand to your motivation, I am think so it is bad
design - and jsonb_set behave is not happy.

I am think so it is wrong idea, because you lost some information - field
position - I push value on index 10, but it will be stored on second
position.

Regards

Pavel

> > 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(at)postgrespro(dot)ru
>

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2018-10-12 06:05:43 Re: pgsql: Add TAP tests for pg_verify_checksums
Previous Message Andres Freund 2018-10-12 04:36:24 Re: Performance improvements for src/port/snprintf.c