Обсуждение: UserLock oddity with Limit

Fiddling with userlock stuff for the purposes of setting up an action
queue.  Having the lock in the where clause causes the lock code to
actually lock 2 rows, not just the one that is being returned.  0's in
the last section means it could not be locked.  This is with 7.1.1.
The function itself is pretty simple, so I'm wondering that the
function isn't being evaluated for 2 rows where only 1 was wanted.

Userlock code is in the contrib. section.


CREATE TABLE testlock ( id SERIAL PRIMARY KEY
);

INSERT INTO testlock DEFAULT VALUES:
INSERT INTO testlock DEFAULT VALUES:
INSERT INTO testlock DEFAULT VALUES:
INSERT INTO testlock DEFAULT VALUES:
INSERT INTO testlock DEFAULT VALUES:

SELECT id FROM testlock WHERE user_write_lock_oid(oid) = '1' LIMIT 1;

-- From another connection

SELECT user_write_lock_oid(oid) FROM testlock;


--
Rod Taylor  BarChord Entertainment Inc.

As a general rule I don't.  But I'm having a hard time trying to find
out if there is a lock on a given item without attempting to lock it.
Seems to work that way with all locks but most delay until it can
obtain it.  Userlocks don't wait.


--
Rod Taylor  BarChord Entertainment Inc.
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Rod Taylor" <rbt@barchord.com>
Cc: "Hackers List" <pgsql-hackers@postgresql.org>
Sent: Tuesday, May 08, 2001 11:35 AM
Subject: Re: [HACKERS] UserLock oddity with Limit


> "Rod Taylor" <rbt@barchord.com> writes:
> > Fiddling with userlock stuff for the purposes of setting up an
action
> > queue.  Having the lock in the where clause causes the lock code
to
> > actually lock 2 rows, not just the one that is being returned.
>
> A WHERE clause should *never* contain function calls with side
effects.
> I do not regard this behavior as a bug.  Put the function call in
the
> SELECT's output list if you want to know exactly which rows it is
> evaluated at.
>
> regards, tom lane
>

"Rod Taylor" <rbt@barchord.com> writes:
> Fiddling with userlock stuff for the purposes of setting up an action
> queue.  Having the lock in the where clause causes the lock code to
> actually lock 2 rows, not just the one that is being returned.

A WHERE clause should *never* contain function calls with side effects.
I do not regard this behavior as a bug.  Put the function call in the
SELECT's output list if you want to know exactly which rows it is
evaluated at.
        regards, tom lane