ERROR: Memory exhausted in AllocSetAlloc(68) - Mailing list pgsql-bugs

Hello all,

I can't understand why my server, having 1 Gb of the RAM can't reproduce one simple
SELECT on two not so very large tables:

isbs=# SELECT count(i.*),sum(i.param1),sum(60-i.param1) FROM inlog i, object ob WHERE i.dttm>='1-4-2003' AND
i.dttm<'1-5-2003' AND i.param1<60 AND i.sgrid=10 AND i.servid<>1496 AND ob.dton<=i.dttm AND i.dttm<=ob.dtoff
AND not ob.obid IN (292,746);
ERROR:  Memory exhausted in AllocSetAlloc(68)



There are server's error log:

TopMemoryContext: 32796 total in 4 blocks; 14036 free (5 chunks); 18760 used
TopTransactionContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
DeferredTriggerXact: 0 total in 0 blocks; 0 free (0 chunks); 0 used
TransactionCommandContext: 531619840 total in 74 blocks; 6872 free (75 chunks); 531612968 used
PlanExprContext: 8192 total in 1 blocks; 8120 free (0 chunks); 72 used
PlanExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
PlanExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
PlanExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
AggExprContext2: 8192 total in 1 blocks; 8008 free (3 chunks); 184 used
AggExprContext1: 8192 total in 1 blocks; 8008 free (3 chunks); 184 used
PlanExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
QueryContext: 57344 total in 3 blocks; 17144 free (2 chunks); 40200 used
DeferredTriggerSession: 0 total in 0 blocks; 0 free (0 chunks); 0 used
PortalMemory: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
CacheMemoryContext: 2088960 total in 8 blocks; 1003504 free (1 chunks); 1085456 used
object_devid: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
object_oid: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
object_obid_dton_dtoff: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
inlog_servid: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
inlog_state: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
inlog_dttm_servid: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
inlog_oid: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_type_oid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
person_pkey: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
person_obid: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_index_indrelid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
ttprefixes_pkey: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_trigger_oid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_trigger_tgconstrrelid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_trigger_tgconstrname_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_constraint_oid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_constraint_conrelid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_constraint_conname_nsp_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_depend_reference_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_depend_depender_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_attrdef_oid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_user: 5120 total in 5 blocks; 420 free (0 chunks); 4700 used
pg_amproc_opc_procnum_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_amop_opc_strategy_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_group_sysid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_cast_source_target_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_class_oid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_conversion_default_index: 2076 total in 2 blocks; 700 free (0 chunks); 1376 used
pg_conversion_oid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_namespace_oid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_language_oid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_opclass_oid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_shadow_usename_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_operator_oid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_proc_oid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_amop_opc_opr_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_group_name_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_language_name_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_proc_proname_args_nsp_index: 2076 total in 2 blocks; 700 free (0 chunks); 1376 used
pg_operator_oprname_l_r_n_index: 2076 total in 2 blocks; 700 free (0 chunks); 1376 used
pg_shadow_usesysid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
MdSmgr: 8192 total in 1 blocks; 6116 free (0 chunks); 2076 used
DynaHash: 8192 total in 1 blocks; 6668 free (0 chunks); 1524 used
DynaHashTable: 8192 total in 1 blocks; 5068 free (0 chunks); 3124 used
DynaHashTable: 8192 total in 1 blocks; 6104 free (0 chunks); 2088 used
DynaHashTable: 8192 total in 1 blocks; 5068 free (0 chunks); 3124 used
DynaHashTable: 8192 total in 1 blocks; 6104 free (0 chunks); 2088 used
DynaHashTable: 8192 total in 1 blocks; 3496 free (0 chunks); 4696 used
DynaHashTable: 8192 total in 1 blocks; 3496 free (0 chunks); 4696 used
DynaHashTable: 24576 total in 2 blocks; 9112 free (4 chunks); 15464 used
DynaHashTable: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
DynaHashTable: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
DynaHashTable: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
DynaHashTable: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
DynaHashTable: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
2003-05-27 16:37:07 ERROR:  Memory exhausted in AllocSetAlloc(68)


isbs=# select count(*) from inlog;
  count
---------
 1227532

isbs=# select count(*) from object;
 count
-------
  1580

isbs=# select relation_size('inlog');
 relation_size
---------------
     114278400          (about 100 Mb)

isbs=# select relation_size('object');
 relation_size
---------------
        532480          (about 500 Kb)


There are some memory-related parameters from postgresql.conf file:

> shared_buffers = 20000
> max_fsm_relations = 100
> max_fsm_pages = 10000
> max_locks_per_transaction = 64
> wal_buffers = 32
>
> sort_mem = 40960
> vacuum_mem = 8192
>
isbs=# select version();
                               version
---------------------------------------------------------------------
 PostgreSQL 7.3.2 on i386-unknown-freebsd4.7, compiled by GCC 2.95.4



Is this a bug or I really need more physical RAM especially for this query?
Thanks a lot for any comments.



--
  best reagards,
Ruslan A Dautkhanov