Обсуждение: out of memory error
Hi, a query on our production database give following errror: 2010-08-05 10:52:40 CEST [12106]: [278-1] ERROR: out of memory 2010-08-05 10:52:40 CEST [12106]: [279-1] DETAIL: Failed on request of size 48. any suggestion ? -- 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 quandoli 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 eventualiallegati, 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. --
Hi Silvio, I don't know if this is relevant. But, work_mem and some other parameters inside postgresql.conf are not set. Here is a portion of the file: shared_buffers = 32MB temp_buffers = 8MB max_prepared_transactions = 5 work_mem = 1MB maintenance_work_mem = 16MB max_stack_depth = 2MB []´s Victor Hugo P.Clemente Brazil 2010/8/5 Silvio Brandani <silvio.brandani@tech.sdb.it>: > Hi, > > a query on our production database give following errror: > > > 2010-08-05 10:52:40 CEST [12106]: [278-1] ERROR: out of memory > 2010-08-05 10:52:40 CEST [12106]: [279-1] DETAIL: Failed on request of size > 48. > > > > > any suggestion ? > > -- > 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. > -- > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > -- []´s Victor Hugo
Silvio Brandani <silvio.brandani@tech.sdb.it> wrote: > a query on our production database give following errror: > > 2010-08-05 10:52:40 CEST [12106]: [278-1] ERROR: out of memory > 2010-08-05 10:52:40 CEST [12106]: [279-1] DETAIL: Failed on > request of size 48. What query? On what OS? Is this a 32-bit or 64-bit build of PostgreSQL? How long does it run before failing. What does memory usage look like before and during the run? (Sample of `vmstat 1` at different points are good, if your OS supports that.) > any suggestion ? Read this page and post again with more detail: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems -Kevin
Victor Hugo ha scritto: > Hi Silvio, > > I don't know if this is relevant. But, work_mem and some other > parameters inside postgresql.conf are not set. Here is a portion of > the file: > > shared_buffers = 32MB > temp_buffers = 8MB > max_prepared_transactions = 5 > work_mem = 1MB > maintenance_work_mem = 16MB > max_stack_depth = 2MB > > []´s > > Victor Hugo P.Clemente > Brazil > > 2010/8/5 Silvio Brandani <silvio.brandani@tech.sdb.it>: > >> Hi, >> >> a query on our production database give following errror: >> >> >> 2010-08-05 10:52:40 CEST [12106]: [278-1] ERROR: out of memory >> 2010-08-05 10:52:40 CEST [12106]: [279-1] DETAIL: Failed on request of size >> 48. >> >> >> >> >> any suggestion ? >> >> -- >> 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. >> -- >> >> -- >> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-admin >> >> > > > > I have tried to increase the parameters but still fail. what is strange is that with psql the query works fine and give result immediatly, with application through odbc the query fail --- Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quandoli 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 eventualiallegati, 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. --
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > Silvio Brandani <silvio.brandani@tech.sdb.it> wrote: >> a query on our production database give following errror: >> >> 2010-08-05 10:52:40 CEST [12106]: [278-1] ERROR: out of memory >> 2010-08-05 10:52:40 CEST [12106]: [279-1] DETAIL: Failed on >> request of size 48. > What query? On what OS? Is this a 32-bit or 64-bit build of > PostgreSQL? How long does it run before failing. What does memory > usage look like before and during the run? Also, out-of-memory should result in a memory usage map getting dumped to the postmaster log. That would be useful to see too. regards, tom lane
Tom Lane ha scritto: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > >> Silvio Brandani <silvio.brandani@tech.sdb.it> wrote: >> >>> a query on our production database give following errror: >>> >>> 2010-08-05 10:52:40 CEST [12106]: [278-1] ERROR: out of memory >>> 2010-08-05 10:52:40 CEST [12106]: [279-1] DETAIL: Failed on >>> request of size 48. >>> > > >> What query? On what OS? Is this a 32-bit or 64-bit build of >> PostgreSQL? How long does it run before failing. What does memory >> usage look like before and during the run? >> > > Also, out-of-memory should result in a memory usage map getting dumped > to the postmaster log. That would be useful to see too. > > regards, tom lane > > TopMemoryContext: 178680 total in 14 blocks; 7312 free (16 chunks); 171368 used TopTransactionContext: 8192 total in 1 blocks; 7568 free (0 chunks); 624 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: 3409969152 total in 417 blocks; 17496 free (10 chunks); 3409951656 used JoinRelHashTable: 2088960 total in 8 blocks; 851696 free (15 chunks); 1237264 used smgr relation table: 24576 total in 2 blocks; 11840 free (4 chunks); 12736 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; 1004136 free (0 chunks); 1545208 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_tipmer_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 mmerca_cod_emb: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used m_merca_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used m_aeropu_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 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_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; 5792 free (0 chunks); 2400 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 2010-08-05 15:20:00 CEST [10349]: [262-1] ERROR: out of memory 2010-08-05 15:20:00 CEST [10349]: [263-1] DETAIL: Failed on request of size 16. The query: BEGIN;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(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(tipmer.descripcio) ::char(31) as Comm_Group,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.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,m_cli fab,m_cli agent,m_aeropu aer_l,m_aeropu aer_d,m_merca merca,oe_vessel_t oev,m_cianav cia,m_cianav cia2,m_tipmer tipmer,m_zonmar zmar,m_zonmar zmar2,m_zonmar zmar3,m_zonmar zmar4,oe_vessel_imbarco oevi 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 '7/1/2010' AND '7/31/2010' AND oes.cod_des = cons.codigo 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.tip_mer = merca.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 merca.grupo=tipmer.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 GROUP BY oes.azienda,oes.booking_nr,oem.progr_ctnr linux cento5 64 bit with 8G ram . when run teh query the cpu go to 100% and the memory go high to 8G then the out of memory Silvio B. --- Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quandoli 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 eventualiallegati, 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. --
Silvio Brandani <silvio.brandani@tech.sdb.it> writes: >> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >>> What query? [ query with aggregates and GROUP BY ] Does EXPLAIN show that it's trying to use a hash aggregation plan? If so, try turning off enable_hashagg. I think the hash table might be ballooning far past the number of entries the planner expected. Do you have an idea how many groups there should be in the query result? regards, tom lane
2010/8/5 Silvio Brandani <silvio.brandani@tech.sdb.it>: >> > > I have tried to increase the parameters but still fail. what is strange is > that with psql the query works fine and give result immediatly, with > application through odbc the query fail That's usually the opposite of what you want to do here.
Silvio , I had a similar problem when starting the database from an account that didn't have the appropriate ulimits set. Check theulimit values using ulimit -a. HTH, Bob Lunney --- On Thu, 8/5/10, Silvio Brandani <silvio.brandani@tech.sdb.it> wrote: > From: Silvio Brandani <silvio.brandani@tech.sdb.it> > Subject: [ADMIN] out of memory error > To: pgsql-admin@postgresql.org > Date: Thursday, August 5, 2010, 9:01 AM > Hi, > > a query on our production database give following errror: > > > 2010-08-05 10:52:40 CEST [12106]: [278-1] ERROR: out > of memory > 2010-08-05 10:52:40 CEST [12106]: [279-1] DETAIL: > Failed on request of size 48. > > > > > any suggestion ? > > -- 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. > -- > > -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin >
Bob Lunney ha scritto: > Silvio , > > I had a similar problem when starting the database from an account that didn't have the appropriate ulimits set. Checkthe ulimit values using ulimit -a. > > HTH, > > Bob Lunney > > --- On Thu, 8/5/10, Silvio Brandani <silvio.brandani@tech.sdb.it> wrote: > > >> From: Silvio Brandani <silvio.brandani@tech.sdb.it> >> Subject: [ADMIN] out of memory error >> To: pgsql-admin@postgresql.org >> Date: Thursday, August 5, 2010, 9:01 AM >> Hi, >> >> a query on our production database give following errror: >> >> >> 2010-08-05 10:52:40 CEST [12106]: [278-1] ERROR: out >> of memory >> 2010-08-05 10:52:40 CEST [12106]: [279-1] DETAIL: >> Failed on request of size 48. >> >> >> >> >> any suggestion ? >> >> -- 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. >> -- >> >> -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-admin >> >> > > > > > I have the following set: ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited max nice (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 71679 max locked memory (kbytes, -l) 32 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 max rt priority (-r) 0 stack size (kbytes, -s) 10240 cpu time (seconds, -t) unlimited max user processes (-u) 71679 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited Silvio B --- Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quandoli 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 eventualiallegati, 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. --
Silvio Brandani ha scritto: > Bob Lunney ha scritto: >> Silvio , >> I had a similar problem when starting the database from an account >> that didn't have the appropriate ulimits set. Check the ulimit >> values using ulimit -a. >> >> HTH, >> >> Bob Lunney >> >> --- On Thu, 8/5/10, Silvio Brandani <silvio.brandani@tech.sdb.it> wrote: >> >> >>> From: Silvio Brandani <silvio.brandani@tech.sdb.it> >>> Subject: [ADMIN] out of memory error >>> To: pgsql-admin@postgresql.org >>> Date: Thursday, August 5, 2010, 9:01 AM >>> Hi, >>> >>> a query on our production database give following errror: >>> >>> >>> 2010-08-05 10:52:40 CEST [12106]: [278-1] ERROR: out >>> of memory >>> 2010-08-05 10:52:40 CEST [12106]: [279-1] DETAIL: Failed on request >>> of size 48. >>> >>> >>> >>> >>> any suggestion ? >>> >>> -- 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. >>> -- >>> >>> -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-admin >>> >>> >> >> >> >> > I have the following set: > > ulimit -a > core file size (blocks, -c) 0 > data seg size (kbytes, -d) unlimited > max nice (-e) 0 > file size (blocks, -f) unlimited > pending signals (-i) 71679 > max locked memory (kbytes, -l) 32 > max memory size (kbytes, -m) unlimited > open files (-n) 1024 > pipe size (512 bytes, -p) 8 > POSIX message queues (bytes, -q) 819200 > max rt priority (-r) 0 > stack size (kbytes, -s) 10240 > cpu time (seconds, -t) unlimited > max user processes (-u) 71679 > virtual memory (kbytes, -v) unlimited > file locks (-x) unlimited > > Silvio B > it seems the execution plan is different for this query when run from the application versus the psql . How can I check the execution plan of a query run by a user?? I can set explain analyze for the query via psql but how can I check with application running the query??? Thanks --- Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quandoli 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 eventualiallegati, 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. --
Excerpts from Silvio Brandani's message of vie ago 06 07:56:53 -0400 2010: > it seems the execution plan is different for this query when run from > the application versus the psql . How can I check the execution plan of > a query run by a user?? > I can set explain analyze for the query via psql but how can I check > with application running the query??? If this is a prepared query, then that is a good guess. You can see the real plan that the application is getting in psql by explaining the execution of a prepared statement like this: PREPARE foo(int, text) AS SELECT blah FROM foo, bar WHERE foo.id = $1 AND ... EXPLAIN ANALYZE EXECUTE foo(someval, another); -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
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 --- Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quandoli 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 eventualiallegati, 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. --
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 quandoli 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 eventualiallegati, 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. --
Silvio Brandani <silvio.brandani@tech.sdb.it> writes: >> 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 : Is it really the *exact* same query both ways, or are you doing something like parameterizing the query in the application? regards, tom lane
Tom Lane ha scritto: > Silvio Brandani <silvio.brandani@tech.sdb.it> writes: > >>> 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 : >>> > > Is it really the *exact* same query both ways, or are you doing > something like parameterizing the query in the application? > > regards, tom lane > > Is it exactly the same, the query text is from the postgres log. I just try it in test environment and we have same situazione : psql it works, from application (odbc) do not. thanks -- 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 quandoli 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 eventualiallegati, 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. --
Silvio Brandani <silvio.brandani@tech.sdb.it> writes: > Tom Lane ha scritto: >> Is it really the *exact* same query both ways, or are you doing >> something like parameterizing the query in the application? > Is it exactly the same, the query text is from the postgres log. > I just try it in test environment and we have same situazione : psql it > works, from application (odbc) do not. Hm, there's got to be something different between the two cases. Maybe the odbc application is issuing some SET commands that change the chosen plan? regards, tom lane
Tom Lane ha scritto: > Silvio Brandani <silvio.brandani@tech.sdb.it> writes: > >> Tom Lane ha scritto: >> >>> Is it really the *exact* same query both ways, or are you doing >>> something like parameterizing the query in the application? >>> > > >> Is it exactly the same, the query text is from the postgres log. >> I just try it in test environment and we have same situazione : psql it >> works, from application (odbc) do not. >> > > Hm, there's got to be something different between the two cases. > Maybe the odbc application is issuing some SET commands that change > the chosen plan? > > regards, tom lane > > Is it possible to check the query plan of an odbc application ?? maybe tracing in the logfile or something else , I know the plan of the query I run on psql that works fine but I don't know the plan of the query with out of memory. Thanks a lot --- Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quandoli 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 eventualiallegati, 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. --
Tom Lane ha scritto: > Silvio Brandani <silvio.brandani@tech.sdb.it> writes: > >> Tom Lane ha scritto: >> >>> Is it really the *exact* same query both ways, or are you doing >>> something like parameterizing the query in the application? >>> > > >> Is it exactly the same, the query text is from the postgres log. >> I just try it in test environment and we have same situazione : psql it >> works, from application (odbc) do not. >> > > Hm, there's got to be something different between the two cases. > Maybe the odbc application is issuing some SET commands that change > the chosen plan? > > regards, tom lane > > I trace all the sql executed by application in the logfile then executed in psql and it works. -- 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 quandoli 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 eventualiallegati, 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. --