Re: [INTERFACES] Postgres Locking, Access'97 and ODBC - Mailing list pgsql-interfaces
From | Jose' Soares Da Silva |
---|---|
Subject | Re: [INTERFACES] Postgres Locking, Access'97 and ODBC |
Date | |
Msg-id | Pine.LNX.3.96.980505094653.403A-100000@proxy.bazzanese.com Whole thread Raw |
In response to | Postgres Locking, Access'97 and ODBC (Byron Nikolaidis <byronn@insightdist.com>) |
List | pgsql-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'
pgsql-interfaces by date: