Memory Issue with array_agg?

From: Robert Sosinski <rsosinski(at)ticketevolution(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Memory Issue with array_agg?
Date: 2013-08-19 03:00:09
Message-ID: CAH1hji3OSyiBs63PKrc0WJ2Z-aUWDWNNgccGVS4BKR+a9sVDaw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

When using array_agg on a large table, memory usage seems to spike up until
Postgres crashes with the following error:

2013-08-17 18:41:02 UTC [2716]: [2] WARNING: terminating connection because
of crash of another server process
2013-08-17 18:41:02 UTC [2716]: [3] 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.
2013-08-17 18:41:02 UTC [2716]: [4] HINT: In a moment you should be able to
reconnect to the database and repeat your command.

I've definitely isolated it down to using array_agg, as when I changed the
query to use string_agg, it worked fine. I also tried using array_agg on a
few different queries, all yielding the same issue. Swapping in string_agg
fixed the issue once more.

This particular table has over 2 million rows and is 1.2 Gigs, and when I
ran the query while viewing htop, the virtual size of the Postgres process
ballooned to 13.9G until crashing.

The version of Postgres I am using is: PostgreSQL 9.2.4 on
x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.7.2-2ubuntu1)
4.7.2, 64-bit

Any help would be much appreciated, thanks!

-Robert

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kevin Grittner 2013-08-19 03:33:29 Re: Select performance variation based on the different combinations of using where lower(), order by, and limit
Previous Message Michael Paquier 2013-08-19 01:21:36 Re: pg_basebackup from new master's slave then recovery from new master.