Обсуждение: BUG #4004: out of memory

The following bug has been logged online:

Bug reference:      4004
Logged by:          Manos Tsagias
Email address:      tsagias@gmail.com
PostgreSQL version: 8.3.0
Operating system:   Mac OS X
Description:        out of memory
Details:

hi,

 I used COPY to import a 2GB CSV file into a table. After almost 2 hours I
got:

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

ERROR: out of memory
SQL state: 53200
Detail: Failed on request of size 32.
Context: COPY mkil_ratings, line 92876876: "16452,1409665,4,2005-07-10"

Before that, Postgresql was complaining about:

HINT:  Consider increasing the configuration parameter
"checkpoint_segments".
LOG:  checkpoints are occurring too frequently (14 seconds apart)


The above two lines were repeated many-many times

and finally, the trace of the error:

x 127.0.0.1(49853) COPY(728) malloc: *** error: can't allocate region
x 127.0.0.1(49853) COPY(728) malloc: *** set a breakpoint in szone_error to
debug
TopMemoryContext: 34608 total in 4 blocks; 4976 free (8 chunks); 29632 used
  TopTransactionContext: 8192 total in 1 blocks; 7272 free (0 chunks); 920
used
    AfterTriggerEvents: 3715096576 total in 457 blocks; 14264 free (447
chunks); 3715082312 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: 8192 total in 1 blocks; 5576 free (0 chunks); 2616 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: 72732 total in 4 blocks; 3480 free (13 chunks); 69252
used
      ExecutorState: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
        ExprContext: 8192 total in 1 blocks; 8104 free (0 chunks); 88 used
  Relcache by OID: 8192 total in 1 blocks; 3376 free (0 chunks); 4816 used
  CacheMemoryContext: 667472 total in 20 blocks; 251248 free (1 chunks);
416224 used
    movie_ratings_pkey: 1024 total in 1 blocks; 152 free (0 chunks); 872
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; 7552 free (0 chunks); 640 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


NOTE: The first two lines (about the debug) were repeated hundred times or
so..

Hope that helps,
Manos._

"Manos Tsagias" <tsagias@gmail.com> writes:
>  I used COPY to import a 2GB CSV file into a table. After almost 2 hours I
> got:
> ERROR: out of memory

Looks like you're running out of memory for pending AFTER trigger
events.  What triggers or foreign keys have you got on that table?

The usual recommendation for bulk loading into a table with a foreign
key is to drop the FK constraint, bulk load, re-create the FK
constraint.  This will be significantly faster than allowing the FK
to be checked incrementally.  Yeah, it'd be nice if that were
automated for you ...

            regards, tom lane

Thank you Tom for the reply.

There are no AFTER trigger events defined, but there is one FK. I'll drop
the FK as you suggested.

Thank you again for your help.
Manos._

On Sat, Mar 1, 2008 at 7:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "Manos Tsagias" <tsagias@gmail.com> writes:
> >  I used COPY to import a 2GB CSV file into a table. After almost 2 hours
> I
> > got:
> > ERROR: out of memory
>
> Looks like you're running out of memory for pending AFTER trigger
> events.  What triggers or foreign keys have you got on that table?
>
> The usual recommendation for bulk loading into a table with a foreign
> key is to drop the FK constraint, bulk load, re-create the FK
> constraint.  This will be significantly faster than allowing the FK
> to be checked incrementally.  Yeah, it'd be nice if that were
> automated for you ...
>
>                        regards, tom lane
>