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

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'