"Spencer Riddering" <spencer (AT) riddering (DOT) net> writes:
Sure it does. If it doesn't, your test case surely will not prove it;
you cannot prove the existence or lack of existence of a lock in a test
case with only one connection...

I suspect your complaint really has to do with the fact that the
transaction snapshot is established before the function is entered,
and thus before the lock is taken. Pre-8.0, we did not advance the
snapshot within functions, and so the commands within the function
would all see a snapshot that predated the obtaining of the lock.

Short answer: try 8.0.

regards, tom lane

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

On Thu, Mar 17, 2005 at 08:48:54AM +0000, Spencer Riddering wrote:

How did you determine that? It's not clear from the example you
posted, and your function has its LOCK statements commented out.

I ran simple tests in 7.4.7 and LOCK worked in a PL/pgSQL function.
Here's an example, run from psql:

CREATE TABLE foo (x integer);

CREATE FUNCTION locktest() RETURNS void AS '
BEGIN
LOCK TABLE foo IN SHARE ROW EXCLUSIVE MODE;
RETURN;
END;
' LANGUAGE plpgsql;

BEGIN;
SELECT locktest();
SELECT * FROM pg_locks;

relation | database | transaction | pid | mode | granted
----------+----------+-------------+-------+-----------------------+---------
16759 | 17144 | | 26277 | AccessShareLock | t
(3 rows)

If I try to acquire a conflicting lock in another transaction, it
blocks and pg_locks then looks like this:

relation | database | transaction | pid | mode | granted
----------+----------+-------------+-------+-----------------------+---------
19293 | 17144 | | 26274 | ShareRowExclusiveLock | f
16759 | 17144 | | 26277 | AccessShareLock | t

Is it possible that when you called the function without executing
anything beforehand, it was run in a transaction that ended sooner
than you were expecting? That would release any locks the function
had acquired.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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