pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres Locking, Access'97 and ODBC
2+ messages / 2 participants
[nested] [flat]
* Postgres Locking, Access'97 and ODBC
@ 1998-04-30 16:10 Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 1 reply; 2+ messages in thread
From: Byron Nikolaidis @ 1998-04-30 16:10 UTC (permalink / raw)
To: Jose' Soares Da Silva <sferac@bo.nettuno.it>; pgsql-hackers@postgreSQL.org <pgsql-hackers@postgreSQL.org>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [INTERFACES] Postgres Locking, Access'97 and ODBC
@ 1998-05-05 10:00 Jose' Soares Da Silva <sferac@bo.nettuno.it>
parent: Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 0 replies; 2+ messages in thread
From: Jose' Soares Da Silva @ 1998-05-05 10:00 UTC (permalink / raw)
To: Byron Nikolaidis <byronn@insightdist.com>; +Cc: pgsql-hackers@postgreSQL.org <pgsql-hackers@postgreSQL.org>; 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'
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1998-05-05 10:00 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-04-30 16:10 Postgres Locking, Access'97 and ODBC Byron Nikolaidis <byronn@insightdist.com>
1998-05-05 10:00 ` Jose' Soares Da Silva <sferac@bo.nettuno.it>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox