BUG #13680: PostgreSQL backend process crashes on jsonb_object_agg() in plpgsql recursive function - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      13680
Logged by:          Boyko
Email address:      b.yordanov2@gmail.com
PostgreSQL version: 9.5beta1
Operating system:   CentOS release 6.4 (Final)
Description:

Hi,

I wrote the following function:

CREATE OR REPLACE FUNCTION public.recursive_stats_daily(d jsonb, i
interval)
 RETURNS TABLE(day_time_col timestamp without time zone, data_col jsonb)
 LANGUAGE plpgsql
AS $function$
begin
    return query select stats.day_time_col,jsonb_object_agg(z,(d->z ||
stats.data_col->z)) as dd 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;
    if day_time_col::time < '23:00:00'::time then
        return query select * from
recursive_stats_daily(dd,i+'1h'::interval);
    end if;
end $function$

The idea is to recursively concat nested json data, which is generated
hourly (24 jsons per day) into single "daily" json object.

Structure of stats:

CREATE TABLE stats (
    id integer NOT NULL,
    day_time_col timestamp without time zone DEFAULT now() NOT NULL,
    at time without time zone DEFAULT '00:00:00'::time without time zone NOT
NULL,
    data_col jsonb DEFAULT '{}'::jsonb NOT NULL
);

Example json data:

=> select * from stats where day_time_col::date = current_date;
-[ RECORD 1

]+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id           | 209
day_time_col | 2015-10-14 00:00:00
at           | 00:00:00
data_col     | {"36": {"2285": {"c": 1}}, "61": {"4023": {"c": 1}}, "71":
{"3572": {"c": 1}}, "143": {"4916": {"c": 1}}, "166": {"1171": {"c": 1}},
"185": {"604": {"c": 1}}, "264": {"6535": {"c": 1}}, "321": {"7817": {"c":
1}}, "383": {"4031": {"c": 1}}, "462": {"8303": {"c": 1}}, "486": {"9795":
{"c": 1}}, "497": {"2745": {"c": 1}}}
-[ RECORD 2

]+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id           | 210
day_time_col | 2015-10-14 01:00:00
at           | 00:00:00
data_col     | {"36": {"1135": {"c": 1}}, "47": {"1364": {"c": 1}}, "54":
{"5920": {"c": 1}, "8402": {"c": 1}}, "71": {"386": {"c": 1}}, "90":
{"7145": {"c": 1}}, "94": {"8625": {"c": 1}}, "168": {"3814": {"c": 1}},
"326": {"722": {"c": 1}}, "339": {"4888": {"c": 1}}, "420": {"6447": {"c":
1}}, "430": {"5701": {"c": 1}}, "460": {"861": {"c": 1}}, "466": {"1722":
{"c": 1}}, "496": {"6945": {"c": 1}}, "498": {"6929": {"c": 1}}}

Calling the function like this causes the crash:

sravni_hstore=> select * from
recursive_stats_daily('{}'::jsonb,'1h'::interval);
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Log entry:

< 2015-10-14 19:01:54.640 EEST >LOG:  statement: select * from
recursive_stats_daily('{}'::jsonb,'1h'::interval);
< 2015-10-14 19:01:54.645 EEST >LOG:  server process (PID 15993) was
terminated by signal 11: Segmentation fault
< 2015-10-14 19:01:54.645 EEST >DETAIL:  Failed process was running: select
* from recursive_stats_daily('{}'::jsonb,'1h'::interval);
< 2015-10-14 19:01:54.645 EEST >LOG:  terminating any other active server
processes
< 2015-10-14 19:01:54.645 EEST >WARNING:  terminating connection because of
crash of another server process
< 2015-10-14 19:01:54.645 EEST >DETAIL:  The postmaster has commanded this
server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
< 2015-10-14 19:01:54.645 EEST >HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
< 2015-10-14 19:01:54.645 EEST >FATAL:  the database system is in recovery
mode
< 2015-10-14 19:01:54.645 EEST >LOG:  all server processes terminated;
reinitializing
< 2015-10-14 19:01:54.654 EEST >LOG:  database system was interrupted; last
known up at 2015-10-14 19:01:12 EEST
< 2015-10-14 19:01:54.671 EEST >LOG:  database system was not properly shut
down; automatic recovery in progress
< 2015-10-14 19:01:54.672 EEST >LOG:  invalid record length at 0/7561438
< 2015-10-14 19:01:54.672 EEST >LOG:  redo is not required
< 2015-10-14 19:01:54.678 EEST >LOG:  MultiXact member wraparound
protections are now enabled
< 2015-10-14 19:01:54.678 EEST >LOG:  database system is ready to accept
connections
< 2015-10-14 19:01:54.678 EEST >LOG:  autovacuum launcher started