From: Byron Nikolaidis <byronn@insightdist.com>
To: Jose' Soares Da Silva <sferac@bo.nettuno.it>
To: interfaces postgres <pgsql-interfaces@postgreSQL.org>
Subject: Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
Date: Wed, 10 Jun 1998 13:45:51 -0400
Message-ID: <357EC64F.E514A171@insightdist.com> (raw)
References: <Pine.LNX.3.96.980610091152.1091B-100000@proxy.bazzanese.com>
<357EA325.6BABF8D4@insightdist.com>
Byron Nikolaidis wrote:
> Jose' Soares Da Silva wrote:
>
> > My problem is that I need a TIMESTAMP data type defined in M$-Access because
> > M$-Access wants it to have best performance when it updates a table via ODBC.
> > M$-Access doesn't lock a record being modified, to allow control concurrent
> > access to data M$-Access reads again the record to verify if it was modified by
> > another user, before update it to database.
> > If there's a TIMESTAMP M$-Access verifies only, if this field was modified,
> > otherwise it verifies every field of the table, and obviously it is slower.
> > I beleave it would very useful if you could add this feature to psqlodbc.
> > Thanks, Jose'
> >
>
I did some testing with SQLSpecialColumns 'SQL_ROWVER'. As I noted in my previous mail,
we dont return anything for this function in the driver. I tried hard-coding a column
that was a SQL_TIMESTAMP type (in my table it was a postgres 'datetime'). Access did use
that column. Here are the results:
test1 table
----------
a,c,d,e,f,g = int2
b,h = varchar
datetim = datetime
Access results without ROWVER (this is the way things currently are)
---------------------------------------------------------------------
BEGIN
update test1 set b='hello' where a=7 AND b='other' AND c=3 AND d=4 AND e is NULL AND f is
NULL AND g=5 AND h='stuff'
COMMIT
Access results with ROWVER
-------------------------------
BEGIN
update test1 set b='hello' where a=7 AND datetim = '1998-05-30 10:59:00';
select a,b,c,d,e,f,g,h,datetim where a=7;
COMMIT
Conclusion:
-----------
The update statement was definately smaller and only involved the key and the timestamp
column. The extra select that it does to verify no one has changed anything (using the
value of the timestamp) slowed the update down, though. I don't think the speed gain on
the smaller update statement makes up for the extra query. In either case, the backend
locking problem would still prevent the update if the table was opened by someone else (or
even the same application, as in our declare/fetch problem).
Also, something would have to be done to actually put a timestamp value in every time a
row was added or updated. Access actually prevented me from entering a value in my
'datetim' field because it assumed the dbms would fill it in. I guess you could use a
trigger to update the timestamp field. OR if we had a pseudo column that qualified, we
could use that, however when I tried using a pseudo column, Access barfed on me
complaining "Table TMP%#$$^ already exists". If I added the pseudo column to the output,
the message went away. I have no idea what the heck that means?
Any ideas or thoughts?
Byron
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: byronn@insightdist.com, sferac@bo.nettuno.it
Subject: Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
In-Reply-To: <357EC64F.E514A171@insightdist.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox