Re: BUG #13680: PostgreSQL backend process crashes on jsonb_object_agg() in plpgsql recursive function

Applied the patch and the crash is gone, looks good, was able to debug my
function and it now works as expected, posting it for the record:

create or replace function recursive_stats_daily (d jsonb, i interval)
returns table (daily_json_for date, data_col jsonb)
language plpgsql
as $$
begin
if i::time < '23:00:00'::time then
return query
select * from recursive_stats_daily(
(select d || jsonb_object_agg(z,(coalesce(d->z,'{}'::jsonb)::jsonb
|| (stats.data_col->z)::jsonb)) from
stats,jsonb_object_keys(stats.data_col) z where stats.day_time_col::date =
current_date and stats.day_time_col::time = '00:00:00'::time + i group by
stats.day_time_col),
(i+'1h'::interval)
);
else
return query select current_date, d;
end if;
end $$;

As a side note - I notice this weird behavior of the concat operator -
below queries show what I mean:

sravni_hstore=# select ('{"3": {"9703": {"c": 1}}}'::jsonb->'3');
-[ RECORD 1 ]----------------
?column? | {"9703": {"c": 1}}

sravni_hstore=# select '{"3": {"9703": {"c": 1}}}'::jsonb->'3';
-[ RECORD 1 ]----------------
?column? | {"9703": {"c": 1}}

sravni_hstore=# select ('{"3": {"8309": {"c": 1}}}'::jsonb->'3');
-[ RECORD 1 ]----------------
?column? | {"8309": {"c": 1}}

sravni_hstore=# select '{"3": {"8309": {"c": 1}}}'::jsonb->'3';
-[ RECORD 1 ]----------------
?column? | {"8309": {"c": 1}}

sravni_hstore=# select ('{"3": {"9703": {"c": 1}}}'::jsonb->'3') || ('{"3":
{"8309": {"c": 1}}}'::jsonb->'3');
-[ RECORD 1 ]----------------------------------
?column? | {"8309": {"c": 1}, "9703": {"c": 1}}

sravni_hstore=# select '{"3": {"9703": {"c": 1}}}'::jsonb->'3' || '{"3":
{"8309": {"c": 1}}}'::jsonb->'3';
-[ RECORD 1 ]----------------
?column? | {"8309": {"c": 1}}

What I expect is that there should be no difference in the output of the
last two queries. Not sure if I should submit this as a separate bug?

Thanks for the effort! :)
Boyko

2015-10-15 8:41 GMT+03:00 Michael Paquier <michael(dot)paquier(at)gmail(dot)com>:

> On Thu, Oct 15, 2015 at 10:44 AM, Michael Paquier wrote:
> > I am looking into it in more details, for now I have added an open item
> for 9.5.
> > Regards,
>
> This simple query reproduces the crash as well:
> =# select json_object_agg(1, NULL::json);
> json_object_agg
> -----------------
> { "1" : null }
> (1 row)
> =# select jsonb_object_agg(1, NULL::jsonb);
> server closed the connection unexpectedly
>
> It happens that jsonb_object_agg_transfn is not able to manage
> correctly NULL values in the context of a JSONB value, and it seems to
> me that this is caused by an oversight in datum_to_jsonb regarding the
> handling of NULL values.
>
> Attached is a patch with some regression tests for master and
> REL9_5_STABLE where the bug has been introduced.
> Thoughts?
> --
> Michael
>