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:
Show quoted text
"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 memoryLooks 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