BUG #4993: memory issue with array_agg - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      4993
Logged by:          Morus Walter
Email address:      morus.walter.ml@googlemail.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