Re: [HACKERS] [PATCH] Generic type subscripting - Mailing list pgsql-hackers

> On Wed, Sep 16, 2020 at 01:52:27PM +0200, Pavel Stehule wrote:
> > > On Tue, Sep 15, 2020 at 08:42:40PM +0200, Pavel Stehule wrote:
> > >
> > > Maybe I found a another issue.
> > >
> > > create table foo(a jsonb);
> > >
> > > postgres=# select * from foo;
> > > ┌───────────────────────────────────────────────────────────────────┐
> > > │                                 a                                 │
> > > ╞═══════════════════════════════════════════════════════════════════╡
> > > │ [0, null, null, null, null, null, null, null, null, null, "ahoj"] │
> > > └───────────────────────────────────────────────────────────────────┘
> > > (1 row)
> > >
> > > It is working like I expect
> > >
> > > but
> > >
> > > postgres=# truncate foo;
> > > TRUNCATE TABLE
> > > postgres=# insert into foo values('[]');
> > > INSERT 0 1
> > > postgres=# update foo set a[10] = 'ahoj';
> > > UPDATE 1
> > > postgres=# select * from foo;
> > > ┌──────────┐
> > > │    a     │
> > > ╞══════════╡
> > > │ ["ahoj"] │
> > > └──────────┘
> > > (1 row)
> >
> > Thanks for looking at the last patch, I appreciate! The situation you've
> > mention is an interesting edge case. If I understand correctly, the
> > first example is the result of some operations leading to filling gaps
> > between 0 and "ahoj". In the second case there is no such gap that's why
> > nothing was "filled in", although one could expect presence of a "start
> > position" and fill with nulls everything from it to the new element, is
> > that what you mean?
> >
>
> I expect any time
>
> a[10] := 10;
>
> ? a[10] --> 10
>
> ===
>
>
> postgres=# truncate foo;
> TRUNCATE TABLE
> postgres=# insert into foo values('[]');
> INSERT 0 1
> postgres=# update foo set a[10] = 'AHOJ';
> UPDATE 1
> postgres=# select (a)[10] from foo;
> ┌───┐
> │ a │
> ╞═══╡
> │ ∅ │
> └───┘
> (1 row)
>
> There should be consistency
>
> postgres=# create table foo2(a text[]);
> CREATE TABLE
> postgres=# insert into foo2 values('{}');
> INSERT 0 1
> postgres=# update foo set a[10] = 'AHOJ';
> UPDATE 1
> postgres=# select (a)[10] from foo;
> ┌────────┐
> │   a    │
> ╞════════╡
> │ "AHOJ" │
> └────────┘
> (1 row)
>
> and some natural behaviour - any special case with different behaviour is a
> bad thing generally.

Yeah, I see your point. IIRC there is no notion of an arbitrary index in
jsonb array, so it needs to be done within an assignment operation
similar to how the last patch fills the gaps between elements. Taking
into account, that if there are more than one elements in the array, all
the gaps should be filled and the behaviour is already the same as you
described, what needs to be changed is more nulls need to be added
around before the first element depending on the assignment index.

I have my concerns about the performance side of this implementation as
well as how surprising this would be for users, but at the same time the
patch already does something similar and the code change should not be
that big, so why not - I can include this change into the next rebased
version. But it still can cause some confusion as it's not going to work
for negative indices, so

    update foo set a[-10] = 1;

and

    select a[-10] from foo;

can return different value from what was assigned. Otherwise, if we will
try to fix a[-10] assignment in the same way, it will prepend the array
and a[10] will not return the same value.