Skip site navigation (1) Skip section navigation (2)

Re: Show max_identifier_length causes heavy query execution

From: Moreno D(dot) <moreno(dot)d(at)hotmail(dot)it>
To: <laurenz(dot)albe(at)wien(dot)gv(dot)at>, <pgsql-odbc(at)postgresql(dot)org>
Subject: Re: Show max_identifier_length causes heavy query execution
Date: 2009-05-28 13:42:52
Message-ID: SNT116-W35E382CE7C4B2F80471EB6ED500@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-odbc
>If that is the cause of your problem, then setting "Parse=1"
>will fix it.
> 
>In ODBC Administrator on Windows, this option is called "Parse Statements"
>and can be found in the advanced options page 1 (Button "Datasource").

Thank you Laurenz, setting "Parse=1" solved my problem!
Anyway, another problem appeared at another point in the application, i think this is the reason why that flag was turned off.
I simplified the query that gives the error, and obtained this :

    SELECT 1 as prog  UNION  SELECT code from aaa; --This one gives error

where aaa is a previously created table (If I create the table in the same statement, I don't get the error) :

    create table aaa(
    code integer   -- i tried also with character varying, it's the same
    );

If I write the two select in reverse order it works

    SELECT code as prog from aaa UNION  SELECT 1; --This one works

>Without the ODBC statements it is difficult to say something conclusive,

I attach below the log file, but I don't know what are the "ODBC statements"...Is it the "mylog" file? I'm using ADO components to connect to the database, so I don't know very much about ODBC, I'm sorry..

I analized the mylog in both cases and noticed this difference:

(This is the correct one)
[2956-2.138]parse_statement: entering...
[2956-2.138]unquoted=1, quote=0, dquote=0, numeric=0, delim=' ', token='SELECT', ptr='code as prog from aaa UNION  SELECT 1;
'
[2956-2.140]SELECT
[2956-2.140]unquoted=1, quote=0, dquote=0, numeric=0, delim=' ', token='code', ptr='as prog from aaa UNION  SELECT 1;
'
[2956-2.142]blevel=0 btoken=SELECT in_dot=0 in_field=0 tbname=
[2956-2.143]reallocing at nfld=0
[2956-2.144]got field='code', dot='(null)'
[2956-2.144]unquoted=1, quote=0, dquote=0, numeric=0, delim=' ', token='as', ptr='prog from aaa UNION  SELECT 1;
'
[2956-2.146]blevel=0 btoken=code in_dot=0 in_field=1 tbname=
[2956-2.147]got AS
[2956-2.147]unquoted=1, quote=0, dquote=0, numeric=0, delim=' ', token='prog', ptr='from aaa UNION  SELECT 1;
'
[2956-2.149]blevel=0 btoken=as in_dot=0 in_field=1 tbname=
[2956-2.149]alias for field 'code' is 'prog'
[2956-2.150]unquoted=1, quote=0, dquote=0, numeric=0, delim=' ', token='from', ptr='aaa UNION  SELECT 1;
'
[2956-2.152]First [2956-2.152]FROM
[2956-2.152]unquoted=1, quote=0, dquote=0, numeric=0, delim=' ', token='aaa', ptr='UNION  SELECT 1;
'
[2956-2.153]got table = 'aaa'
[2956-2.154]unquoted=1, quote=0, dquote=0, numeric=0, delim=' ', token='UNION', ptr='SELECT 1;
'
[2956-2.155]UNION...                       <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 

(And this is the one that gives error)
[3244-1.937]parse_statement: entering...
[3244-1.937]unquoted=1, quote=0, dquote=0, numeric=0, delim=' ', token='SELECT', ptr='1 as prog  UNION  SELECT code from aaa;
'
[3244-1.939]SELECT
[3244-1.939]unquoted=1, quote=0, dquote=0, numeric=1, delim=' ', token='1', ptr='as prog  UNION  SELECT code from aaa;
'
[3244-1.941]blevel=0 btoken=SELECT in_dot=0 in_field=0 tbname=
[3244-1.942]reallocing at nfld=0
[3244-1.942]**** got numeric: nfld = 0
[3244-1.943]got field='(null)', dot='(null)'
[3244-1.944]unquoted=1, quote=0, dquote=0, numeric=0, delim=' ', token='as', ptr='prog  UNION  SELECT code from aaa;
'
[3244-1.945]blevel=0 btoken=1 in_dot=0 in_field=1 tbname=
[3244-1.946]got AS
[3244-1.946]unquoted=1, quote=0, dquote=0, numeric=0, delim=' ', token='prog', ptr='UNION  SELECT code from aaa;
'
[3244-1.948]blevel=0 btoken=as in_dot=0 in_field=1 tbname=
[3244-1.948]alias for field '(null)' is 'prog'
[3244-1.949]unquoted=1, quote=0, dquote=0, numeric=0, delim=' ', token='UNION', ptr='SELECT code from aaa;
'
[3244-1.951]blevel=0 btoken=prog in_dot=0 in_field=0 tbname=prog  
[3244-1.952]got field='UNION', dot='(null)' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
[3244-1.953]unquoted=1, quote=0, dquote=0, numeric=0, delim=' ', token='SELECT', ptr='code from aaa;




This is psqlodbc_2008.log:
[0.007]Driver Version='08.03.0400,200811070001' linking 1400 static Multithread library
[0.015]Global Options: fetch=100, socket=4096, unknown_sizes=2, max_varchar_size=255, max_longvarchar_size=8190
[0.018]                disable_optimizer=0, ksqo=1, unique_index=1, use_declarefetch=0
[0.021]                text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64
[0.025]                extra_systable_prefixes='dd_;', conn_settings='' conn_encoding=''
[0.104]    [ PostgreSQL version string = '8.3.4' ]
[0.106]    [ PostgreSQL version number = '8.3' ]
[0.121]conn=031C2470, query='select oid, typbasetype from pg_type where typname = 'lo''
[0.139]    [ fetched 0 rows ]
[0.148]    [ Large Object oid = -999 ]
[0.150]    [ Client encoding = 'UTF8' (code = 6) ]
[0.161]conn=031C2470, 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=1;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=1;UseServerSidePrepare=0;LowerCaseIdentifier=0;XaOpt=1'
[1.909]conn=031C2470, query='SELECT 1 as prog  UNION  SELECT code from aaa;
'
[1.922]    [ fetched 1 rows ]
[1.971]conn=031C2470, query='select current_schema()'
[1.989]    [ fetched 1 rows ]
[2.003]conn=031C2470, query='select nspname from pg_namespace n, pg_class c where c.relnamespace=n.oid and c.oid='"aaa"'::regclass'
[2.024]    [ fetched 1 rows ]
[2.095]conn=031C2470, query='select n.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid, d.adsrc from (((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.relname = E'aaa' and n.nspname = E'public') inner join pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum> 0 and a.attrelid = c.oid) inner join pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum'
[2.152]    [ fetched 1 rows ]
[2.289]PGAPI_Columns: table='aaa',field_name='code',type=23,name='int4'
[2.393]STATEMENT ERROR: func=PGAPI_DescribeCol, desc='Col#=1, #Cols=1,1 keys=2', errnum=13, errmsg='Invalid column number in DescribeCol.'
[2.397]                 ------------------------------------------------------------
[2.398]                 hdbc=031C2470, stmt=031C6FD0, result=031C72E8
[2.399]                 prepare=0, internal=0
[2.400]                 bindings=031C89E0, bindings_allocated=1
[2.401]                 parameters=00000000, parameters_allocated=0
[2.402]                 statement_type=0, statement='SELECT 1 as prog  UNION  SELECT code from aaa;
'
[2.403]                 stmt_with_params='SELECT 1 as prog  UNION  SELECT code from aaa;
'
[2.405]                 data_at_exec=-1, current_exec_param=-1, put_data=0
[2.406]                 currTuple=-1, current_col=-1, lobj_fd=-1
[2.406]                 maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0, scroll_concurrency=1
[2.407]                 cursor_name='SQL_CUR031C6FD0'
[2.408]                 ----------------QResult Info -------------------------------
[2.409]                 fields=031C7398, backend_tuples=031C8698, tupleField=52201112, conn=031C2470
[2.411]                 fetch_count=0, num_total_rows=1, num_fields=1, cursor='(NULL)'
[2.412]                 message='(NULL)', command='SELECT', notice='(NULL)'
[2.414]                 status=100, inTuples=0
[2.415]CONN ERROR: func=PGAPI_DescribeCol, desc='Col#=1, #Cols=1,1 keys=2', errnum=0, errmsg='(NULL)'
[2.418]            ------------------------------------------------------------
[2.419]            henv=031C0A70, conn=031C2470, status=1, num_stmts=16
[2.420]            sock=031C0AA0, stmts=031C0B68, lobj_type=-999
[2.421]            ---------------- Socket Info -------------------------------
[2.422]            socket=552, reverse=0, errornumber=0, errormsg='(NULL)'
[2.423]            buffer_in=52187072, buffer_out=52191176
[2.424]            buffer_filled_in=456, buffer_filled_out=0, buffer_read_in=456
[3.990]conn=031C2470, PGAPI_Disconnect

Regards,

Moreno Dissegna
Developer
S.A. Software S.r.l.






_________________________________________________________________
PiĆ¹ di 100 Emoticon gratis per il tuo Messenger!
http://intrattenimento.it.msn.com/emoticon

In response to

Responses

pgsql-odbc by date

Next:From: Hiroshi InoueDate: 2009-05-29 00:04:35
Subject: Re: Show max_identifier_length causes heavy query execution
Previous:From: FarooqDate: 2009-05-28 05:25:56
Subject: ODBC driver and refcursors

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group