Out of memory error - Mailing list pgsql-general

Hello,

In a database of one of our customers we sometimes get out of memory errors. Below I have copy pasted one of these very long messages.
The error doesn't always occur, when I copy paste the query and run it manually it works.

The current server is an OpenSUSE 12.2 with postgresql 9.2.1 (we also had it with OpenSUSE 11.3 and 9.0 so we moved the DB to the knew server in the hope that would solve it).
It has 8GB of RAM

Memory parameters are:
shared_buffers = 4GB
temp_buffers = 32MB
work_mem = 32MB
maintenance_work_mem = 256MB

Checked the memory usage in the OS and it is fine (0 KiB in swap)

Any suggestions what we can do about this?

TopMemoryContext: 149952 total in 17 blocks; 8568 free (8 chunks); 141384 used
  TopTransactionContext: 8192 total in 1 blocks; 7392 free (1 chunks); 800 used
  Type information cache: 24240 total in 2 blocks; 3744 free (0 chunks); 20496 used
  Operator lookup cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used
  TableSpace cache: 8192 total in 1 blocks; 3216 free (0 chunks); 4976 used
  MessageContext: 1048576 total in 8 blocks; 526360 free (7 chunks); 522216 used
  Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
  smgr relation table: 24576 total in 2 blocks; 5696 free (4 chunks); 18880 used
  TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks); 32 used
  Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
  PortalMemory: 8192 total in 1 blocks; 7888 free (0 chunks); 304 used
    PortalHeapMemory: 1024 total in 1 blocks; 824 free (0 chunks); 200 used
      ExecutorState: 189424 total in 11 blocks; 6848 free (5 chunks); 182576 used
        HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
          HashBatchContext: 19128368 total in 13 blocks; 747952 free (5 chunks); 18380416 used
        TupleSort: 32816 total in 2 blocks; 7584 free (0 chunks); 25232 used
        TupleSort: 32816 total in 2 blocks; 5408 free (7 chunks); 27408 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
        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
        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
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
        AggContext: 8192 total in 1 blocks; 8016 free (0 chunks); 176 used
          TupleHashTable: 8192 total in 1 blocks; 3744 free (0 chunks); 4448 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
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
        ExprContext: 8192 total in 1 blocks; 8088 free (3 chunks); 104 used
        ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used
        AggContext: 122880 total in 4 blocks; 32 free (0 chunks); 122848 used
          TupleHashTable: 516096 total in 6 blocks; 179184 free (20 chunks); 336912 used
       ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
        ExprContext: 8192 total in 1 blocks; 8016 free (3 chunks); 176 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
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
        AggContext: 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
        Unique: 0 total in 0 blocks; 0 free (0 chunks); 0 used
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
  Relcache by OID: 24576 total in 2 blocks; 12832 free (3 chunks); 11744 used
  CacheMemoryContext: 1342128 total in 21 blocks; 201888 free (1 chunks); 1140240 used
    voorraad_filiaal_idx: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
    voorraad_artikel_nr_idx: 2048 total in 1 blocks; 664 free (0 chunks); 1384 used
    voorraad_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
    voorraad_filiaal_id_key: 2048 total in 1 blocks; 576 free (0 chunks); 1472 used
    pg_toast_2619_index: 2048 total in 1 blocks; 528 free (0 chunks); 1520 used
    idx_artikel_uitvoering_barcode: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
    idx_artikel_uitvoering_artikel_nr: 2048 total in 1 blocks; 664 free (0 chunks); 1384 used
    artikel_uitvoering_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
    artikel_ts_idx: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
    artikel_productnr_idx: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
    artikel_omschrijving_idx: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
    artikel_pkey: 2048 total in 1 blocks; 664 free (0 chunks); 1384 used
    webshopcategoriefilter_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
    webshopartikelgroep_meta_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
    pg_toast_20705_index: 2048 total in 1 blocks; 528 free (0 chunks); 1520 used
    webshoppagina_taal_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
    pg_toast_20696_index: 2048 total in 1 blocks; 528 free (0 chunks); 1520 used
    webshoppagina_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
    webshopkorting_gevolg_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
    webshopkorting_conditie_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
    webshopkorting_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
    webshopartikelafbeelding_artikel_nr_idx: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
    webshopartikelafbeelding_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
    artikelgroep_order_idx: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
    artikelgroep_pkey: 2048 total in 1 blocks; 664 free (0 chunks); 1384 used
    artikelgroep_artikel_artikelgroep_nr_idx: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
    artikelgroep_artikel_artikel_nr_idx: 2048 total in 1 blocks; 664 free (0 chunks); 1384 used
    artikelgroep_artikel_pkey: 2048 total in 1 blocks; 528 free (0 chunks); 1520 used
    webshopland_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
    webshoptaal_pkey: 2048 total in 1 blocks; 664 free (0 chunks); 1384 used
    pg_attrdef_adrelid_adnum_index: 2048 total in 1 blocks; 528 free (0 chunks); 1520 used
    artikelcat_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
    configkassa_pkey: 2048 total in 1 blocks; 192 free (0 chunks); 1856 used
    webshopsettings_pkey: 2048 total in 1 blocks; 712 free (0 chunks); 1336 used
    pg_index_indrelid_index: 2048 total in 1 blocks; 664 free (0 chunks); 1384 used
    pg_db_role_setting_databaseid_rol_index: 2048 total in 1 blocks; 576 free (0 chunks); 1472 used
    pg_opclass_am_name_nsp_index: 3072 total in 2 blocks; 1224 free (2 chunks); 1848 used
    pg_foreign_data_wrapper_name_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
    pg_enum_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
    pg_class_relname_nsp_index: 3072 total in 2 blocks; 1520 free (1 chunks); 1552 used
    pg_foreign_server_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
    pg_statistic_relid_att_inh_index: 3072 total in 2 blocks; 1224 free (2 chunks); 1848 used
    pg_cast_source_target_index: 3072 total in 2 blocks; 1520 free (1 chunks); 1552 used
    pg_language_name_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
    pg_collation_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
    pg_amop_fam_strat_index: 3072 total in 2 blocks; 1224 free (2 chunks); 1848 used
    pg_index_indexrelid_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
    pg_ts_template_tmplname_index: 3072 total in 2 blocks; 1568 free (2 chunks); 1504 used
    pg_ts_config_map_index: 3072 total in 2 blocks; 1272 free (2 chunks); 1800 used
    pg_opclass_oid_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
    pg_foreign_data_wrapper_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
    pg_ts_dict_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
    pg_conversion_default_index: 3072 total in 2 blocks; 1272 free (2 chunks); 1800 used
    pg_operator_oprname_l_r_n_index: 3072 total in 2 blocks; 1224 free (2 chunks); 1848 used
    pg_trigger_tgrelid_tgname_index: 3072 total in 2 blocks; 1520 free (1 chunks); 1552 used
    pg_enum_typid_label_index: 3072 total in 2 blocks; 1568 free (2 chunks); 1504 used
    pg_ts_config_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
    pg_user_mapping_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
    pg_opfamily_am_name_nsp_index: 3072 total in 2 blocks; 1272 free (2 chunks); 1800 used
    pg_foreign_table_relid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
    pg_type_oid_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
    pg_aggregate_fnoid_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
    pg_constraint_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
    pg_rewrite_rel_rulename_index: 3072 total in 2 blocks; 1568 free (2 chunks); 1504 used
    pg_ts_parser_prsname_index: 3072 total in 2 blocks; 1568 free (2 chunks); 1504 used
    pg_ts_config_cfgname_index: 3072 total in 2 blocks; 1568 free (2 chunks); 1504 used
    pg_ts_parser_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
    pg_operator_oid_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
    pg_namespace_nspname_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
    pg_ts_template_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
    pg_amop_opr_fam_index: 3072 total in 2 blocks; 1224 free (2 chunks); 1848 used
    pg_default_acl_role_nsp_obj_index: 3072 total in 2 blocks; 1272 free (2 chunks); 1800 used
    pg_collation_name_enc_nsp_index: 3072 total in 2 blocks; 1272 free (2 chunks); 1800 used
    pg_range_rngtypid_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
    pg_ts_dict_dictname_index: 3072 total in 2 blocks; 1568 free (2 chunks); 1504 used
    pg_type_typname_nsp_index: 3072 total in 2 blocks; 1520 free (1 chunks); 1552 used
    pg_opfamily_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
    pg_class_oid_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
    pg_proc_proname_args_nsp_index: 3072 total in 2 blocks; 1224 free (2 chunks); 1848 used
    pg_attribute_relid_attnum_index: 3072 total in 2 blocks; 1520 free (1 chunks); 1552 used
    pg_proc_oid_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
    pg_language_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
    pg_namespace_oid_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
    pg_amproc_fam_proc_index: 3072 total in 2 blocks; 1224 free (2 chunks); 1848 used
    pg_foreign_server_name_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
    pg_attribute_relid_attnam_index: 3072 total in 2 blocks; 1568 free (2 chunks); 1504 used
    pg_conversion_oid_index: 3072 total in 2 blocks; 1704 free (4 chunks); 1368 used
    pg_user_mapping_user_server_index: 3072 total in 2 blocks; 1568 free (2 chunks); 1504 used
    pg_conversion_name_nsp_index: 3072 total in 2 blocks; 1568 free (2 chunks); 1504 used
    pg_authid_oid_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
    pg_auth_members_member_role_index: 3072 total in 2 blocks; 1520 free (1 chunks); 1552 used
    pg_tablespace_oid_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
    pg_database_datname_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
    pg_auth_members_role_member_index: 3072 total in 2 blocks; 1568 free (2 chunks); 1504 used
    pg_database_oid_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
    pg_authid_rolname_index: 3072 total in 2 blocks; 1656 free (3 chunks); 1416 used
  MdSmgr: 8192 total in 1 blocks; 6560 free (0 chunks); 1632 used
  tokenize file cxt: 0 total in 0 blocks; 0 free (0 chunks); 0 used
  hba parser context: 7168 total in 3 blocks; 3760 free (5 chunks); 3408 used
  LOCALLOCK hash: 24576 total in 2 blocks; 13920 free (4 chunks); 10656 used
  Timezones: 83472 total in 2 blocks; 3744 free (0 chunks); 79728 used
  ErrorContext: 8192 total in 1 blocks; 8160 free (4 chunks); 32 used
2012-12-10 13:51:13 CET 250929 250929_webshop [unknown] 109.72.90.156 ERROR:  out of memory
2012-12-10 13:51:13 CET 250929 250929_webshop [unknown] 109.72.90.156 DETAIL:  Failed on request of size 6.
2012-12-10 13:51:13 CET 250929 250929_webshop [unknown] 109.72.90.156 STATEMENT:  SELECT DISTINCT artikel.productnr, MIN(omschrijving) AS naam, MIN(artikelgroep_artikel.volgnr) AS volgnr, MAX(product_voorraad.product_voorraad) FROM artikel LEFT JOIN artikel_uitvoering ON artikel.nr = artikel_uitvoering.artikel_nr LEFT JOIN artikelgroep_artikel ON artikel.nr = artikelgroep_artikel.artikel_nr JOIN (SELECT artikel.productnr, SUM(voorraad.aantal - voorraad.inorder - voorraad.inwsorder) AS product_voorraad FROM artikel JOIN voorraad ON voorraad.artikel_nr = artikel.nr WHERE voorraad.filiaal_id = 1 AND voorraad.aantal >= (voorraad.inorder + voorraad.inwsorder) GROUP BY artikel.productnr ORDER BY artikel.productnr) AS product_voorraad ON product_voorraad.productnr = artikel.productnr WHERE TRUE AND (artikel.productnr IN (SELECT DISTINCT artikel.productnr FROM artikel LEFT JOIN artikel_uitvoering ON artikel.nr = artikel_uitvoering.artikel_nr LEFT JOIN artikelgroep_artikel ON artikel.nr = artikelgroep_artikel.artikel_nr WHERE TRUE AND ((artikelgroep_artikel.artikelgroep_nr = 442)))) GROUP BY artikel.productnr HAVING MAX(product_voorraad) > 0 ORDER BY volgnr asc LIMIT 50 OFFSET 0