Обсуждение: 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. --