Re: out of memory error

From: Silvio Brandani <silvio(dot)brandani(at)tech(dot)sdb(dot)it>
To: pgsql-admin(at)postgresql(dot)org
Cc: Silvio Brandani <silvio(dot)brandani(at)tech(dot)sdb(dot)it>
Subject: Re: out of memory error
Date: 2010-09-02 13:37:59
Message-ID: 4C7FA8B7.4000702@tech.sdb.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Silvio Brandani ha scritto:
>
> Still problems of Out of Memory:
>
> the query is the following and if I run it from psql is working fine,
> but from application I get error :
>
> SELECT MAX(oec.ctnr_nr) ::char(13) as Ctnr_nr,MAX(oec.file_ref)
> ::char(7) as File_Ref,MAX(oec.move_type) ::char(5)
> as Ctnr_type,MAX(oec.ct_feet) ::char(3) as feet,MAX(cons.nombre)
> ::char(51) as Consignee,MAX(refs.name_sales) ::char(51)
> as Salesman,MAX(refs2.name_principal) ::char(51) as
> Cargo_principal,MAX(uslist.username) ::char(50)
> as User,MAX(fab.nombre) ::char(51) as Shipper,MAX(agent.nombre)
> ::char(51) as Dest_Agent,MAX(zmar2.nombre) ::char(61)
> as Ocean_Area,MAX(aer_l.codigo) ::char(7) as
> Port_Code_L,MAX(zmar3.codigo) ::char(7)
> as Ocean_Area_L_Code,MAX(zmar.nombre) ::char(61) as
> Ocean_Area,MAX(aer_d.codigo) ::char(7)
> as Port_Code_D,MAX(zmar4.codigo) ::char(7) as
> Ocean_Area_D_Code,MAX(oev.vessel_name) ::char(31)
> as Vessel_Name,MAX(oev.vessel_voy) ::char(11) as
> Vessel_Voy,MAX(oevi.departure) as Departure,MAX(cia.nombre) ::char(31)
> as SS_Line,MAX(cia2.nom_cod) ::char(5) as Scac_Code,MAX(oes.hbl)
> ::char(16) as HBL,MAX(oes.mbl) ::char(16)
> as BL,SUM(oem.volume) as Volume,MAX(oes.con_venta) ::char(4) as
> Incoterm ,MAX(oes.booking_nr) as key1, MAX(oem.progr_ctnr)
> as key2 FROM oe_sped_t oes LEFT OUTER JOIN ref_sales refs ON oes.hbl
> =refs.house AND oes.expediente = refs.reference
> and oes.azienda = refs.azienda LEFT OUTER JOIN ref_sales refs2 ON
> oes.hbl =refs2.house AND oes.expediente = refs2.reference
> and oes.azienda = refs2.azienda,oe_sped_m oem, oe_container oec,m_cli
> cons,open_ref oref,m_cli fab,m_cli agent,
> m_aeropu aer_l,m_aeropu aer_d,oe_vessel_t oev,m_cianav cia,m_cianav
> cia2,m_zonmar zmar,m_zonmar zmar2,m_zonmar zmar3,
> m_zonmar zmar4,oe_vessel_imbarco oevi,users uslist WHERE oes.entry_nr
> = oem.entry_nr AND oes.booking_nr = oec.booking_nr
> AND oem.progr_ctnr = oec.progr_ctnr AND oes.azienda = oem.azienda AND
> oes.azienda = oec.azienda
> AND oem.azienda = oec.azienda AND oes.azienda IN ('60') AND
> oevi.departure Between '8/1/2010'
> AND '8/31/2010' AND oes.cod_des = cons.codigo AND oes.expediente =
> oref.reference and oes.azienda =oref.azienda
> AND oes.cod_fab = fab.codigo AND oes.agen_des = agent.codigo AND
> oes.aero_ori = aer_l.codigo
> AND oes.aero_des = aer_d.codigo AND oes.vessel_code = oev.vessel_code
> AND oes.azienda = oev.azienda
> AND aer_d.zon_mar = zmar.codigo AND aer_d.zon_mar = zmar4.codigo AND
> aer_l.zon_mar = zmar2.codigo
> AND aer_l.zon_mar = zmar3.codigo AND oes.vessel_code = oevi.vessel_code
> AND oes.aero_ori = oevi.port_loading and oes.azienda = oevi.azienda
> AND oev.carrier = cia.codigo and oev.azienda=cia.azienda AND
> oev.carrier= cia2.codigo and oev.azienda = cia2.azienda
> AND oref.id_user=lpad(CAST(uslist.userid as char(6)),6,'0') GROUP BY
> oes.azienda,oes.booking_nr,oem.progr_ctnr
>
> And the trace in the logfile is:
>
>
> TopMemoryContext: 178680 total in 14 blocks; 6624 free (14 chunks);
> 172056 used
> TopTransactionContext: 8192 total in 1 blocks; 7504 free (0 chunks);
> 688 used
> Type information cache: 24576 total in 2 blocks; 11888 free (5
> chunks); 12688 used
> Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks);
> 6512 used
> Operator lookup cache: 24576 total in 2 blocks; 11888 free (5
> chunks); 12688 used
> MessageContext: 3091202048 total in 380 blocks; 41368 free (34
> chunks); 3091160680 used
> JoinRelHashTable: 1040384 total in 7 blocks; 24336 free (12
> chunks); 1016048 used
> smgr relation table: 24576 total in 2 blocks; 9776 free (4 chunks);
> 14800 used
> TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0
> chunks); 32 used
> Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
> PortalMemory: 8192 total in 1 blocks; 8160 free (1 chunks); 32 used
> Relcache by OID: 24576 total in 2 blocks; 12832 free (3 chunks);
> 11744 used
> CacheMemoryContext: 2549344 total in 23 blocks; 943032 free (1
> chunks); 1606312 used
> users_username_key: 2048 total in 1 blocks; 752 free (0 chunks);
> 1296 used
> users_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
> oevi_1: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
> oe_vessel_imbarco_pkey: 2048 total in 1 blocks; 632 free (0
> chunks); 1416 used
> m_zonmar_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
> m_cianav_pkey: 2048 total in 1 blocks; 656 free (0 chunks); 1392 used
> oev_1: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
> oe_vessel_t_pkey: 2048 total in 1 blocks; 656 free (0 chunks); 1392
> used
> m_aeropu_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
> open_ref_reference_iddept_azienda_key: 2048 total in 1 blocks; 632
> free (0 chunks); 1416 used
> open_ref_pkey: 2048 total in 1 blocks; 632 free (0 chunks); 1416 used
> mcli_nome: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
> mcli_acro: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
> <m_cli_x1>: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
> m_cli_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
> oec_2: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
> oec_1: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
> oe_container_booking_nr_progr_ctnr_azienda_key: 2048 total in 1
> blocks; 632 free (0 chunks); 1416 used
> oe_container_pkey: 2048 total in 1 blocks; 632 free (0 chunks); 1416 used
> oem_x1: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
> oe_sped_m_pkey: 2048 total in 1 blocks; 632 free (0 chunks); 1416 used
> ref_sales_pkey: 2048 total in 1 blocks; 440 free (0 chunks); 1608 used
> oes_x7: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
> oes_x6: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
> oes_x5: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
> oes_x4: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
> oes_x3: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
> oes_x2: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
> oes_x1: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
> oe_sped_t_pkey: 2048 total in 1 blocks; 656 free (0 chunks); 1392 used
> navig_save_pkey: 2048 total in 1 blocks; 656 free (0 chunks); 1392
> used
> navig_fields_pkey: 2048 total in 1 blocks; 632 free (0 chunks);
> 1416 used
> navig_subarea_pkey: 2048 total in 1 blocks; 656 free (0 chunks);
> 1392 used
> navig_area_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296
> used
> navig_left_table_pkey: 2048 total in 1 blocks; 752 free (0 chunks);
> 1296 used
> gnp_cod_tipo_par: 2048 total in 1 blocks; 608 free (0 chunks); 1440
> used
> gen_param_pkey: 2048 total in 1 blocks; 632 free (0 chunks); 1416 used
> glchart_groups_pk_gr: 2048 total in 1 blocks; 752 free (0 chunks);
> 1296 used
> pg_attrdef_oid_index: 2048 total in 1 blocks; 752 free (0 chunks);
> 1296 used
> empresa_pkey: 2048 total in 1 blocks; 704 free (0 chunks); 1344 used
> pg_attrdef_adrelid_adnum_index: 2048 total in 1 blocks; 608 free (0
> chunks); 1440 used
> pg_toast_2619_index: 2048 total in 1 blocks; 608 free (0 chunks);
> 1440 used
> pg_index_indrelid_index: 2048 total in 1 blocks; 704 free (0
> chunks); 1344 used
> pg_ts_dict_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks);
> 1328 used
> pg_aggregate_fnoid_index: 3072 total in 2 blocks; 1696 free (2
> chunks); 1376 used
> pg_language_name_index: 3072 total in 2 blocks; 1744 free (3
> chunks); 1328 used
> pg_statistic_relid_att_index: 3072 total in 2 blocks; 1600 free (2
> chunks); 1472 used
> pg_ts_dict_dictname_index: 3072 total in 2 blocks; 1648 free (2
> chunks); 1424 used
> pg_namespace_nspname_index: 3072 total in 2 blocks; 1696 free (2
> chunks); 1376 used
> pg_opfamily_oid_index: 3072 total in 2 blocks; 1744 free (3
> chunks); 1328 used
> pg_opclass_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks);
> 1376 used
> pg_ts_parser_prsname_index: 3072 total in 2 blocks; 1648 free (2
> chunks); 1424 used
> pg_amop_fam_strat_index: 3072 total in 2 blocks; 1384 free (2
> chunks); 1688 used
> pg_opclass_am_name_nsp_index: 3072 total in 2 blocks; 1576 free (3
> chunks); 1496 used
> pg_trigger_tgrelid_tgname_index: 3072 total in 2 blocks; 1600 free
> (2 chunks); 1472 used
> pg_cast_source_target_index: 3072 total in 2 blocks; 1600 free (2
> chunks); 1472 used
> pg_auth_members_role_member_index: 3072 total in 2 blocks; 1648
> free (2 chunks); 1424 used
> pg_attribute_relid_attnum_index: 3072 total in 2 blocks; 1600 free
> (2 chunks); 1472 used
> pg_ts_config_cfgname_index: 3072 total in 2 blocks; 1648 free (2
> chunks); 1424 used
> pg_authid_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks);
> 1376 used
> pg_ts_config_oid_index: 3072 total in 2 blocks; 1744 free (3
> chunks); 1328 used
> pg_conversion_default_index: 3072 total in 2 blocks; 1432 free (3
> chunks); 1640 used
> pg_language_oid_index: 3072 total in 2 blocks; 1744 free (3
> chunks); 1328 used
> pg_enum_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks);
> 1328 used
> pg_proc_proname_args_nsp_index: 3072 total in 2 blocks; 1576 free
> (3 chunks); 1496 used
> pg_ts_parser_oid_index: 3072 total in 2 blocks; 1744 free (3
> chunks); 1328 used
> pg_database_oid_index: 3072 total in 2 blocks; 1696 free (2
> chunks); 1376 used
> pg_conversion_name_nsp_index: 3072 total in 2 blocks; 1648 free (2
> chunks); 1424 used
> pg_class_relname_nsp_index: 3072 total in 2 blocks; 1600 free (2
> chunks); 1472 used
> pg_attribute_relid_attnam_index: 3072 total in 2 blocks; 1648 free
> (2 chunks); 1424 used
> pg_class_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks);
> 1376 used
> pg_amproc_fam_proc_index: 3072 total in 2 blocks; 1384 free (2
> chunks); 1688 used
> pg_operator_oprname_l_r_n_index: 3072 total in 2 blocks; 1384 free
> (2 chunks); 1688 used
> pg_index_indexrelid_index: 3072 total in 2 blocks; 1696 free (2
> chunks); 1376 used
> pg_type_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks);
> 1376 used
> pg_rewrite_rel_rulename_index: 3072 total in 2 blocks; 1648 free (2
> chunks); 1424 used
> pg_authid_rolname_index: 3072 total in 2 blocks; 1696 free (2
> chunks); 1376 used
> pg_auth_members_member_role_index: 3072 total in 2 blocks; 1600
> free (2 chunks); 1472 used
> pg_enum_typid_label_index: 3072 total in 2 blocks; 1648 free (2
> chunks); 1424 used
> pg_constraint_oid_index: 3072 total in 2 blocks; 1744 free (3
> chunks); 1328 used
> pg_conversion_oid_index: 3072 total in 2 blocks; 1744 free (3
> chunks); 1328 used
> pg_ts_template_tmplname_index: 3072 total in 2 blocks; 1648 free (2
> chunks); 1424 used
> pg_ts_config_map_index: 3072 total in 2 blocks; 1624 free (3
> chunks); 1448 used
> pg_namespace_oid_index: 3072 total in 2 blocks; 1696 free (2
> chunks); 1376 used
> pg_type_typname_nsp_index: 3072 total in 2 blocks; 1600 free (2
> chunks); 1472 used
> pg_operator_oid_index: 3072 total in 2 blocks; 1696 free (2
> chunks); 1376 used
> pg_amop_opr_fam_index: 3072 total in 2 blocks; 1600 free (2
> chunks); 1472 used
> pg_proc_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks);
> 1376 used
> pg_opfamily_am_name_nsp_index: 3072 total in 2 blocks; 1624 free (3
> chunks); 1448 used
> pg_ts_template_oid_index: 3072 total in 2 blocks; 1744 free (3
> chunks); 1328 used
> MdSmgr: 8192 total in 1 blocks; 5760 free (0 chunks); 2432 used
> LOCALLOCK hash: 24576 total in 2 blocks; 15984 free (5 chunks); 8592
> used
> Timezones: 53584 total in 2 blocks; 3744 free (0 chunks); 49840 used
> ErrorContext: 24576 total in 3 blocks; 24480 free (18 chunks); 96 used
>
> Any suggestion higly appreciated
>
> Silvio Brandani
>
>

Postgres version is 8.3.8 x64bit under linux Centos, the driver odbc is
an 8.02.02.

Could be a problem with Postgres ODBC driver version ??

--
Silvio Brandani
Infrastructure Administrator
SDB Information Technology
Phone: +39.055.3811222
Fax: +39.055.5201119

---

Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quando li comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti, sono riportate alla pagina http://www.savinodelbene.com/news/privacy.html
Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/codice_penale_616.html
L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attività lavorativa o contrari a norme.
--

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2010-09-02 14:27:54 Re: out of memory error
Previous Message Silvio Brandani 2010-09-02 12:50:46 Re: out of memory error