Re: Delete fails with out of memory

2009/10/18 yuliada <yuliada(at)gmail(dot)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(dot)rembialkowski(at)gmail(dot)com
http://filip.rembialkowski.net/