The following bug has been logged online:

Bug reference: 1459
Logged by: Rainer Frey
Email address: rainer.frey (AT) inxmail (DOT) de
PostgreSQL version: 8.0.1
Operating system: Redhat Linux 9, Kernel 2.4.20-8, AMD Sempron 2500+, 1GB
RAM
Description: Connection hangs when other connection is not committed
Details:

There seems to be a locking problem when not using autocommit. I came across
this in a relatively complex Java application, but could reproduce it with
the following scenario: (user test has priviledge to create db)
createdb -U test -W testdb
psql -U test -W testdb
CREATE TABLE test_table (id integer);
ALTER TABLE test_table ADD test integer;

now start another client session, disable autocommit:
psql -U test -W testdb
\set AUTOCOMMIT off
SELECT * FROM test_table;

Back in the first session, try to add another column:
ALTER TABLE test_table ADD test1 integer;

This hangs forever, until I commit session 2.
A select should not lock a table even when it is not committed.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Am Donnerstag, 3. Februar 2005 16:11 schrieb Rainer Frey:
The SELECT obtains a read (shared) lock on the table, but the ALTER TABLE
requires a write (exclusive) lock. This is certainly necessary because you
don't want the structure of the table to be changed while you are reading it.
Additionally, the locking protocol requires that all locks once obtained need
to be held until the end of the transaction. Both of these issues together
explain the problem you are seeing. There is nothing that can be done about
it.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Am Freitag, 4. Februar 2005 11:54 schrieb Rainer Frey:
If that is the end of the transaction, then you might as well commit it then.
But what if you plan to do an update in the same transaction based on the
selection results? You can't release and reaquire locks in the same
transaction without getting into a bunch of trouble. Read up on "strict
two-phase locking" if you're curious.

Yes, there is a statement_timeout parameter or something like that.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend