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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
