Postgresql 7.4.3/8.2.4 out of memory - Mailing list pgsql-general

Dear all,


With the below transaction we got an out of memory error.


BEGIN;
ANALYZE referenceAuthorLnkTemp;

INSERT INTO referenceAuthor (author)
    SELECT DISTINCT ON (author) author
    FROM referenceAuthorLnkTemp;

ANALYZE referenceAuthor;

UPDATE referenceAuthorLnkTemp
    SET idAuthor = referenceAuthor.id
    FROM referenceAuthor
    WHERE referenceAuthorLnkTemp.author = referenceAuthor.author;

INSERT INTO referenceAuthorLnk (idAuthor, idReference)
    SELECT DISTINCT ON (referenceAuthorLnkTemp.idAuthor,
referenceAuthorLnkTemp.idReference) referenceAuthorLnkTemp.idAuthor,
referenceAuthorLnkTemp.idReference
    FROM referenceAuthorLnkTemp;
END;


This transaction belongs to a file with many other transactions
executed through a java program and JDBC
(postgresql-8.2-505.jdbc3.jar).
Launching this transaction by hand in psql works.

Server has 8GB of RAM and OS is RHEL 3 update 6.
Linux 2.4.21-37.ELsmp #1 SMP Wed Sep 7 13:28:55 EDT 2005 i686 i686 i386
GNU/Linux


The number of rows in tables are:

43360793 tuples referenceauthorlnktemp
43360791 tuples referenceauthorlnk
56990 tuples referenceauthor

Thanks for help.


TopMemoryContext: 49152 total in 5 blocks; 7576 free (23 chunks); 41576
used
unnamed prepared statement: 24576 total in 2 blocks; 13672 free (1
chunks); 10904 used
TopTransactionContext: 8192 total in 1 blocks; 4736 free (0 chunks);
3456 used
AfterTriggerEvents: 3170885632 total in 397 blocks; 12200 free (384
chunks); 3170873432 used
SPI Plan: 7168 total in 3 blocks; 4056 free (0 chunks); 3112 used
SPI Plan: 3072 total in 2 blocks; 800 free (0 chunks); 2272 used
SPI Plan: 3072 total in 2 blocks; 744 free (0 chunks); 2328 used
SPI Plan: 3072 total in 2 blocks; 808 free (0 chunks); 2264 used
SPI Plan: 7168 total in 3 blocks; 4056 free (0 chunks); 3112 used
SPI Plan: 3072 total in 2 blocks; 760 free (0 chunks); 2312 used
SPI Plan: 3072 total in 2 blocks; 808 free (0 chunks); 2264 used
SPI Plan: 3072 total in 2 blocks; 752 free (0 chunks); 2320 used
SPI Plan: 3072 total in 2 blocks; 752 free (0 chunks); 2320 used
SPI Plan: 3072 total in 2 blocks; 808 free (0 chunks); 2264 used
SPI Plan: 3072 total in 2 blocks; 1088 free (0 chunks); 1984 used
SPI Plan: 3072 total in 2 blocks; 808 free (0 chunks); 2264 used
SPI Plan: 3072 total in 2 blocks; 808 free (0 chunks); 2264 used
SPI Plan: 7168 total in 3 blocks; 4056 free (0 chunks); 3112 used
SPI Plan: 3072 total in 2 blocks; 808 free (0 chunks); 2264 used
SPI Plan: 3072 total in 2 blocks; 808 free (0 chunks); 2264 used
SPI Plan: 3072 total in 2 blocks; 752 free (0 chunks); 2320 used
SPI Plan: 3072 total in 2 blocks; 808 free (0 chunks); 2264 used
SPI Plan: 7168 total in 3 blocks; 4056 free (0 chunks); 3112 used
SPI Plan: 3072 total in 2 blocks; 808 free (0 chunks); 2264 used
SPI Plan: 3072 total in 2 blocks; 1088 free (0 chunks); 1984 used
SPI Plan: 3072 total in 2 blocks; 1088 free (0 chunks); 1984 used
SPI Plan: 7168 total in 3 blocks; 4056 free (0 chunks); 3112 used
SPI Plan: 3072 total in 2 blocks; 1088 free (0 chunks); 1984 used
SPI Plan: 3072 total in 2 blocks; 1088 free (0 chunks); 1984 used
SPI Plan: 3072 total in 2 blocks; 1088 free (0 chunks); 1984 used
SPI Plan: 7168 total in 3 blocks; 4056 free (0 chunks); 3112 used
SPI Plan: 3072 total in 2 blocks; 1088 free (0 chunks); 1984 used
SPI Plan: 3072 total in 2 blocks; 800 free (0 chunks); 2272 used
SPI Plan: 3072 total in 2 blocks; 808 free (0 chunks); 2264 used
SPI Plan: 3072 total in 2 blocks; 784 free (0 chunks); 2288 used
SPI Plan: 3072 total in 2 blocks; 744 free (0 chunks); 2328 used
SPI Plan: 3072 total in 2 blocks; 800 free (0 chunks); 2272 used
SPI Plan: 3072 total in 2 blocks; 744 free (0 chunks); 2328 used
SPI Plan: 3072 total in 2 blocks; 744 free (0 chunks); 2328 used
SPI Plan: 7168 total in 3 blocks; 4056 free (0 chunks); 3112 used
RI query cache: 24576 total in 2 blocks; 14136 free (5 chunks); 10440
used
Operator class cache: 8192 total in 1 blocks; 4872 free (0 chunks);
3320 used
Type information cache: 8192 total in 1 blocks; 1800 free (0 chunks);
6392 used
MessageContext: 8192 total in 1 blocks; 7840 free (0 chunks); 352 used
smgr relation table: 24576 total in 2 blocks; 16080 free (4 chunks);
8496 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; 896 free (0 chunks); 128 used
ExecutorState: 24576 total in 2 blocks; 19088 free (12 chunks); 5488
used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
TupleSort: 1417712 total in 22 blocks; 853304 free (13188 chunks);
564408 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
Unique: 0 total in 0 blocks; 0 free (0 chunks); 0 used
Relcache by OID: 8192 total in 1 blocks; 2336 free (0 chunks); 5856 used
CacheMemoryContext: 659000 total in 19 blocks; 22056 free (3 chunks);
636944 used
referenceauthorlnk_pkey: 1024 total in 1 blocks; 288 free (0 chunks);
736 used
uq_referenceauthor_author: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
referenceauthor_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
uq_keyword_keyword: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
keyword_pkey: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
uq_genename_value: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
genename_pkey: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
uq_gene_numident: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
gene_pkey: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 288 free (0
chunks); 736 used
uq_domain_name: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
domain_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
uq_component_name: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
component_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
uq_commonname_comname: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
commonname_pkey: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
uq_organismspecies_scname_taxid: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
organismspecies_pkey: 1024 total in 1 blocks; 352 free (0 chunks); 672
used
uq_uniprotentity_primaryac: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
uniprotentity_pkey: 1024 total in 1 blocks; 352 free (0 chunks); 672
used
pg_shdepend_depender_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_description_o_c_o_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_trigger_tgconstrrelid_index: 1024 total in 1 blocks; 352 free (0
chunks); 672 used
pg_trigger_tgconstrname_index: 1024 total in 1 blocks; 352 free (0
chunks); 672 used
pg_trigger_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672
used
pg_depend_depender_index: 1024 total in 1 blocks; 216 free (0 chunks);
808 used
pg_depend_reference_index: 1024 total in 1 blocks; 216 free (0 chunks);
808 used
pg_constraint_contypid_index: 1024 total in 1 blocks; 352 free (0
chunks); 672 used
pg_constraint_conrelid_index: 1024 total in 1 blocks; 352 free (0
chunks); 672 used
pg_constraint_oid_index: 1024 total in 1 blocks; 352 free (0 chunks);
672 used
pg_constraint_conname_nsp_index: 1024 total in 1 blocks; 288 free (0
chunks); 736 used
massspectrometrycomment_pkey: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
isoformlnk_pkey: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 288 free (0
chunks); 736 used
pg_index_indrelid_index: 1024 total in 1 blocks; 352 free (0 chunks);
672 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks);
696 used
pg_type_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 288 free (0
chunks); 736 used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_proc_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
pg_operator_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672
used
pg_opclass_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672
used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_namespace_oid_index: 1024 total in 1 blocks; 352 free (0 chunks);
672 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 352 free (0
chunks); 672 used
pg_language_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_language_name_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 352 free (0 chunks);
672 used
pg_authid_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672
used
pg_authid_rolname_index: 1024 total in 1 blocks; 352 free (0 chunks);
672 used
pg_database_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672
used
pg_conversion_oid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_conversion_default_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 288 free (0
chunks); 736 used
pg_class_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672
used
pg_cast_source_target_index: 1024 total in 1 blocks; 288 free (0
chunks); 736 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 288 free (0
chunks); 736 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 288 free (0
chunks); 736 used
pg_amproc_opc_proc_index: 1024 total in 1 blocks; 216 free (0 chunks);
808 used
pg_amop_opr_opc_index: 1024 total in 1 blocks; 288 free (0 chunks); 736
used
pg_amop_opc_strat_index: 1024 total in 1 blocks; 216 free (0 chunks);
808 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
MdSmgr: 8192 total in 1 blocks; 4288 free (0 chunks); 3904 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
2007-06-05 08:09:29 CEST:unipadm:unipbactest_bacteria_trembl_new>ERROR:
  out of memory
2007-06-05 08:09:29
CEST:unipadm:unipbactest_bacteria_trembl_new>DETAIL:  Failed on request
of size 32.
2007-06-05 08:09:29
CEST:unipadm:unipbactest_bacteria_trembl_new>STATEMENT:

    INSERT INTO referenceAuthorLnk (idAuthor, idReference)
        SELECT DISTINCT ON (referenceAuthorLnkTemp.idAuthor,
referenceAuthorLnkTemp.idReference) referenceAuthorLnkTemp.idAuthor,
referenceAuthorLnkTemp.idReference
        FROM referenceAuthorLnkTemp
2007-06-05 08:09:30 CEST:unipadm:unipbactest_bacteria_trembl_new>LOG:
unexpected EOF on client connection