pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedOut of Memory postgres
3+ messages / 2 participants
[nested] [flat]
* Out of Memory postgres
@ 2011-01-14 09:43 Silvio Brandani <silvio.brandani@tech.sdb.it>
0 siblings, 1 reply; 3+ messages in thread
From: Silvio Brandani @ 2011-01-14 09:43 UTC (permalink / raw)
To: pgsql-admin
Still problems of Out of Memory:
the query is the following and if I run it from psql is working fine,
but from application (through ODBC) I get error ,
I try to change odbc drivers (version 8.x to 9.x) and postgres version
(8.3.x to 9.x , Linux platform ) but the error appear in all versions.
I try also to modify flags odbc declare/fetch but noways.
The erros is the following, I just see an high MessageContext values-
Any help higly appreciated.
:
LOG: statement: declare SQL_CUR02B79258 cursor for 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(oec.load_date) as Load_date,MAX(oec.disch_date) as
Discharge_date,MAX(oec.seal1) ::char(21) as Seal,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 '10/1/2010' AND '10/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
TopMemoryContext: 57608 total in 7 blocks; 4752 free (16 chunks); 52856 used
TopTransactionContext: 8192 total in 1 blocks; 7816 free (0 chunks);
376 used
Type information cache: 8192 total in 1 blocks; 1800 free (0 chunks);
6392 used
Operator class cache: 8192 total in 1 blocks; 3848 free (0 chunks);
4344 used
Operator lookup cache: 24576 total in 2 blocks; 14072 free (6
chunks); 10504 used
MessageContext: 1590689792 total in 211 blocks; 8496 free (19
chunks); 1590681296 used
JoinRelHashTable: 1040384 total in 7 blocks; 413400 free (12
chunks); 626984 used
smgr relation table: 8192 total in 1 blocks; 744 free (0 chunks);
7448 used
TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0
chunks); 16 used
Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
PortalMemory: 8192 total in 1 blocks; 8176 free (2 chunks); 16 used
Relcache by OID: 8192 total in 1 blocks; 2336 free (0 chunks); 5856 used
CacheMemoryContext: 1341144 total in 22 blocks; 68608 free (1
chunks); 1272536 used
users_username_key: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
users_pkey: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
oevi_1: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
oe_vessel_imbarco_pkey: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
m_zonmar_pkey: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
m_cianav_pkey: 1024 total in 1 blocks; 280 free (0 chunks); 744 used
oev_1: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
oe_vessel_t_pkey: 1024 total in 1 blocks; 280 free (0 chunks); 744 used
m_aeropu_pkey: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
open_ref_reference_iddept_azienda_key: 1024 total in 1 blocks; 192
free (0 chunks); 832 used
open_ref_pkey: 1024 total in 1 blocks; 192 free (0 chunks); 832 used
mcli_nome: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
mcli_acro: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
<m_cli_x1>: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
m_cli_pkey: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
oec_2: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
oec_1: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
oe_container_booking_nr_progr_ctnr_azienda_key: 1024 total in 1
blocks; 192 free (0 chunks); 832 used
oe_container_pkey: 1024 total in 1 blocks; 192 free (0 chunks); 832
used
oem_x1: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
oe_sped_m_pkey: 1024 total in 1 blocks; 192 free (0 chunks); 832 used
ref_sales_pkey: 1024 total in 1 blocks; 128 free (0 chunks); 896 used
oes_x7: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
oes_x6: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
oes_x5: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
oes_x4: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
oes_x3: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
oes_x2: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
oes_x1: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
oe_sped_t_pkey: 1024 total in 1 blocks; 280 free (0 chunks); 744 used
navig_save_pkey: 1024 total in 1 blocks; 152 free (0 chunks); 872 used
navig_fields_pkey: 1024 total in 1 blocks; 192 free (0 chunks); 832
used
navig_subarea_pkey: 1024 total in 1 blocks; 240 free (0 chunks);
784 used
navig_area_pkey: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
navig_left_table_pkey: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
gnp_cod_tipo_par: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
gen_param_pkey: 1024 total in 1 blocks; 192 free (0 chunks); 832 used
glchart_groups_pk_gr: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
empresa_pkey: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_index_indrelid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_ts_dict_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_language_name_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_ts_dict_dictname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_opfamily_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
pg_opclass_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_ts_parser_prsname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_amop_fam_strat_index: 1024 total in 1 blocks; 88 free (0
chunks); 936 used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 240 free
(0 chunks); 784 used
pg_cast_source_target_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 280 free
(0 chunks); 744 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 240 free
(0 chunks); 784 used
pg_ts_config_cfgname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_authid_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_ts_config_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_conversion_default_index: 1024 total in 1 blocks; 128 free (0
chunks); 896 used
pg_language_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
pg_enum_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
pg_ts_parser_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_database_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 280 free
(0 chunks); 744 used
pg_class_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_amproc_fam_proc_index: 1024 total in 1 blocks; 88 free (0
chunks); 936 used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 88 free (0
chunks); 936 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_type_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_authid_rolname_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 240 free
(0 chunks); 784 used
pg_enum_typid_label_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_constraint_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_conversion_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_ts_template_tmplname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_ts_config_map_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_namespace_oid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_operator_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_amop_opr_fam_index: 1024 total in 1 blocks; 240 free (0 chunks);
784 used
pg_proc_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_opfamily_am_name_nsp_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_ts_template_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
MdSmgr: 8192 total in 1 blocks; 6664 free (0 chunks); 1528 used
LOCALLOCK hash: 8192 total in 1 blocks; 1856 free (0 chunks); 6336 used
Timezones: 48616 total in 2 blocks; 5968 free (0 chunks); 42648 used
ErrorContext: 24576 total in 3 blocks; 24528 free (22 chunks); 48 used
ERROR: out of memory
DETAIL: Failed on request of size 16.
ERROR: current transaction is aborted, commands ignored until end of
transaction block
STATEMENT: close SQL_CUR02B79258
LOG: statement: ROLLBACK
--
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/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/privacy/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.
--
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Out of Memory postgres
@ 2011-01-14 20:34 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Silvio Brandani <silvio.brandani@tech.sdb.it>
0 siblings, 1 reply; 3+ messages in thread
From: Kevin Grittner @ 2011-01-14 20:34 UTC (permalink / raw)
To: pgsql-admin; Silvio Brandani <silvio.brandani@tech.sdb.it>
Silvio Brandani <silvio.brandani@tech.sdb.it> wrote:
> I try to change odbc drivers (version 8.x to 9.x) and postgres
> version (8.3.x to 9.x , Linux platform ) but the error appear in
> all versions.
> MessageContext: 1590689792 total in 211 blocks; 8496 free (19
> chunks); 1590681296 used
Exact versions might be significant. Also, locale information,
particularly character set and encoding, at each layer may be
significant. I seem to remember some problems with recursive calls
within error handling when there were byte sequences which didn't
work in the encoding. There has been work to fix that in the
PostgreSQL backend, but I don't know about the ODBC driver, and if
you're not using the latest version, you might be missing a critical
fix.
http://www.postgresql.org/support/versioning
-Kevin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Out of Memory postgres
@ 2011-01-17 09:56 Silvio Brandani <silvio.brandani@tech.sdb.it>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 3+ messages in thread
From: Silvio Brandani @ 2011-01-17 09:56 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-admin
Il 14/01/2011 21.34, Kevin Grittner ha scritto:
> Silvio Brandani<silvio.brandani@tech.sdb.it> wrote:
>
>> I try to change odbc drivers (version 8.x to 9.x) and postgres
>> version (8.3.x to 9.x , Linux platform ) but the error appear in
>> all versions.
>
>> MessageContext: 1590689792 total in 211 blocks; 8496 free (19
>> chunks); 1590681296 used
>
> Exact versions might be significant. Also, locale information,
> particularly character set and encoding, at each layer may be
> significant. I seem to remember some problems with recursive calls
> within error handling when there were byte sequences which didn't
> work in the encoding. There has been work to fix that in the
> PostgreSQL backend, but I don't know about the ODBC driver, and if
> you're not using the latest version, you might be missing a critical
> fix.
>
> http://www.postgresql.org/support/versioning
>
> -Kevin
>
Current version is 8.3.6 but if give error also with 9.0.1 server version.
Regarding the ODBC driver we test all last versions (9.0.2, 8.4.0.2) .
The encoding used is UTF8 users work on a Turkish language.
--
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/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/privacy/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.
--
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-01-17 09:56 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-01-14 09:43 Out of Memory postgres Silvio Brandani <silvio.brandani@tech.sdb.it>
2011-01-14 20:34 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-01-17 09:56 ` Silvio Brandani <silvio.brandani@tech.sdb.it>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox