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

Re: [INTERFACES] Postgres Locking, Access'97 and ODBC

From: "Jose' Soares Da Silva" <sferac(at)bo(dot)nettuno(dot)it>
To: Byron Nikolaidis <byronn(at)insightdist(dot)com>
Cc: "pgsql-hackers(at)postgreSQL(dot)org" <pgsql-hackers(at)postgreSQL(dot)org>, pgsql-interfaces(at)postgreSQL(dot)org
Subject: Re: [INTERFACES] Postgres Locking, Access'97 and ODBC
Date: 1998-05-05 10:00:27
Message-ID: Pine.LNX.3.96.980505094653.403A-100000@proxy.bazzanese.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-interfaces
On Thu, 30 Apr 1998, Byron Nikolaidis wrote:

Thank you very much Byron for your explanation.

> Jose' Soares Da Silva wrote:
> 
> > Now I have another problem using M$-Access;
> >    I have a table like this one:
> >
> > Table    = comuni
> > +------------------------------+----------------------------------+-------+
> > |          Field               |              Type                | Length|
> > +------------------------------+----------------------------------+-------+
> > | istat                        | char() not null                  |     6 |
> > | nome                         | varchar()                        |    50 |
> > | provincia                    | char()                           |     2 |
> > | codice_fiscale               | char()                           |     4 |
> > | cap                          | char()                           |     5 |
> > | regione                      | char()                           |     3 |
> > | distretto                    | char()                           |     4 |
> > +------------------------------+----------------------------------+-------+
> > ... in this table I have stored 8k rows, if I load it from M$-Access and
> > then I modify a row and I try to save it to database, it goes in a loop
> > I don't know what's happening.
> >     Please help me.                                         Thanks, Jose'
> >
> 
> This problem has to do with the Postgres' locking mechanism.  You cant update a
> table while you have the table open for reading.   You may be asking yourself,
> but I do not have the table open for reading.  Ahhh, but Access does because of
> the way the odbc driver uses cursors to manage backend data.
> 
> Here is the illustration:
> ---------------------
> Access uses two backend connections.  On one connection, it does a query to get
> key values from the table:
> "declare c1 cursor for select key from table"
> 
> It then fetches 101 keys from this query.   This fetch results in the following
> 2 queries to the backend:
> "fetch 100 in c1"
> "fetch 100 in c1"
> 
> (Note that there are 8000+ rows in the table so this leaves the table locked)
> 
> On the other connection, it actually does the update query:
> "update table set a1=2 where key=1"
> 
> This update will wait forever because the other query has the table completely
> locked.
> 
> Workarounds
> --------------
> In Access, you can go to the end of the table first, before you begin your
> update.  Then, any update or insert you do should work.
> 
> You can also do your update on a smaller subset of records by using a filter in
> Access.  200 or less rows would allow the driver to handle it since all the
> keys would have been read in as illustrated above.

Seems this problem exists also when I read only one row.
I tried this: 
I got the first row using a form, then I modified a field on this form and
then I tried to load the next row (by using right arrow), and Access
is already there locked by PostgreSQL.
ps command give me the followinng result: (two backend connections as you said)

3033  ?  S  0:00 postmaster -i -o -F -B 512 -S
5034  ?  S  0:01 /usr/local/pgsql/bin/postgres -p -Q -P5 -F -B 512 -v 6553
5035  ?  S  0:07 /usr/local/pgsql/bin/postgres -p -Q -P5 -F -B 512 -v 6553

> 
> Now for the ultimate question
> -----------------------------
> What is the current status/priority of the locking enhancements for Postgres?
> Clearly, this is an important problem and needs to be addressed.  Even though
> the above example only involves Microsoft Access, we  have applications which
> need to write data to tables that may already be open for reading for a long
> time,
> such as while doing a massive report with lots of joins.  With the current
> locking strategy, these applications are impossible.

Is there in project to work on this problem ?
                                                                   Jose'


In response to

pgsql-hackers by date

Next:From: Andrew MartinDate: 1998-05-05 10:53:26
Subject: Re: [HACKERS] Suggestions
Previous:From: Andreas ZeugswetterDate: 1998-05-05 08:24:20
Subject: AW: [HACKERS] Auto Type conversion

pgsql-interfaces by date

Next:From: Byron NikolaidisDate: 1998-05-05 15:12:22
Subject: Re: [INTERFACES] ODBC: Problem with Fetching records
Previous:From: Arthur AlacarDate: 1998-05-04 03:57:36
Subject: ODBC FAQ

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