Postgres Locking, Access'97 and ODBC - Mailing list pgsql-hackers

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.

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.

Regards,

Byron