Show max_identifier_length causes heavy query execution

Hello all,

I noticed that after executing a simple query
that takes few milliseconds, the odbc driver executes another query
which takes about 30 seconds to execute, because it scans a large table.
This is the part of the log file I think is showing the problem, see below for the entire log file:

[15.534]conn=02E72C78, query='select count(*) from az001.doc101 where (code,riga)=(1,1)
'
[15.552] [ fetched 1 rows ]
[15.608]conn=02E72C78, query='show max_identifier_length'
[15.619] [ fetched 1 rows ]
[15.660]STATEMENT ERROR: func=PGAPI_ColAttributes, desc='', errnum=30, errmsg='this request may be for MS SQL Server'

Then the driver executes the heavy query "SELECT * FROM az001.doc101", which retrieves about 1.5 million rows.

I came up with a workaround to avoid this problem, executing the query
SELECT (select count(*) from az001.doc101 where (code,riga)=(1,1))
instead
of the original one, but this problem seems to appear in many points of
the application, so i would like to find a better solution.

Here are some additional information,
the table structure is

CREATE TABLE az001.doc101
(
code integer NOT NULL,
riga integer NOT NULL,
subriga integer NOT NULL,
t_prog character varying(6) NOT NULL,
fd_a character varying,
fd_nd double precision,
fd_il integer,
fd_v double precision,
fd_d timestamp with time zone,
fd_sn boolean NOT NULL DEFAULT false,
CONSTRAINT doc101_pkey PRIMARY KEY (code, riga, subriga, t_prog),
CONSTRAINT doc101_code_fkey1 FOREIGN KEY (code, riga, subriga)
REFERENCES az001.doc110 (code, riga, subriga) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (OIDS=FALSE);

and this is the entire log file:

[0.060]conn=02E72C78, PGAPI_DriverConnect( in)='DSN=PostgreSQL Log;', fDriverCompletion=0
[0.082]DSN info: DSN='PostgreSQL Log',server='localhost',port='5432',dbase='muna',user='client',passwd='xxxxx'
[0.091] onlyread='0',protocol='7.4',showoid='0',fakeoidindex='0',showsystable='0'
[0.097] conn_settings='', conn_encoding='(null)'
[0.100] translation_dll='',translation_option=''
[0.116]Driver Version='08.03.0300,200809260001' linking 1400 static Multithread library
[0.123]Global Options: fetch=100, socket=4096, unknown_sizes=2, max_varchar_size=255, max_longvarchar_size=8190
[0.131] disable_optimizer=0, ksqo=1, unique_index=1, use_declarefetch=0
[0.133] text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64
[0.136] extra_systable_prefixes='dd_;', conn_settings='' conn_encoding=''
[0.544] [ PostgreSQL version string = '8.3.4' ]
[0.545] [ PostgreSQL version number = '8.3' ]
[0.565]conn=02E72C78, query='select oid, typbasetype from pg_type where typname = 'lo''
[0.586] [ fetched 0 rows ]
[0.601] [ Large Object oid = -999 ]
[0.602] [ Client encoding = 'UTF8' (code = 6) ]
[0.613]conn=02E72C78,
PGAPI_DriverConnect(out)='DSN=PostgreSQL
Log;DATABASE=muna;SERVER=localhost;PORT=5432;UID=client;PWD=xxxxxx;SSLmode=disable;ReadOnly=0;Protocol=7.4-1;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=2;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=1;CommLog=1;Optimizer=0;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=1;UseServerSidePrepare=0;LowerCaseIdentifier=0;XaOpt=1'
[15.534]conn=02E72C78, query='select count(*) from az001.doc101 where (code,riga)=(1,1)
'
[15.552] [ fetched 1 rows ]
[15.608]conn=02E72C78, query='show max_identifier_length'
[15.619] [ fetched 1 rows ]
[15.660]STATEMENT ERROR: func=PGAPI_ColAttributes, desc='', errnum=30, errmsg='this request may be for MS SQL Server'
[15.663] ------------------------------------------------------------
[15.664] hdbc=02E72C78, stmt=02E77808, result=02E70EA0
[15.665] prepare=0, internal=0
[15.666] bindings=02E791E8, bindings_allocated=1
[15.667] parameters=00000000, parameters_allocated=0
[15.668] statement_type=0, statement='select count(*) from az001.doc101 where (code,riga)=(1,1)
'
[15.670] stmt_with_params='select count(*) from az001.doc101 where (code,riga)=(1,1)
'
[15.671] data_at_exec=-1, current_exec_param=-1, put_data=0
[15.672] currTuple=-1, current_col=-1, lobj_fd=-1
[15.673] maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0, scroll_concurrency=1
[15.674] cursor_name='SQL_CUR02E77808'
[15.675] ----------------QResult Info -------------------------------
[15.677] fields=02E77BA8, backend_tuples=02E78E70, tupleField=48729712, conn=02E72C78
[15.678] fetch_count=0, num_total_rows=1, num_fields=1, cursor='(NULL)'
[15.679] message='(NULL)', command='SELECT', notice='(NULL)'
[15.680] status=100, inTuples=0
[15.681]CONN ERROR: func=PGAPI_ColAttributes, desc='', errnum=0, errmsg='(NULL)'
[15.683] ------------------------------------------------------------
[15.685] henv=02E72C38, conn=02E72C78, status=1, num_stmts=16
[15.687] sock=02E70A08, stmts=02E70AE0, lobj_type=-999
[15.688] ---------------- Socket Info -------------------------------
[15.690] socket=552, reverse=0, errornumber=0, errormsg='(NULL)'
[15.692] buffer_in=48715736, buffer_out=48719856
[15.692] buffer_filled_in=76, buffer_filled_out=0, buffer_read_in=76
[15.735]CONN ERROR: func=SQLNativeSqlW, desc='', errnum=-2, errmsg='Sql string too large'
[15.737] ------------------------------------------------------------
[15.738] henv=02E72C38, conn=02E72C78, status=1, num_stmts=16
[15.739] sock=02E70A08, stmts=02E70AE0, lobj_type=-999
[15.740] ---------------- Socket Info -------------------------------
[15.741] socket=552, reverse=0, errornumber=0, errormsg='(NULL)'
[15.743] buffer_in=48715736, buffer_out=48719856
[15.743] buffer_filled_in=76, buffer_filled_out=0, buffer_read_in=76
[15.772]conn=02E72C78, query='SELECT * FROM az001.doc101'

Thanks in advance, I apologize for my poor english.

Regards,

Moreno Dissegna
Developer
S.A. Software S.r.l.
_________________________________________________________________
Più di 30 stazioni. Ascolta la Radio su Messenger!
http://messenger.it/radioMessenger.aspx