Thread: Memory error

Memory error

From

Daniel Weinberger

Date:

27 August 2009, 18:41:58

Yesterday I ran an SQL import.  It failed after 3.33 hours with following
message:



ERROR:  out of memory

DETAIL:  Failed on request of size 32.



********** Error **********



ERROR: out of memory

SQL state: 53200
Detail: Failed on request of size 32.

The log file is as follows;

2009-08-26 18:43:40 PDT   LOG:  checkpoints are occurring too frequently (28
seconds apart)
2009-08-26 18:43:40 PDT   HINT:  Consider increasing the configuration
parameter "checkpoint_segments".
2009-08-26 18:45:10 PDT   LOG:  checkpoints are occurring too frequently (28
seconds apart)
2009-08-26 18:45:10 PDT   HINT:  Consider increasing the configuration
parameter "checkpoint_segments".
2009-08-26 18:45:36 PDT   LOG:  checkpoints are occurring too frequently (26
seconds apart)
2009-08-26 18:45:36 PDT   HINT:  Consider increasing the configuration
parameter "checkpoint_segments".
2009-08-26 18:50:20 PDT   LOG:  checkpoints are occurring too frequently (27
seconds apart)
2009-08-26 18:50:20 PDT   HINT:  Consider increasing the configuration
parameter "checkpoint_segments".
2009-08-26 18:51:21 PDT   LOG:  checkpoints are occurring too frequently (27
seconds apart)
2009-08-26 18:51:21 PDT   HINT:  Consider increasing the configuration
parameter "checkpoint_segments".
2009-08-26 18:54:01 PDT   LOG:  checkpoints are occurring too frequently (27
seconds apart)
2009-08-26 18:54:01 PDT   HINT:  Consider increasing the configuration
parameter "checkpoint_segments".
2009-08-26 18:54:27 PDT   LOG:  checkpoints are occurring too frequently (26
seconds apart)
2009-08-26 18:54:27 PDT   HINT:  Consider increasing the configuration
parameter "checkpoint_segments".
2009-08-26 18:54:51 PDT   LOG:  checkpoints are occurring too frequently (24
seconds apart)
2009-08-26 18:54:51 PDT   HINT:  Consider increasing the configuration
parameter "checkpoint_segments".
2009-08-26 18:55:17 PDT   LOG:  checkpoints are occurring too frequently (26
seconds apart)
2009-08-26 18:55:17 PDT   HINT:  Consider increasing the configuration
parameter "checkpoint_segments".
2009-08-26 19:06:04 PDT   LOG:  checkpoints are occurring too frequently (26
seconds apart)
2009-08-26 19:06:04 PDT   HINT:  Consider increasing the configuration
parameter "checkpoint_segments".
2009-08-26 19:12:39 PDT   LOG:  checkpoints are occurring too frequently (29
seconds apart)
2009-08-26 19:12:39 PDT   HINT:  Consider increasing the configuration
parameter "checkpoint_segments".
2009-08-26 19:13:08 PDT   LOG:  checkpoints are occurring too frequently (29
seconds apart)
2009-08-26 19:13:08 PDT   HINT:  Consider increasing the configuration
parameter "checkpoint_segments".
2009-08-26 19:14:13 PDT   LOG:  checkpoints are occurring too frequently (28
seconds apart)
2009-08-26 19:14:13 PDT   HINT:  Consider increasing the configuration
parameter "checkpoint_segments".
2009-08-26 19:15:47 PDT   LOG:  checkpoints are occurring too frequently (29
seconds apart)
2009-08-26 19:15:47 PDT   HINT:  Consider increasing the configuration
parameter "checkpoint_segments".
2009-08-26 19:16:09 PDT   LOG:  checkpoints are occurring too frequently (22
seconds apart)
2009-08-26 19:16:09 PDT   HINT:  Consider increasing the configuration
parameter "checkpoint_segments".
TopMemoryContext: 49416 total in 6 blocks; 4768 free (5 chunks); 44648 used
  TopTransactionContext: 8192 total in 1 blocks; 7776 free (0 chunks); 416
used
    AfterTriggerEvents: 317710336 total in 49 blocks; 1312 free (42 chunks);
317709024 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: 131072 total in 5 blocks; 13008 free (4 chunks); 118064
used
  smgr relation table: 8192 total in 1 blocks; 2808 free (0 chunks); 5384
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: 2048 total in 1 blocks; 888 free (0 chunks); 1160 used
      ExecutorState: 65592 total in 4 blocks; 22888 free (45 chunks); 42704
used
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
        HashTableContext: 8192 total in 1 blocks; 8096 free (0 chunks); 96
used
          HashBatchContext: 2113560 total in 10 blocks; 915344 free (8
chunks); 1198216 used
        HashTableContext: 8192 total in 1 blocks; 8144 free (1 chunks); 48
used
          HashBatchContext: 2113560 total in 10 blocks; 434960 free (8
chunks); 1678600 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: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
  Relcache by OID: 8192 total in 1 blocks; 3376 free (0 chunks); 4816 used
  CacheMemoryContext: 667472 total in 20 blocks; 204496 free (0 chunks);
462976 used
    keycodeindex: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
    dateindex: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
    gifts_pkey: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
    pet_temp_kc_kc_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
    keycodes_pkey: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
    pet_temp_gift_kc_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
    pet_temp_gift_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
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_ts_dict_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
    pg_aggregate_fnoid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
    pg_language_name_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_ts_dict_dictname_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
    pg_namespace_nspname_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
    pg_opfamily_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
    pg_opclass_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
    pg_ts_parser_prsname_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
    pg_amop_fam_strat_index: 1024 total in 1 blocks; 88 free (0 chunks); 936
used
    pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
    pg_trigger_tgrelid_tgname_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_auth_members_role_member_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
    pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
    pg_ts_config_cfgname_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
    pg_authid_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
    pg_ts_config_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_language_oid_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_proc_proname_args_nsp_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
    pg_ts_parser_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_conversion_name_nsp_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
    pg_class_relname_nsp_index: 1024 total in 1 blocks; 240 free (0 chunks);
784 used
    pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
    pg_class_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_operator_oprname_l_r_n_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_type_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
    pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
    pg_authid_rolname_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
    pg_auth_members_member_role_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
    pg_enum_typid_label_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
    pg_constraint_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
    pg_conversion_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 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_namespace_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
    pg_type_typname_nsp_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
    pg_operator_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
    pg_amop_opr_fam_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_opfamily_am_name_nsp_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
    pg_ts_template_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
  MdSmgr: 8192 total in 1 blocks; 7744 free (0 chunks); 448 used
  LOCALLOCK hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
  Timezones: 48616 total in 2 blocks; 5968 free (0 chunks); 42648 used
  ErrorContext: 8192 total in 1 blocks; 8176 free (3 chunks); 16 used
2009-08-26 19:16:41 PDT world margo ERROR:  out of memory
2009-08-26 19:16:41 PDT world margo DETAIL:  Failed on request of size 32.
2009-08-26 19:16:41 PDT world margo STATEMENT:  insert into pet.gifts
 (select a.idnumber, a.amount, a.date, a.keycode, a.don_code, a.gift_id,
a.gift_type, a.fund, a.iho_flag, a.iho_name,
  a.soft_credit, a.add_date, a.edit_date, a.deleted, a.orig_id,
a.batch_code, a.soft_id, a.delete_date, a.directresponse, a.sustsignup,
  a.origkey, a.notes, a.clnt_giftid, a.source, a.importbatch, a.archive
 from pet.gifts_temp as a
 left join pet.keycodes as b
 on a.keycode = b.keycode
 left join pet.gifts as c
 on (a.gift_id = c.gift_id)
 where b.keycode is not null
 and c.gift_id is null
 );

Please tell me what I need to do to insert this data into my database.

Re: Memory error

From

Tom Lane

Date:

27 August 2009, 20:23:29

Daniel Weinberger <daniel.weinberger@gmail.com> writes:
> Yesterday I ran an SQL import.  It failed after 3.33 hours with following
> message:
> ERROR:  out of memory

Looks like the memory consumption is here:

>     AfterTriggerEvents: 317710336 total in 49 blocks; 1312 free (42 chunks);
> 317709024 used

Perhaps you should look into the memory limits on your postmaster, as
falling over after 300MB doesn't seem very sane for any reasonably
modern machine.  However, the real answer is probably to drop whatever
after-row triggers you've got on the target table (perhaps FOREIGN KEY
constraints?) and re-establish the constraints after doing the import.

There's been some speculation about better ways to manage mass updates
of foreign key constraints, but nobody's done anything about it yet...

            regards, tom lane