Thread: Is this typical psqlodbc behavior?

(2013/12/10 0:07), Mike Landl wrote:
> Hi there,
>
> I am trying to figure out if the behavior below is something that
> psqlodbc is doing automatically of if there is something else at play.
>
> The programming language I am using to access PostgreSQL is a high level
> 4^th generation language called ProIV and I believe it is doing strange
> and/or unnecessary things behind the scenes. When I run a simple query
> (SELECT CUSTOMER_BILL_TO,CUSMS_BILL_NAME FROM CUS_MST WHERE
> CUSTOMER_BILL_TO='00000675';) against PostgreSQL the following shows up
> in the PostgreSQL log file:
>
> LOG:  statement: SAVEPOINT _EXEC_SVP_0x1b42080
>
> LOG:  duration: 0.017 ms
>
> LOG:  statement: 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, pg_get_expr(d.adbin, d.adrelid), case t.typtype when
> 'd' then t.typbasetype else 0 end, t.typtypmod, c.relhasoids from
> (((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid =
> c.relnamespace and c.oid = 481563) 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
>
> LOG:  duration: 2.487 ms
>
> LOG:  statement: RELEASE _EXEC_SVP_0x1b42080
>
> LOG:  duration: 0.034 ms
>
> LOG:  statement: PREPARE "_KEYSET_0x1af2180"(tid,tid) as SELECT
> CUSTOMER_BILL_TO,CUSMS_BILL_NAME , "ctid" FROM CUS_MST  where ctid in
> ($1,$2)
>
> LOG:  duration: 0.089 ms
>
> LOG:  statement: SAVEPOINT _per_query_svp_;DEALLOCATE
> "_KEYSET_0x1af2180";RELEASE _per_query_svp_
>
> LOG:  duration: 0.027 ms
>
> LOG:  statement: SAVEPOINT _per_query_svp_;select relhasoids, c.oid from
> pg_class c, pg_namespace n where relname = 'cus_mst' and nspname =
> 'public' and c.relnamespace = n.oid;RELEASE _per_query_svp_
>
> LOG:  duration: 0.180 ms
>
> LOG:  statement: SAVEPOINT _per_query_svp_;select a.attname, a.atttypid
> from pg_index i, pg_attribute a where indrelid=481563 and indnatts=1 and
> indisunique and indexprs is null and indpred is null and i.indrelid =
> a.attrelid and a.attnum=i.indkey[0] and attnotnull and atttypid in (23,
> 26);RELEASE _per_query_svp_
>
> LOG:  duration: 0.246 ms
>
> LOG:  statement: SELECT CUSTOMER_BILL_TO,CUSMS_BILL_NAME , "ctid" FROM
> CUS_MST WHERE CUSTOMER_BILL_TO='00000675'
>
> LOG:  duration: 0.100 ms
>
> LOG:  statement: SAVEPOINT _EXEC_SVP_0x1af2180
>
> LOG:  duration: 0.018 ms
>
> LOG:  statement: 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, pg_get_expr(d.adbin, d.adrelid), case t.typtype when
> 'd' then t.typbasetype else 0 end, t.typtypmod, c.relhasoids from
> (((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid =
> c.relnamespace and c.oid = 481563) 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
>
> LOG:  duration: 2.412 ms
>
> LOG:  statement: RELEASE _EXEC_SVP_0x1af2180
>
> LOG:  duration: 0.020 ms
>
> What I am wondering are 2 things:
>
> 1.Are the savepoints something that the odbc driver is automatically
> creating or could it be the programming language be adding additional
> things to my query?

Psqlodbc driver does it.
You can avoid the behavior using the driver option setting or connection
  string.

> 2.Is the odbc driver automatically reading pg_class, pg_catalog, etc. or
> again, could it be the programming language adding additional queries
> behind the scenes?

Psqlodbc driver does it.

regards,
Hiroshi Inoue

--
I am using the free version of SPAMfighter.
SPAMfighter has removed 1065 of my spam emails to date.
Get the free SPAMfighter here: http://www.spamfighter.com/len

Do you have a slow PC? Try a Free scan
http://www.spamfighter.com/SLOW-PCfighter?cid=sigen

Thank you. Can you tell me how I can disable the savepoints using odbc.ini
in Linux?
Apologies if I should know this, I haven't been able to find it.
Is there a list of odbc.ini options that psqlodbc recognizes anywhere?
Thanks again.

-----Original Message-----
From: Inoue, Hiroshi [mailto:inoue@tpf.co.jp]
Sent: Monday, December 09, 2013 7:06 PM
To: Mike Landl; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Is this typical psqlodbc behavior?

(2013/12/10 0:07), Mike Landl wrote:
> Hi there,
>
> I am trying to figure out if the behavior below is something that
> psqlodbc is doing automatically of if there is something else at play.
>
> The programming language I am using to access PostgreSQL is a high
> level 4^th generation language called ProIV and I believe it is doing
> strange and/or unnecessary things behind the scenes. When I run a
> simple query (SELECT CUSTOMER_BILL_TO,CUSMS_BILL_NAME FROM CUS_MST
> WHERE
> CUSTOMER_BILL_TO='00000675';) against PostgreSQL the following shows
> up in the PostgreSQL log file:
>
> LOG:  statement: SAVEPOINT _EXEC_SVP_0x1b42080
>
> LOG:  duration: 0.017 ms
>
> LOG:  statement: 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, pg_get_expr(d.adbin, d.adrelid), case
> t.typtype when 'd' then t.typbasetype else 0 end, t.typtypmod,
> c.relhasoids from (((pg_catalog.pg_class c inner join
> pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.oid =
> 481563) 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
>
> LOG:  duration: 2.487 ms
>
> LOG:  statement: RELEASE _EXEC_SVP_0x1b42080
>
> LOG:  duration: 0.034 ms
>
> LOG:  statement: PREPARE "_KEYSET_0x1af2180"(tid,tid) as SELECT
> CUSTOMER_BILL_TO,CUSMS_BILL_NAME , "ctid" FROM CUS_MST  where ctid in
> ($1,$2)
>
> LOG:  duration: 0.089 ms
>
> LOG:  statement: SAVEPOINT _per_query_svp_;DEALLOCATE
> "_KEYSET_0x1af2180";RELEASE _per_query_svp_
>
> LOG:  duration: 0.027 ms
>
> LOG:  statement: SAVEPOINT _per_query_svp_;select relhasoids, c.oid
> from pg_class c, pg_namespace n where relname = 'cus_mst' and nspname
> = 'public' and c.relnamespace = n.oid;RELEASE _per_query_svp_
>
> LOG:  duration: 0.180 ms
>
> LOG:  statement: SAVEPOINT _per_query_svp_;select a.attname,
> a.atttypid from pg_index i, pg_attribute a where indrelid=481563 and
> indnatts=1 and indisunique and indexprs is null and indpred is null
> and i.indrelid = a.attrelid and a.attnum=i.indkey[0] and attnotnull
> and atttypid in (23, 26);RELEASE _per_query_svp_
>
> LOG:  duration: 0.246 ms
>
> LOG:  statement: SELECT CUSTOMER_BILL_TO,CUSMS_BILL_NAME , "ctid" FROM
> CUS_MST WHERE CUSTOMER_BILL_TO='00000675'
>
> LOG:  duration: 0.100 ms
>
> LOG:  statement: SAVEPOINT _EXEC_SVP_0x1af2180
>
> LOG:  duration: 0.018 ms
>
> LOG:  statement: 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, pg_get_expr(d.adbin, d.adrelid), case
> t.typtype when 'd' then t.typbasetype else 0 end, t.typtypmod,
> c.relhasoids from (((pg_catalog.pg_class c inner join
> pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.oid =
> 481563) 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
>
> LOG:  duration: 2.412 ms
>
> LOG:  statement: RELEASE _EXEC_SVP_0x1af2180
>
> LOG:  duration: 0.020 ms
>
> What I am wondering are 2 things:
>
> 1.Are the savepoints something that the odbc driver is automatically
> creating or could it be the programming language be adding additional
> things to my query?

Psqlodbc driver does it.
You can avoid the behavior using the driver option setting or connection
  string.

> 2.Is the odbc driver automatically reading pg_class, pg_catalog, etc.
> or again, could it be the programming language adding additional
> queries behind the scenes?

Psqlodbc driver does it.

regards,
Hiroshi Inoue

--
I am using the free version of SPAMfighter.
SPAMfighter has removed 1065 of my spam emails to date.
Get the free SPAMfighter here: http://www.spamfighter.com/len

Do you have a slow PC? Try a Free scan
http://www.spamfighter.com/SLOW-PCfighter?cid=sigen

(2013/12/10 22:06), Mike Landl wrote:
> Thank you. Can you tell me how I can disable the savepoints using odbc.ini
> in Linux?
> Apologies if I should know this, I haven't been able to find it.
> Is there a list of odbc.ini options that psqlodbc recognizes anywhere?

Please look at *Level of rollback on errors* section at
docs/config.html.
Specify PROTOCOL option with the level of rollback on errors
    PROTOCOL=7.4[-(0|1|2)]
0:driver does nothing
1:driver rollback the transaction entirely
2:driver rollback the statement

The default is 2 for recent versions of PostgreSQL servers and
the driver issues SAVEPOINT commands beforehand to rollback the
subsequent statement on errors.

regards,
Hiroshi Inoue




--
I am using the free version of SPAMfighter.
SPAMfighter has removed 788 of my spam emails to date.
Get the free SPAMfighter here: http://www.spamfighter.com/len

Do you have a slow PC? Try a Free scan
http://www.spamfighter.com/SLOW-PCfighter?cid=sigen

Thank you so much.

-----Original Message-----
From: Hiroshi Inoue [mailto:inoue@tpf.co.jp]
Sent: Tuesday, December 10, 2013 10:00 AM
To: Mike Landl; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Is this typical psqlodbc behavior?

(2013/12/10 22:06), Mike Landl wrote:
> Thank you. Can you tell me how I can disable the savepoints using
> odbc.ini in Linux?
> Apologies if I should know this, I haven't been able to find it.
> Is there a list of odbc.ini options that psqlodbc recognizes anywhere?

Please look at *Level of rollback on errors* section at docs/config.html.
Specify PROTOCOL option with the level of rollback on errors
    PROTOCOL=7.4[-(0|1|2)]
0:driver does nothing
1:driver rollback the transaction entirely 2:driver rollback the statement

The default is 2 for recent versions of PostgreSQL servers and the driver
issues SAVEPOINT commands beforehand to rollback the subsequent statement on
errors.

regards,
Hiroshi Inoue




--
I am using the free version of SPAMfighter.
SPAMfighter has removed 788 of my spam emails to date.
Get the free SPAMfighter here: http://www.spamfighter.com/len

Do you have a slow PC? Try a Free scan
http://www.spamfighter.com/SLOW-PCfighter?cid=sigen

On 12/10/2013 07:00 AM, Hiroshi Inoue wrote:
> (2013/12/10 22:06), Mike Landl wrote:
>> Thank you. Can you tell me how I can disable the savepoints using
>> odbc.ini
>> in Linux?
>> Apologies if I should know this, I haven't been able to find it.
>> Is there a list of odbc.ini options that psqlodbc recognizes anywhere?
>
> Please look at *Level of rollback on errors* section at
> docs/config.html.

On a somewhat related note, when you go here:

http://psqlodbc.projects.pgfoundry.org/

and try to access the docs you time out waiting for a page. Is there
another location for on line documents or can the above site be fixed?


>
> regards,
> Hiroshi Inoue
>


Thanks,
--
Adrian Klaver
adrian.klaver@gmail.com