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.paquier@gmail.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
>