Problems with PostGreSQL ODBC and Windows 2003 Server
am 25.11.2007 19:01:13 von claudia.amorimHello,
I'm having serious problems with PostGreSQL and Windows Server 2003
Enterprise Edition. The PostgreSQL Server doesn't start if I set the shar=
ed
buffers higher than 1GB. All my programs can use only 3 GB of RAM and I h=
ave 8GB
of RAM.
When I monitor the processes I can see that PostGreSQL allocs only 700 MB=
of
memory, and
my application 2GB. Total: 3GB.
When I try to execute a query in a table about 4 milion registers, my
application crashes with an error message "Out of memory" or
"invalid sql statement". But the sql statement is ok - if I execute it
in a table with less registers, it works and it is very simple. I'm using=
a cursor
via ODBC.
My program was made in Delphi 2006, and I use ADO via ODBC to connect to
PostGreSQL.
The configuration:
PostGreSQL 8.2.5
O.S: Windows Server 2003 Enterprise Edition
Service Pack 2
Computer:
dual quad core Intel(R) Xeon(R) CPU E5345 @ 2.33GHz
8GB of RAM
Physical Address Extension
3 HDs in RAID-5
My boot.ini:
[boot loader]
timeout=3D30
default=3Dmulti(0)disk(0)rdisk(0)partition(2)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS=3D"Windows Server 2003, Enter=
prise"
/fastdetect /PAE /NoExecute=3DOptOut /3GB
PostGreSQL.conf:
shared_buffers =3D 1024MB # min 128kB or max_conne=
ctions*16kB
# (change requires restart)
temp_buffers =3D 32MB # min 800kB
#max_prepared_transactions =3D 5 # can be 0 or more
# (change requires restart)
# Note: increasing max_prepared_transactions costs ~600 bytes of shared m=
emory
# per transaction slot, plus lock space (see max_locks_per_transaction).
work_mem =3D16MB # min 64kB
maintenance_work_mem =3D 256MB # min 1MB
max_stack_depth =3D 2MB # min 100kB
# - Free Space Map -
max_fsm_pages =3D 409600 # min max_fsm_relations*16, 6 byt=
es each
# (change requires restart)
#max_fsm_relations =3D 1000 # min 100, ~70 bytes each
# (change requires restart)
#----------------------------------------------------------- -------------=
---
# WRITE AHEAD LOG
#----------------------------------------------------------- -------------=
---
# - Checkpoints -
checkpoint_segments =3D 128 # in logfile segments, min 1, =
16MB each
checkpoint_timeout =3D 15min # range 30s-1h
checkpoint_warning =3D 30s # 0 is off
#----------------------------------------------------------- -------------=
---
# QUERY TUNING
#----------------------------------------------------------- -------------=
---
effective_cache_size =3D 5120MB
The structure of my table:
CREATE TABLE "public"."fato_financeiro" (
"CODCLI" VARCHAR(6),
"PREST" VARCHAR(4) NOT NULL,
"NUMTRANSVENDA" VARCHAR(10) NOT NULL,
"RECNUM" VARCHAR(8) NOT NULL,
"CODFORNEC" VARCHAR(8),
"TIPO" VARCHAR(2),
"NUMDOC" VARCHAR(10),
"PREST_1" VARCHAR(4),
"VALOR" DOUBLE PRECISION,
"DTEMISSAO" TIMESTAMP WITH TIME ZONE,
"DTVENC" TIMESTAMP WITH TIME ZONE,
"DTPAG" TIMESTAMP WITH TIME ZONE,
"VPAGO" DOUBLE PRECISION,
"PAGO_PAG" VARCHAR(9),
"ATRASADO" VARCHAR(3),
CONSTRAINT "fato_financeiro_idx" PRIMARY KEY("PREST", "NUMTRANSVENDA", =
"RECNUM")
) WITHOUT OIDS;
SQL statement:
select
fato_financeiro."TIPO",
fato_financeiro."NUMDOC",
fato_financeiro."PREST",
fato_financeiro."NUMDOC",
fato_financeiro."DTVENC",
fato_financeiro."DTPAG",
fato_financeiro."PAGO_PAG",
fato_financeiro."ATRASADO",
fato_financeiro."CODCLI",
fato_financeiro."CODFORNEC",
fato_financeiro."DTEMISSAO"
from fato_financeiro
And here is a piece of psqlodbc log file:
[0.000]conn=3D02DE3A70, PGAPI_DriverConnect(
in)=3D'DSN=3DBI;UID=3Dbiuser;PWD=3Dxxxxxxxxx;', fDriverCompletion=3D0
[0.000]DSN info:
DSN=3D'BI',server=3D'localhost',port=3D'5432',dbase=3D'BI',u ser=3D'biuser=
',passwd=3D'xxxxx'
[0.000]
onlyread=3D'0',protocol=3D'7.4',showoid=3D'0',fakeoidindex=3 D'0',showsyst=
able=3D'0'
[0.000] conn_settings=3D'',conn_encoding=3D'(null)'
[0.000] translation_dll=3D'',translation_option=3D''
[0.000]Driver Version=3D'08.02.0400,200704270001' linking static Multithr=
ead library
[0.000]Global Options: fetch=3D100, socket=3D4096, unknown_sizes=3D0,
max_varchar_size=3D255, max_longvarchar_size=3D8190
[0.000] disable_optimizer=3D0, ksqo=3D1, unique_index=3D1,
use_declarefetch=3D1
[0.000] text_as_longvarchar=3D1, unknowns_as_longvarchar=3D=
0,
bools_as_char=3D1 NAMEDATALEN=3D64
[0.000] extra_systable_prefixes=3D'dd_;', conn_settings=3D=
''
conn_encoding=3D''
[0.046] [ PostgreSQL version string =3D '8.2.5' ]
[0.046] [ PostgreSQL version number =3D '8.2' ]
[0.046]conn=3D02DE3A70, query=3D'select oid, typbasetype from pg_type whe=
re typname
=3D 'lo''
[0.046]NOTICE from backend during send_query: 'SLOG'
[0.046]NOTICE from backend during send_query: 'C00000'
[0.046]NOTICE from backend during send_query: 'Mstatement: select oid,
typbasetype from pg_type where typname =3D 'lo''
[0.046]NOTICE from backend during send_query: 'Fpostgres.c'
[0.046]NOTICE from backend during send_query: 'L811'
[0.046]NOTICE from backend during send_query: 'Rexec_simple_query'
[0.046] [ fetched 1 rows ]
[0.046] [ Large Object oid =3D 17288 ]
[0.046] [ Client encoding =3D 'LATIN9' (code =3D 16) ]
[0.046]conn=3D02DE3A70,
PGAPI_DriverConnect(out)=3D'DSN=3DBI;DATABASE=3DBI;SERVER=3D localhost;POR=
T=3D5432;UID=3Dbiuser;PWD=3Dxxxxxxxxx;SSLmode=3Ddisable;Read Only=3D0;Prot=
ocol=3D7.4-1;FakeOidIndex=3D0;ShowOidColumn=3D0;RowVersionin g=3D0;ShowSys=
temTables=3D0;ConnSettings=3D;Fetch=3D100;Socket=3D4096;Unkn ownSizes=3D0;=
MaxVarcharSize=3D255;MaxLongVarcharSize=3D8190;Debug=3D0;Com mLog=3D1;Opti=
mizer=3D0;Ksqo=3D1;UseDeclareFetch=3D1;TextAsLongVarchar=3D1 ;UnknownsAsLo=
ngVarchar=3D0;BoolsAsChar=3D1;Parse=3D0;CancelAsFreeStmt=3D0 ;ExtraSysTabl=
ePrefixes=3Ddd_;;LFConversion=3D1;UpdatableCursors=3D1;Disal lowPremature=3D=
0;TrueIsMinus1=3D0;BI=3D0;ByteaAsLongVarBinary=3D0;UseServer SidePrepare=3D=
0;LowerCaseIdentifier=3D0;XaOpt=3D1'
[0.062]STATEMENT ERROR: func=3Dset_statement_option, desc=3D'', errnum=3D=
30,
errmsg=3D'The option may be for MS SQL Server(Set)'
[0.062]
------------------------------------------------------------
[0.062] hdbc=3D02DE3A70, stmt=3D02DE85C8, result=3D000000=
00
[0.062] prepare=3D0, internal=3D0
[0.062] bindings=3D00000000, bindings_allocated=3D0
[0.062] parameters=3D02DE8F48, parameters_allocated=3D1
[0.062] statement_type=3D-2, statement=3D'(NULL)'
[0.062] stmt_with_params=3D'(NULL)'
[0.062] data_at_exec=3D-1, current_exec_param=3D-1, put_d=
ata=3D0
[0.062] currTuple=3D-1, current_col=3D-1, lobj_fd=3D-1
[0.062] maxRows=3D0, rowset_size=3D1, keyset_size=3D0, cu=
rsor_type=3D0,
scroll_concurrency=3D1
[0.062] cursor_name=3D''
[0.062] ----------------QResult Info
-------------------------------
[0.062]CONN ERROR: func=3Dset_statement_option, desc=3D'', errnum=3D0, er=
rmsg=3D'(NULL)'
[0.062]
Thanks,
Cl=E1udia.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match