Out of memory error, FreeBSD 6.2, PostgreSQL 8.2.4 - Mailing list pgsql-general

Hi,

I have a query that has run on 3 other *identical* machines (hardware,
software, postgresql.conf idenntical, just other data in the database)
that give me an "out of memory error" every time I try (see below).

Anyone any idea of where or how to look for the problem or the
solution?

From the logfile:


TopMemoryContext: 81920 total in 9 blocks; 8856 free (12 chunks); 73064 used
SPI Plan: 39936 total in 7 blocks; 7808 free (2 chunks); 32128 used
Type information cache: 8192 total in 1 blocks; 1800 free (0 chunks); 6392 used
SPI Plan: 1024 total in 1 blocks; 192 free (0 chunks); 832 used
SPI Plan: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
SPI Plan: 3072 total in 2 blocks; 1152 free (0 chunks); 1920 used
SPI Plan: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
SPI Plan: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
SPI Plan: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
PL/PgSQL function context: 24576 total in 2 blocks; 15192 free (11 chunks); 9384 used
CFuncHash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used
Rendezvous variable hash: 8192 total in 1 blocks; 3848 free (0 chunks); 4344 used
PLpgSQL function cache: 24596 total in 2 blocks; 5904 free (0 chunks); 18692 used
TopTransactionContext: 8192 total in 1 blocks; 6792 free (0 chunks); 1400 used
ExecutorState: 8192 total in 1 blocks; 7784 free (0 chunks); 408 used
ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
SPI Exec: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
SPI Proc: 8192 total in 1 blocks; 7640 free (2 chunks); 552 used
SPI TupTable: 8192 total in 1 blocks; 6584 free (0 chunks); 1608 used
Operator class cache: 8192 total in 1 blocks; 4872 free (0 chunks); 3320 used
MessageContext: 40960 total in 3 blocks; 21448 free (11 chunks); 19512 used
smgr relation table: 24576 total in 2 blocks; 16080 free (4 chunks); 8496 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; 7904 free (0 chunks); 288 used
PortalHeapMemory: 31744 total in 6 blocks; 632 free (0 chunks); 31112 used
ExecutorState: 139376 total in 6 blocks; 59800 free (9 chunks); 79576 used
HashTableContext: 24576 total in 2 blocks; 16336 free (9 chunks); 8240 used
HashBatchContext: 533741652 total in 76 blocks; 1376 free (74 chunks); 533740276 used
HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
HashBatchContext: 32768 total in 2 blocks; 13808 free (1 chunks); 18960 used
HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
HashBatchContext: 65536 total in 3 blocks; 21648 free (2 chunks); 43888 used
HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
HashBatchContext: 131072 total in 4 blocks; 7824 free (4 chunks); 123248 used
HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
HashBatchContext: 16384 total in 1 blocks; 4808 free (0 chunks); 11576 used
HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
HashBatchContext: 524288 total in 6 blocks; 163376 free (6 chunks); 360912 used
TupleSort: 24600 total in 2 blocks; 6960 free (8 chunks); 17640 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
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: 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
PortalHeapMemory: 1024 total in 1 blocks; 880 free (0 chunks); 144 used
ExecutorState: 8192 total in 1 blocks; 6048 free (1 chunks); 2144 used
ExprContext: 8192 total in 1 blocks; 8176 free (6 chunks); 16 used
Relcache by OID: 8192 total in 1 blocks; 2336 free (0 chunks); 5856 used
CacheMemoryContext: 659000 total in 19 blocks; 18368 free (1 chunks); 640632 used
idx_components_status: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_components7: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_components5: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_components4: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_components3: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_components2: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_components1: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
components_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_indications_second_foundation_id: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_indications_patient_id: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_indications_first_foundation_id: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_indications9: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_indications3: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_indications2: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
indications_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
indication_functions_idx_status: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
indication_functions_idx_start_date: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_indication_functions_parent_id_fk: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_indication_functions8: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_indication_functions7: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_indication_functions6: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_indication_functions5: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_indication_functions4: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_indication_functions3: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_indication_functions2: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_indication_functions1: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
indication_functions_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_cares9: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_cares8: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_cares7: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_cares3: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_cares11: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_cares10: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_cares1: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
cares_idx_indication_function: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
cares_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_deliver_cares_status: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_deliver_cares_sibling_major_id: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_deliver_cares_sibling_id: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_deliver_cares_department_id: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_deliver_cares_deliver_date: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_deliver_cares4: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_deliver_cares3: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_deliver_cares2: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_deliver_cares1: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
deliver_cares_idx_assigned_org_personnel_id: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
deliver_cares_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_patients_surname_usage_id_fk: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_patients_staying_place_id_fk: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_patients_status: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_patients_residency_status_id_fk: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_patients_partner_in_awbz_fk: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_patients_marital_status_id_fk: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_patients_living_style_id_fk: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_patients_legal_status_id_fk: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_patients_indication_partner_id_fk: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_patients_human_id_pk: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_patients_communication_type_id_fk: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_patients_address_id_fk: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_patients16: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_patients15: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_patients14: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pk_patients: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_patient_transfers_to_department_id_fk: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_patient_transfers_patient_transfer_id_pk: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_patient_transfers_patient_id_fk: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_patient_transfers_org_personnel_id_fk: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
idx_patient_transfers_from_department_id_fk: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
patient_transfers_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_toast_2618_index: 1024 total in 1 blocks; 288 free (0 chunks); 736 used
patients: 39936 total in 7 blocks; 3064 free (0 chunks); 36872 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 288 free (0 chunks); 736 used
pk_execution_histories: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_shdepend_reference_index: 1024 total in 1 blocks; 288 free (0 chunks); 736 used
pg_depend_depender_index: 1024 total in 1 blocks; 216 free (0 chunks); 808 used
pg_depend_reference_index: 1024 total in 1 blocks; 216 free (0 chunks); 808 used
pg_database_datname_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_index_indrelid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 288 free (0 chunks); 736 used
pg_type_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 288 free (0 chunks); 736 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 288 free (0 chunks); 736 used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 288 free (0 chunks); 736 used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 216 free (0 chunks); 808 used
pg_proc_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used
pg_operator_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
pg_opclass_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 216 free (0 chunks); 808 used
pg_namespace_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
pg_language_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
pg_language_name_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
pg_authid_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
pg_authid_rolname_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
pg_database_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
pg_conversion_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_conversion_default_index: 1024 total in 1 blocks; 192 free (0 chunks); 832 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 288 free (0 chunks); 736 used
pg_class_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
pg_cast_source_target_index: 1024 total in 1 blocks; 288 free (0 chunks); 736 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 288 free (0 chunks); 736 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_amproc_opc_proc_index: 1024 total in 1 blocks; 216 free (0 chunks); 808 used
pg_amop_opr_opc_index: 1024 total in 1 blocks; 288 free (0 chunks); 736 used
pg_amop_opc_strat_index: 1024 total in 1 blocks; 216 free (0 chunks); 808 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
MdSmgr: 8192 total in 1 blocks; 5968 free (0 chunks); 2224 used
LOCALLOCK hash: 24576 total in 2 blocks; 16168 free (4 chunks); 8408 used
Timezones: 48616 total in 2 blocks; 5968 free (0 chunks); 42648 used
ErrorContext: 40960 total in 3 blocks; 40912 free (19 chunks); 48 used
2007-08-31 12:35:51 CEST zorgmaatwerk ERROR:  out of memory
2007-08-31 12:35:51 CEST zorgmaatwerk DETAIL:  Failed on request of size 36.
2007-08-31 12:35:51 CEST zorgmaatwerk CONTEXT:  PL/pgSQL function "smash_2688" line 36 at for over select rows
2007-08-31 12:35:51 CEST zorgmaatwerk STATEMENT:  -- SMASH-2685: Opening the Unexpected Cares link takes much too long
    --
    -- $Author:
    -- $Date:
    -- $Revision:
    --

    BEGIN;

    CREATE OR REPLACE FUNCTION SMASH_2688() RETURNS int4 AS'
      DECLARE
    ---------------
    -- This function is created by , on
    --
    -- It
    ---------------

        l_test                    INT4;
        l_continue                BOOLEAN := true;
        l_count                   INT4 := 0;
        l_count_rec               INT4 := 0;

        c_record                  RECORD;

      BEGIN

        select into l_test 1
        from   execution_histories
        where  name=''CONVERSION_SMASH_2688''
        ;
        if found
        then
          l_continue := false;
          RAISE NOTICE ''CONVERSION IS DONE BEFORE'';
        end if;

        ----------------
        -- CONVERSION --
        ----------------
        if l_continue is true
        then

          -------------------------
          -- ADD CONVERSION CODE --
          -------------------------
          for c_record in select p.sort_display_name                    as wie
                          ,      department_path(t.from_department_id)  as van
                          ,      t.from_department_id                   as van_dep_id
                          ,      department_path(t.to_department_id)    as naar
                          ,      t.to_department_id                     as naar_dep_id
                          ,      t.transfer_timestamp                   as wanneer
                          ,      d.care_id                              as care_id
                          ,      m.name                                 as zorg
                          ,      count(1)                               as aantal_recs
                          from   patient_transfers     t
                          ,      patients              p
                          ,      deliver_cares         d
                          ,      cares                 c
                          ,      indication_functions  f
                          ,      indications           i
                          ,      components            m
                          where  p.department_id          = t.to_department_id
                          and    d.department_id          = t.from_department_id
                          and    c.care_id                = d.care_id
                          and    f.indication_function_id = c.indication_function_id
                          and    i.indication_id          = f.indication_id
                          and    i.patient_id             = p.patient_id
                          and    c.component_id           = m.component_id
                          and    t.to_department_id      <> t.from_department_id
                          and    d.deliver_date          >= t.transfer_timestamp
                          group  by p.sort_display_name
                          ,         department_path(t.from_department_id)
                          ,         t.from_department_id
                          ,         department_path(t.to_department_id)
                          ,         t.to_department_id
                          ,         t.transfer_timestamp
                          ,         d.care_id
                          ,         m.name
                          order  by t.transfer_timestamp
          loop

            UPDATE  deliver_cares
            SET     department_id = c_record.naar_dep_id
            WHERE   care_id       = c_record.care_id
            AND     department_id = c_record.van_dep_id
            AND     deliver_date >= c_record.wanneer
            ;

            l_count     := l_count     + 1;
            l_count_rec := l_count_rec + c_record.aantal_recs;

            RAISE NOTICE ''Aangepast voor=%, zorg=%, care_id=%, van=%, naar=%, vanaf=%''
                         , c_record.wie
                         , c_record.zorg
                         , c_record.care_id
                         , c_record.van
                         , c_record.naar
                         , c_record.wanneer
                         ;

          end loop;

          -- insert record that conversation has run
          insert into execution_histories
          ( name
          , last_executed
          , execution_time)
          values
          ( ''CONVERSION_SMASH_2688''
          , now()
          , 0
          );

          RAISE NOTICE ''Totaal uitgevoerd=%, Aantal records aangepast=%''
                       , l_count
                       , l_count_rec
                       ;

        end if;

        RETURN 1;

      END;
    '  LANGUAGE 'plpgsql';

    SELECT SMASH_2688();

    COMMIT;

    -- !!! Must be in every alter script. Change script filename below only !!!
    INSERT INTO execution_histories (name,last_executed) VALUES ('0029_15.08.2007.sql','now');

    COMMIT;
2007-08-31 12:35:51 CEST zorgmaatwerk LOG:  duration: 0.120 ms  statement: ROLLBACK;


TIA

--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
web: www.askesis.nl