Date: Wed, 26 May 2021 15:50:49 +0200
From: Dmitry Dolgov <9erthalion6@gmail.com>
To: "Poot, Bas (B.J.)" <bas.poot@politie.nl>
Cc: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: XX000: unknown type of jsonb container.
Message-ID: <20210526135049.afseg47jmnqirqge@localhost>
References: <534fca83789c4a378c7de379e9067d4f@politie.nl>
 <20210407181105.lmi7hgifwzs3eeee@localhost>
 <20210413161500.kufk4iyrmcmhhbrc@localhost>
 <20210423151505.lxx5ti7ivcza3xqw@localhost>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20210423151505.lxx5ti7ivcza3xqw@localhost>
Archived-At: 
 <https://www.postgresql.org/message-id/20210526135049.afseg47jmnqirqge%40localhost>
Precedence: bulk

> On Fri, Apr 23, 2021 at 05:15:05PM +0200, Dmitry Dolgov wrote:
> > On Tue, Apr 13, 2021 at 06:15:00PM +0200, Dmitry Dolgov wrote:
> > > On Wed, Apr 07, 2021 at 08:11:05PM +0200, Dmitry Dolgov wrote:
> > > > On Wed, Apr 07, 2021 at 10:59:31AM +0000, Poot, Bas (B.J.) wrote:
> > > >
> > > > Finally! I have a testcase for you guys. This is my query to generate the data.
> > > > select *
> > > > into tmp_bug2
> > > > from (
> > > > select 'thing' as  logical_name
> > > > , 'thing' as display_name
> > > > , 'thing' as operation
> > > > , '{"unit": "Our special Unit", "some.place": "placename", "place_of_birth": "Over the rainbow", "How_secret_is_it": "Level 1", "Does.it.shoot": "YES!"}'::jsonb as filtur
> > > > from generate_series(1, 302443)
> > > > ) t1;
> > > >
> > > > And this is the query that generates the error.
> > > >
> > > > select
> > > > display_name
> > > > ,'' as x
> > > > ,filtur
> > > > ,jsonb_each_text(filtur) as x
> > > > ,to_jsonb(jsonb_each_text(filtur)) as frows
> > > > ,array(SELECT jsonb_object_keys(filtur)) as objectkeys
> > > > from tmp_bug2
> > > > order by logical_name;
> > > >
> > > > Notice that when you generate not 302443 but 302442 rows, it works perfectly fine.
> > >
> > > Thanks for posting the test case, I can reproduce it on the master
> > > branch as well (also without the second call to jsonb_each_text in line
> > > with to_jsonb). Interesting, it looks like for unclear to me reasons the
> > > argument, evaluated in ExecMakeFunctionResultSet for jsonb_each_text,
> > > contains value from the previous attribute, not jsonb. This makes
> > > iteratorFromContainer complain because both array & object flags are set
> > > in the header. I'll try to investigate, unless someone else will be
> > > faster.
> > 
> > Here is what I've found so far:
> > 
> > * It seems that technically the regression was introduced in
> >   ea190ed14b, but not directly, via using gather paths in more
> >   situations.
> > 
> > * The direct problem is that JsonbIteratorInit is confused by the
> >   argument, because it contains both headers for array and object set.
> >   From what I understood this confusion stems from
> >   ExecMakeFunctionResultSet taking wrong value from the slot. Hacking it
> >   to use different attnum in this case makes it work.
> > 
> > * The explanation for this could be that target list from where attnum
> >   is taken and slot values have different order of elements. This in
> >   turn comes out of grouping planner trying to isolate SRF and SRF-free
> >   targets and as a result changing the order of elements in
> >   final_target. The final_target is then passed into
> >   create_ordered_paths and applied via apply_projection_to_path, but
> >   somehow goes in disagreement with what is used while creating the slot
> >   with values.
> > 
> > If this analysis is correct, I'm not sure yet what would be the best
> > course of action to address the problem, need to think a bit more. But
> > probably others have suggestions or comments?
> 
> I couldn't find any other feasible explanations, and have come to a
> conclusion that this happens when a projection is applied to a
> GatherMerge path. As it's a projection capable path, no new projection
> is created and target list is changed in place. In the subpath target
> list ordering is different because of query ordering, and I don't see
> where it all comes together during execution. Funny enough even explain
> shows that the final plan passes a wrong values to jsonb_each_text.
> 
> If I make GatherMerge non projection capable it fixes this particular
> case and changes only one test in select_parallel (seems like a minor
> plan changes). But I have not enough experience with this code to say if
> it's a good or bad idea.

Almost forgot about this one. It seems that the issue still could be
reproduced on the latest master branch, so probably worth an open item.