BUG #4993: memory issue with array_agg

The following bug has been logged online:

Bug reference: 4993
Logged by: Morus Walter
Email address: morus(dot)walter(dot)ml(at)googlemail(dot)com
PostgreSQL version: 8.4.0
Operating system: linux
Description: memory issue with array_agg
Details:

Hi,

when trying to use the array_agg aggregate I ran into a `out of memory'
issue.

There are two tables
a contains ~ 1 Mio entries, b has 0 to 3 entries for each entry in a (~750k
entries).

There query is
select a.id, array_agg(b.id) from a join b on b.a_id = a.id group by a.id;

Now beeing out of memory isn't necessarily a bug but

* postgres writes a 30 Megabyte error message mostly consisting of lines
accumArrayResult: 8192 total in 1 blocks; 7800 free (0 chunks);
392 used
(~ 380k of these lines)
which seems a bit exaggerated

* I can do the same query using a self defined aggregate as described in
http://valgogtech.blogspot.com/2008/04/postgresql-array-aggregate.html

CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);

without memory issues (~650k rows).

The database config is a out of the box 8.4.0 installation (only change is
max_connections reduced to 32). I know this isn't much memory granted to the
database, OTOH it's just a development installation and I wouldn't want to
use the array aggregate if it eats a lot of memory anyway.

The query plan shows sequential scans on both tables, a hash join, a sort
and a group aggregate. Looks reasonable to me.

The error message says (without the repeated line)
TopMemoryContext: 51930928 total in 6339 blocks; 104088 free (6341 chunks);
51826840 used
Type information cache: 8192 total in 1 blocks; 1800 free (0 chunks); 6392
used
Operator lookup cache: 24576 total in 2 blocks; 14072 free (6 chunks);
10504 used
TopTransactionContext: 8192 total in 1 blocks; 7680 free (0 chunks); 512
used
MessageContext: 65536 total in 4 blocks; 14152 free (9 chunks); 51384
used
Operator class cache: 8192 total in 1 blocks; 3848 free (0 chunks); 4344
used
smgr relation table: 8192 total in 1 blocks; 760 free (0 chunks); 7432
used
TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0 chunks);
16 used
Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used
PortalHeapMemory: 1024 total in 1 blocks; 920 free (0 chunks); 104 used
ExecutorState: 24576 total in 2 blocks; 13208 free (136 chunks); 11368
used
HashTableContext: 8192 total in 1 blocks; 7904 free (1 chunks); 288
used
HashBatchContext: 2129944 total in 10 blocks; 704368 free (5
chunks); 1425576 used
TupleSort: 1368372 total in 16 blocks; 794184 free (11708 chunks);
574188 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
AggContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
accumArrayResult: 0 total in 0 blocks; 0 free (0 chunks); 0 used
accumArrayResult: 8192 total in 1 blocks; 7800 free (0 chunks);
392 used
...
accumArrayResult: 8192 total in 1 blocks; 7800 free (0 chunks);
392 used
ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
Relcache by OID: 8192 total in 1 blocks; 2856 free (0 chunks); 5336 used
CacheMemoryContext: 667696 total in 20 blocks; 153616 free (1 chunks);
514080 used
job_desires_account_id: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
job_desires_pkey: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
accounts_reindex: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
accounts_lastlogin: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
accounts_email_lower_unique: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
accounts_created_at: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
accounts_pkey: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
pg_index_indrelid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_user_mapping_user_server_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_user_mapping_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
pg_type_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 240 free (0 chunks);
784 used
pg_ts_template_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
pg_ts_template_tmplname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_ts_parser_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_ts_parser_prsname_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
pg_ts_dict_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_ts_dict_dictname_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
pg_ts_config_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_ts_config_cfgname_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
pg_ts_config_map_index: 1024 total in 1 blocks; 192 free (0 chunks); 832
used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 240 free (0 chunks);
784 used
pg_proc_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
pg_opfamily_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_opfamily_am_name_nsp_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 88 free (0
chunks); 936 used
pg_namespace_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_namespace_nspname_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_language_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_language_name_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_foreign_server_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_foreign_server_name_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_foreign_data_wrapper_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_foreign_data_wrapper_name_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_enum_typid_label_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
pg_enum_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_database_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_conversion_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
pg_constraint_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_conversion_default_index: 1024 total in 1 blocks; 128 free (0
chunks); 896 used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
pg_cast_source_target_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_authid_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_authid_rolname_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_amop_opr_fam_index: 1024 total in 1 blocks; 240 free (0 chunks); 784
used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_operator_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_amproc_fam_proc_index: 1024 total in 1 blocks; 88 free (0 chunks);
936 used
pg_amop_fam_strat_index: 1024 total in 1 blocks; 88 free (0 chunks); 936
used
pg_opclass_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_index_indexrelid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_class_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
MdSmgr: 8192 total in 1 blocks; 6976 free (0 chunks); 1216 used
LOCALLOCK hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
Timezones: 78520 total in 2 blocks; 5968 free (0 chunks); 72552 used
ErrorContext: 8192 total in 1 blocks; 8176 free (3 chunks); 16 used
ERROR: out of memory
DETAIL: Failed on request of size 28.

If you have an questions, feel free to ask.

Morus