Thread: Delete fails with out of memory

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

Thanks in advance, Yulia
--
View this message in context: http://www.nabble.com/Delete-fails-with-out-of-memory-tp25943614p25943614.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Sat, 2009-10-17 at 20:02 -0700, yuliada wrote:

> 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:
>
>     AfterTriggerEvents: 872443512 total in 839 blocks; 9152 free (5 chunks);
> 872434360 used

The RI triggers are causing the out of memory situation, it would
appear. You'll need to either
* use Truncate
* run a series of smaller transactions to delete the rows
* insert the rows you wish to keep into a new table then add RI to it,
drop the old, rename the new.

--
 Simon Riggs           www.2ndQuadrant.com