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

Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs

From: Byron Nikolaidis <byronn(at)insightdist(dot)com>
To: "Jose' Soares Da Silva" <sferac(at)bo(dot)nettuno(dot)it>, interfaces postgres <pgsql-interfaces(at)postgreSQL(dot)org>
Subject: Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
Date: 1998-06-10 17:45:51
Message-ID: 357EC64F.E514A171@insightdist.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-interfaces


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




In response to

Responses

pgsql-hackers by date

Next:From: David GouldDate: 1998-06-10 18:24:04
Subject: Re: [HACKERS] Re: [PATCHES] Try again: S_LOCK reduced contention
Previous:From: Byron NikolaidisDate: 1998-06-10 17:19:23
Subject: Timestamp field

pgsql-interfaces by date

Next:From: Krasnow, GregDate: 1998-06-10 18:36:54
Subject: RE: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
Previous:From: Byron NikolaidisDate: 1998-06-10 15:15:49
Subject: Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs

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