Problems with PostGreSQL ODBC and Windows 2003 Server

[claudia(dot)amorim(at)pcinformatica(dot)com(dot)br]

Hello,



I'm having serious problems with PostGreSQL and Windows Server 2003
Enterprise Edition. The PostgreSQL Server doesn't start if I set the shared
buffers higher than 1GB. All my programs can use only 3 GB of RAM and I have 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=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Enterprise"
/fastdetect /PAE   /NoExecute=OptOut /3GB


PostGreSQL.conf:


shared_buffers = 1024MB                        # min 128kB or max_connections*16kB
                                        # (change requires restart)
temp_buffers = 32MB                        # min 800kB
#max_prepared_transactions = 5                # can be 0 or more
                                        # (change requires restart)
# Note: increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
work_mem =16MB                                # min 64kB
maintenance_work_mem = 256MB                # min 1MB
max_stack_depth = 2MB                        # min 100kB

# - Free Space Map -

max_fsm_pages = 409600                # min max_fsm_relations*16, 6 bytes each
                                        # (change requires restart)
#max_fsm_relations = 1000                # min 100, ~70 bytes each
                                        # (change requires restart)



#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------


# - Checkpoints -

checkpoint_segments = 128                # in logfile segments, min 1, 16MB each
checkpoint_timeout = 15min                # range 30s-1h
checkpoint_warning = 30s                # 0 is off




#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------


effective_cache_size = 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=02DE3A70, PGAPI_DriverConnect(
in)='DSN=BI;UID=biuser;PWD=xxxxxxxxx;', fDriverCompletion=0
[0.000]DSN info:
DSN='BI',server='localhost',port='5432',dbase='BI',user='biuser',passwd='xxxxx'
[0.000]
onlyread='0',protocol='7.4',showoid='0',fakeoidindex='0',showsystable='0'
[0.000]          conn_settings='',conn_encoding='(null)'
[0.000]          translation_dll='',translation_option=''
[0.000]Driver Version='08.02.0400,200704270001' linking static Multithread library
[0.000]Global Options: fetch=100, socket=4096, unknown_sizes=0,
max_varchar_size=255, max_longvarchar_size=8190
[0.000]                disable_optimizer=0, ksqo=1, unique_index=1,
use_declarefetch=1
[0.000]                text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=1 NAMEDATALEN=64
[0.000]                extra_systable_prefixes='dd_;', conn_settings=''
conn_encoding=''
[0.046]    [ PostgreSQL version string = '8.2.5' ]
[0.046]    [ PostgreSQL version number = '8.2' ]
[0.046]conn=02DE3A70, query='select oid, typbasetype from pg_type where typname
= '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 = '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 = 17288 ]
[0.046]    [ Client encoding = 'LATIN9' (code = 16) ]
[0.046]conn=02DE3A70,
PGAPI_DriverConnect(out)='DSN=BI;DATABASE=BI;SERVER=localhost;PORT=5432;UID=biuser;PWD=xxxxxxxxx;SSLmode=disable;ReadOnly=0;Protocol=7.4-1;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=0;CommLog=1;Optimizer=0;Ksqo=1;UseDeclareFetch=1;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0;LowerCaseIdentifier=0;XaOpt=1'
[0.062]STATEMENT ERROR: func=set_statement_option, desc='', errnum=30,
errmsg='The option may be for MS SQL Server(Set)'
[0.062]
------------------------------------------------------------
[0.062]                 hdbc=02DE3A70, stmt=02DE85C8, result=00000000
[0.062]                 prepare=0, internal=0
[0.062]                 bindings=00000000, bindings_allocated=0
[0.062]                 parameters=02DE8F48, parameters_allocated=1
[0.062]                 statement_type=-2, statement='(NULL)'
[0.062]                 stmt_with_params='(NULL)'
[0.062]                 data_at_exec=-1, current_exec_param=-1, put_data=0
[0.062]                 currTuple=-1, current_col=-1, lobj_fd=-1
[0.062]                 maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0,
scroll_concurrency=1
[0.062]                 cursor_name=''
[0.062]                 ----------------QResult Info
-------------------------------
[0.062]CONN ERROR: func=set_statement_option, desc='', errnum=0, errmsg='(NULL)'
[0.062]





Thanks,
Cláudia.