NOWAIT doesn't work

tested on 9.3

Pavel

2012/10/31 Pavel Stehule <pavel.stehule@gmail.com>:

On Wednesday, October 31, 2012 02:51:38 PM Pavel Stehule wrote:

it is expected behave?

1.session

postgres=# begin;
BEGIN
postgres=# lock oo IN ACCESS EXCLUSIVE MODE;
LOCK TABLE

2. session

postgres=# select * from oo for update nowait;

hangs forever ....

NOWAIT is about row level locks, not about table level locks.

Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 31 October 2012 14:52, Pavel Stehule <pavel.stehule@gmail.com> wrote:

tested on 9.3

Pavel

2012/10/31 Pavel Stehule <pavel.stehule@gmail.com>:

Hello

it is expected behave?

1.session

postgres=# begin;
BEGIN
postgres=# lock oo IN ACCESS EXCLUSIVE MODE;
LOCK TABLE

2. session

postgres=# select * from oo for update nowait;

hangs forever ....

Regards

Pavel Stehule

I've checked on 9.1, works exactly the same.

- Szymon

Hi,

On Wednesday, October 31, 2012 02:51:38 PM Pavel Stehule wrote:

Hello

it is expected behave?

1.session

postgres=# begin;
BEGIN
postgres=# lock oo IN ACCESS EXCLUSIVE MODE;
LOCK TABLE

2. session

postgres=# select * from oo for update nowait;

hangs forever ....

Yes, I think so. From the documentation:

Note that NOWAIT applies only to the row-level lock(s)
(http://www.postgresql.org/docs/9.2/static/sql-select.html)

Greetings,
CK

Pavel Stehule escribió:

Hello

it is expected behave?

1.session

postgres=# begin;
BEGIN
postgres=# lock oo IN ACCESS EXCLUSIVE MODE;
LOCK TABLE

2. session

postgres=# select * from oo for update nowait;

hangs forever ....

"select for update nowait" would raise an error if the tuple-level lock
is being held by some other process; but what's making it wait here is
the table-level lock.

Now, is this the right behavior? I'm not sure. But I know for certain
that making it behave as you expect is very tricky. The table lock is
grabbed during parse analysis; we'd have to postpone grabbing the lock
until after we have had the chance to notice that there's a FOR UPDATE
clause for the table with a NOWAIT option attached.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Alvaro Herrera escribió:

Now, is this the right behavior? I'm not sure. But I know for certain
that making it behave as you expect is very tricky. The table lock is
grabbed during parse analysis; we'd have to postpone grabbing the lock
until after we have had the chance to notice that there's a FOR UPDATE
clause for the table with a NOWAIT option attached.

Furthermore you could do it manually: just do a LOCK TABLE NOWAIT in the
second session before the SELECT FOR UPDATE.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

2012/10/31 Alvaro Herrera <alvherre@2ndquadrant.com>:

Alvaro Herrera escribió:

Now, is this the right behavior? I'm not sure. But I know for certain
that making it behave as you expect is very tricky. The table lock is
grabbed during parse analysis; we'd have to postpone grabbing the lock
until after we have had the chance to notice that there's a FOR UPDATE
clause for the table with a NOWAIT option attached.

Furthermore you could do it manually: just do a LOCK TABLE NOWAIT in the
second session before the SELECT FOR UPDATE.

I understand now, thank you to all for information

Regards

Pavel

Now, is this the right behavior? I'm not sure. But I know for certain
that making it behave as you expect is very tricky. The table lock is
grabbed during parse analysis; we'd have to postpone grabbing the lock
until after we have had the chance to notice that there's a FOR UPDATE
clause for the table with a NOWAIT option attached.

I think our present behavior violates POLS. Users would logically
expect NOWAIT to work for all types of locks. If we get LOCK TIMEOUT
working in 9.3, this will apply there as well.

No question that changing that would be a new feature, though.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com