Обсуждение: out of memory error

Поиск
Список
Период
Сортировка

out of memory error

От
Silvio Brandani
Дата:
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. 
--

Re: out of memory error

От
Victor Hugo
Дата:
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

Re: out of memory error

От
"Kevin Grittner"
Дата:
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

Re: out of memory error

От
Silvio Brandani
Дата:
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. 
--

Re: out of memory error

От
Tom Lane
Дата:
"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

Re: out of memory error

От
Silvio Brandani
Дата:
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. 
--

Re: out of memory error

От
Tom Lane
Дата:
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

Re: out of memory error

От
Scott Marlowe
Дата:
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.

Re: out of memory error

От
Bob Lunney
Дата:
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
>




Re: out of memory error

От
Silvio Brandani
Дата:
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. 
--

Re: out of memory error

От
Silvio Brandani
Дата:
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. 
--

Re: out of memory error

От
Alvaro Herrera
Дата:
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

Re: out of memory error

От
Silvio Brandani
Дата:
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. 
--

Re: out of memory error

От
Silvio Brandani
Дата:
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. 
--

Re: out of memory error

От
Tom Lane
Дата:
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

Re: out of memory error

От
Silvio Brandani
Дата:
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. 
--

Re: out of memory error

От
Tom Lane
Дата:
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

Re: out of memory error

От
Silvio Brandani
Дата:
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. 
--

Re: out of memory error

От
Silvio Brandani
Дата:
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. 
--