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'