From: | Diway <diway(at)diway(dot)net> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | bug with json_array_elements on big table ? |
Date: | 2013-12-08 17:29:10 |
Message-ID: | 1386523750358-5782353.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2013-12-08 18:17:38 | Re: PQclientEncoding() returns -1, resulting in possible assertion failure in psql |
Previous Message | Peter Geoghegan | 2013-12-08 09:21:46 | PQclientEncoding() returns -1, resulting in possible assertion failure in psql |