Re: Show max_identifier_length causes heavy query execution - Mailing list pgsql-odbc
From | Moreno D. |
---|---|
Subject | Re: Show max_identifier_length causes heavy query execution |
Date | |
Msg-id | SNT116-W35E382CE7C4B2F80471EB6ED500@phx.gbl Whole thread Raw |
In response to | Re: Show max_identifier_length causes heavy query execution ("Albe Laurenz" <laurenz.albe@wien.gv.at>) |
Responses |
Re: Show max_identifier_length causes heavy query execution
|
List | 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 turnedoff. 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 componentsto 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 SELECT1; ' [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 fromaaa; ' [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_namespacen on n.oid = c.relnamespace and c.relname = E'aaa' and n.nspname = E'public') inner join pg_catalog.pg_attributea on (not a.attisdropped) and a.attnum> 0 and a.attrelid = c.oid) inner join pg_catalog.pg_type ton t.oid = a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum orderby 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 numberin 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
pgsql-odbc by date: