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 Regards, Eelke Klein Bolt Afrekensystemen Mplus Software