Hi,
I'm trying to run the following query with PG 9.3.1 (also tested with 9.3.2,
same issue)
select fk_header_id, (json_array_elements(data)->>'lines')::int as lines,
(json_array_elements(data)->>'size')::int as size,
(json_array_elements(data)->>'dt_created')::timestamp with time zone as
dt_created into z_stats_base from z;
z is 5.5M lines, z_stats_base will be 260M lines
after some hours and a constant increase of memory the query failed with
this in logs:
2013-12-06 21:01:04 CETLOG: server process (PID 15728) was terminated by
signal 9: Killed
2013-12-06 21:01:04 CETDETAIL: Failed process was running: <my query>
2013-12-06 21:01:04 CETLOG: terminating any other active server processes
2013-12-06 21:01:04 CETWARNING: terminating connection because of crash of
another server process
2013-12-06 21:01:04 CETDETAIL: 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.
2013-12-06 21:01:04 CETHINT: In a moment you should be able to reconnect to
the database and repeat your command.
2013-12-06 21:01:04 CETWARNING: terminating connection because of crash of
another server process
2013-12-06 21:01:04 CETDETAIL: 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.
2013-12-06 21:01:04 CETHINT: In a moment you should be able to reconnect to
the database and repeat your command.
2013-12-06 21:01:04 CETFATAL: the database system is in recovery mode
2013-12-06 21:01:04 CETLOG: all server processes terminated; reinitializing
2013-12-06 21:01:05 CETLOG: database system was interrupted; last known up
at 2013-12-06 18:04:57 CET
2013-12-06 21:01:05 CETLOG: database system was not properly shut down;
automatic recovery in progress
2013-12-06 21:01:06 CETLOG: record with zero length at 8E/E844E0B8
2013-12-06 21:01:06 CETLOG: redo is not required
2013-12-06 21:01:06 CETLOG: checkpoint starting: end-of-recovery immediate
2013-12-06 21:01:06 CETLOG: checkpoint complete: wrote 0 buffers (0.0%); 0
transaction log file(s) added, 0 removed, 0 recycled; write=0.005 s,
sync=0.000 s, total=0.197 s; sync files=0, longest=0.000 s, average=0.000 s
2013-12-06 21:01:06 CETLOG: database system is ready to accept connections
2013-12-06 21:01:06 CETLOG: autovacuum launcher started
=> bug ?
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/bug-with-json-array-elements-on-big-table-tp5782353.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
