2009/10/18 yuliada <yuli...@gmail.com> > > Hello > > I have a large database and I'm trying to execute delete on a table which > has some related tables. The query fails with following error: > ERROR: out of memory > DETAIL: Failed on request of size 1048576. > > I'm new to postgresql and I'm currently trying to figure out what to do by > myself. I've played with some config memory parameters, but with no luck by > now. It would be great if somebody could tell me how to solve it or give > any > clues on why it happens or in which direction to search. > > Here is the log: > > 2009-10-17 17:02:09 PDTLOG: database system was shut down at 2009-10-17 > 17:02:07 PDT > 2009-10-17 17:02:09 PDTLOG: database system is ready to accept connections > 2009-10-17 17:02:09 PDTLOG: autovacuum launcher started > TopMemoryContext: 93376 total in 9 blocks; 10376 free (6 chunks); 83000 > used > TopTransactionContext: 8192 total in 1 blocks; 7632 free (0 chunks); 560 > used > AfterTriggerEvents: 872443512 total in 839 blocks; 9152 free (5 chunks); > 872434360 used > Operator class cache: 8192 total in 1 blocks; 3848 free (0 chunks); 4344 > used > Operator lookup cache: 24576 total in 2 blocks; 14072 free (6 chunks); > 10504 used > MessageContext: 40960 total in 3 blocks; 29544 free (71 chunks); 11416 > used > smgr relation table: 8192 total in 1 blocks; 2816 free (0 chunks); 5376 > 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; 8040 free (0 chunks); 152 used > PortalHeapMemory: 1024 total in 1 blocks; 936 free (0 chunks); 88 used > ExecutorState: 581688 total in 5 blocks; 40936 free (14 chunks); > 540752 used > TIDBitmap: 8380416 total in 10 blocks; 360696 free (34 chunks); > 8019720 used > ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used > Relcache by OID: 8192 total in 1 blocks; 3376 free (0 chunks); 4816 used > CacheMemoryContext: 667696 total in 20 blocks; 200064 free (0 chunks); > 467632 used > idx_att_data_source: 1024 total in 1 blocks; 344 free (0 chunks); 680 > used > idx_att_typeid: 1024 total in 1 blocks; 344 free (0 chunks); 680 used > idx_att_nodeid: 1024 total in 1 blocks; 344 free (0 chunks); 680 used > idx_att_id: 1024 total in 1 blocks; 304 free (0 chunks); 720 used > bn_attributes_pkey: 1024 total in 1 blocks; 344 free (0 chunks); 680 > used > pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 240 free (0 > chunks); 784 used > pg_database_datname_index: 1024 total in 1 blocks; 344 free (0 chunks); > 680 used > pg_index_indrelid_index: 1024 total in 1 blocks; 304 free (0 chunks); > 720 used > pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 192 free (0 > chunks); 832 used > pg_foreign_data_wrapper_name_index: 1024 total in 1 blocks; 344 free (0 > chunks); 680 used > pg_enum_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 used > pg_class_relname_nsp_index: 1024 total in 1 blocks; 240 free (0 chunks); > 784 used > pg_foreign_server_oid_index: 1024 total in 1 blocks; 344 free (0 > chunks); 680 used > pg_statistic_relid_att_index: 1024 total in 1 blocks; 240 free (0 > chunks); 784 used > pg_cast_source_target_index: 1024 total in 1 blocks; 240 free (0 > chunks); 784 used > pg_language_name_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 > used > pg_authid_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 > used > pg_amop_fam_strat_index: 1024 total in 1 blocks; 88 free (0 chunks); 936 > used > pg_index_indexrelid_index: 1024 total in 1 blocks; 304 free (0 chunks); > 720 used > pg_ts_template_tmplname_index: 1024 total in 1 blocks; 280 free (0 > chunks); 744 used > pg_ts_config_map_index: 1024 total in 1 blocks; 192 free (0 chunks); 832 > used > pg_opclass_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 > used > pg_foreign_data_wrapper_oid_index: 1024 total in 1 blocks; 344 free (0 > chunks); 680 used > pg_auth_members_member_role_index: 1024 total in 1 blocks; 280 free (0 > chunks); 744 used > pg_ts_dict_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 > used > pg_conversion_default_index: 1024 total in 1 blocks; 128 free (0 > chunks); 896 used > pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 88 free (0 > chunks); 936 used > pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 240 free (0 > chunks); 784 used > pg_enum_typid_label_index: 1024 total in 1 blocks; 280 free (0 chunks); > 744 used > pg_ts_config_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 > used > pg_user_mapping_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); > 680 used > pg_opfamily_am_name_nsp_index: 1024 total in 1 blocks; 192 free (0 > chunks); 832 used > pg_type_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used > pg_aggregate_fnoid_index: 1024 total in 1 blocks; 344 free (0 chunks); > 680 used > pg_constraint_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); > 680 used > pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 280 free (0 > chunks); 744 used > pg_ts_parser_prsname_index: 1024 total in 1 blocks; 280 free (0 chunks); > 744 used > pg_ts_config_cfgname_index: 1024 total in 1 blocks; 280 free (0 chunks); > 744 used > pg_ts_parser_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 > used > pg_operator_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 > used > pg_namespace_nspname_index: 1024 total in 1 blocks; 304 free (0 chunks); > 720 used > pg_ts_template_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); > 680 used > pg_amop_opr_fam_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 > used > pg_ts_dict_dictname_index: 1024 total in 1 blocks; 280 free (0 chunks); > 744 used > pg_auth_members_role_member_index: 1024 total in 1 blocks; 280 free (0 > chunks); 744 used > pg_type_typname_nsp_index: 1024 total in 1 blocks; 280 free (0 chunks); > 744 used > pg_opfamily_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 > used > pg_class_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 > used > pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 152 free (0 > chunks); 872 used > pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 240 free (0 > chunks); 784 used > pg_proc_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used > pg_language_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 > used > pg_namespace_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 > used > pg_database_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 > used > pg_amproc_fam_proc_index: 1024 total in 1 blocks; 88 free (0 chunks); > 936 used > pg_authid_rolname_index: 1024 total in 1 blocks; 304 free (0 chunks); > 720 used > pg_foreign_server_name_index: 1024 total in 1 blocks; 344 free (0 > chunks); 680 used > pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 280 free (0 > chunks); 744 used > pg_conversion_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); > 680 used > pg_user_mapping_user_server_index: 1024 total in 1 blocks; 280 free (0 > chunks); 744 used > pg_conversion_name_nsp_index: 1024 total in 1 blocks; 280 free (0 > chunks); 744 used > MdSmgr: 8192 total in 1 blocks; 5200 free (0 chunks); 2992 used > LOCALLOCK hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used > Timezones: 79320 total in 2 blocks; 5968 free (0 chunks); 73352 used > ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used > 2009-10-17 17:05:41 PDTERROR: out of memory > 2009-10-17 17:05:41 PDTDETAIL: Failed on request of size 1048576. > 2009-10-17 17:05:41 PDTSTATEMENT: delete from bn_attributes where > id>309279169 >
what is you postgres version? what operating system? how much RAM is there in the machine? what are memory settings (work_mem, other memory related)? -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/
