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 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

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 =3D 32MB
temp_buffers =3D 8MB
max_prepared_transactions =3D 5
work_mem =3D 1MB
maintenance_work_mem =3D 16MB
max_stack_depth =3D 2MB

[]=B4s

Victor Hugo P.Clemente
Brazil

2010/8/5 Silvio Brandani :
> Hi,
>
> a query on our production database give following errror:
>
>
> 2010-08-05 10:52:40 CEST [12106]: [278-1] ERROR: =A0out of memory
> 2010-08-05 10:52:40 CEST [12106]: [279-1] DETAIL: =A0Failed on request of=
size
> 48.
>
>
>
>
> any suggestion ?
>
> --
> Silvio Brandani
> Infrastructure Administrator
> SDB Information Technology
> Phone: +39.055.3811222
> Fax: =A0 +39.055.5201119
>
> ---
>
>
>
>
>
>
> Utilizziamo i dati personali che la riguardano esclusivamente per nostre
> finalit=E0 amministrative e contabili, anche quando li comunichiamo a ter=
zi.
> 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=E0 giuridica qualora perveng=
ano
> da questo indirizzo messaggi estranei all'attivit=E0 lavorativa o contrar=
i 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
>



--=20
[]=B4s
Victor Hugo

--=20
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 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

--
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

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 =3D 32MB
> temp_buffers =3D 8MB
> max_prepared_transactions =3D 5
> work_mem =3D 1MB
> maintenance_work_mem =3D 16MB
> max_stack_depth =3D 2MB
>
> []=B4s
>
> Victor Hugo P.Clemente
> Brazil
>
> 2010/8/5 Silvio Brandani :
> =20
>> 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 o=
f 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 nost=
re
>> finalit=E0 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 ritornar=
lo al
>> mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 6=
16
>> codice penale http://www.savinodelbene.com/codice_penale_616.html
>> L'Azienda non si assume alcuna responsabilit=E0 giuridica qualora perv=
engano
>> da questo indirizzo messaggi estranei all'attivit=E0 lavorativa o cont=
rari 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
>>
>> =20
>
>
>
> =20
I have tried to increase the parameters but still fail. what is strange=20
is that with psql the query works fine and give result immediatly, with=20
application through odbc the query fail



---






Utilizziamo i dati personali che la riguardano esclusivamente per nostre =
finalit=E0 amministrative e contabili, anche quando li comunichiamo a ter=
zi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e=
agli altri Suoi diritti, sono riportate alla pagina http://www.savinodel=
bene.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. 6=
16 codice penale http://www.savinodelbene.com/codice_penale_616.html
L'Azienda non si assume alcuna responsabilit=E0 giuridica qualora perveng=
ano da questo indirizzo messaggi estranei all'attivit=E0 lavorativa o con=
trari a norme.
--

--=20
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

"Kevin Grittner" writes:
> Silvio Brandani 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

--
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

Tom Lane ha scritto:
> "Kevin Grittner" writes:
>
>> Silvio Brandani 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
: 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 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 writes:
>> "Kevin Grittner" 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

--
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

2010/8/5 Silvio Brandani :
>>
>
> 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.

--
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 ,=20

I had a similar problem when starting the database from an account that did=
n't have the appropriate ulimits set. Check the ulimit values using ulimit=
-a.

HTH,

Bob Lunney

--- On Thu, 8/5/10, Silvio Brandani wrote:

> From: Silvio Brandani
> Subject: [ADMIN] out of memory error
> To: pgsql-admin@postgresql.org
> Date: Thursday, August 5, 2010, 9:01 AM
> Hi,
>=20
> a query on our production database give following errror:
>=20
>=20
> 2010-08-05 10:52:40 CEST [12106]: [278-1] ERROR:=A0 out
> of memory
> 2010-08-05 10:52:40 CEST [12106]: [279-1] DETAIL:=A0
> Failed on request of size 48.
>=20
>=20
>=20
>=20
> any suggestion ?
>=20
> -- Silvio Brandani
> Infrastructure Administrator
> SDB Information Technology
> Phone: +39.055.3811222
> Fax:  =A0+39.055.5201119
>=20
> ---
>=20
>=20
>=20
>=20
>=20
>=20
> Utilizziamo i dati personali che la riguardano
> esclusivamente per nostre finalit=E0 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.savinodelb=
ene.com/codice_penale_616.html
> L'Azienda non si assume alcuna responsabilit=E0 giuridica
> qualora pervengano da questo indirizzo messaggi estranei
> all'attivit=E0 lavorativa o contrari a norme.
> --
>=20
> -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>=20




--=20
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

Bob Lunney ha scritto:
> Silvio ,=20
>
> 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 wrote=
:
>
> =20
>> From: Silvio Brandani
>> 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:=20
>> 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=E0 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.savinod=
elbene.com/codice_penale_616.html
>> L'Azienda non si assume alcuna responsabilit=E0 giuridica
>> qualora pervengano da questo indirizzo messaggi estranei
>> all'attivit=E0 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
>>
>> =20
>
>
> =20
>
> =20
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=E0 amministrative e contabili, anche quando li comunichiamo a ter=
zi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e=
agli altri Suoi diritti, sono riportate alla pagina http://www.savinodel=
bene.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. 6=
16 codice penale http://www.savinodelbene.com/codice_penale_616.html
L'Azienda non si assume alcuna responsabilit=E0 giuridica qualora perveng=
ano da questo indirizzo messaggi estranei all'attivit=E0 lavorativa o con=
trari a norme.
--

--=20
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 ha scritto:
> Bob Lunney ha scritto:
>> Silvio ,
>> I had a similar problem when starting the database from an account=20
>> that didn't have the appropriate ulimits set. Check the ulimit=20
>> values using ulimit -a.
>>
>> HTH,
>>
>> Bob Lunney
>>
>> --- On Thu, 8/5/10, Silvio Brandani wrot=
e:
>>
>> =20
>>> From: Silvio Brandani
>>> 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=20
>>> 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=E0 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=20
>>> http://www.savinodelbene.com/codice_penale_616.html
>>> L'Azienda non si assume alcuna responsabilit=E0 giuridica
>>> qualora pervengano da questo indirizzo messaggi estranei
>>> all'attivit=E0 lavorativa o contrari a norme.
>>> --=20
>>>
>>> -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-admin
>>>
>>> =20
>>
>>
>> =20
>> =20
> 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=20
the application versus the psql . How can I check the execution plan of=20
a query run by a user??
I can set explain analyze for the query via psql but how can I check=20
with application running the query???

Thanks

---






Utilizziamo i dati personali che la riguardano esclusivamente per nostre =
finalit=E0 amministrative e contabili, anche quando li comunichiamo a ter=
zi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e=
agli altri Suoi diritti, sono riportate alla pagina http://www.savinodel=
bene.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. 6=
16 codice penale http://www.savinodelbene.com/codice_penale_616.html
L'Azienda non si assume alcuna responsabilit=E0 giuridica qualora perveng=
ano da questo indirizzo messaggi estranei all'attivit=E0 lavorativa o con=
trari a norme.
--

--=20
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

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=20
> the application versus the psql . How can I check the execution plan of=
=20
> a query run by a user??
> I can set explain analyze for the query via psql but how can I check=20
> 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 =3D $1 A=
ND ...
EXPLAIN ANALYZE EXECUTE foo(someval, another);

--=20
Álvaro Herrera
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin