Re: BUG #1550: LOCK TABLE in plpgsql function doesn't work.

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

> When a LOCK TABLE statement is included in a plpgsql function it does not
> actually lock the table.

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
| | 19353 | 26277 | ExclusiveLock | t
19293 | 17144 | | 26277 | ShareRowExclusiveLock | 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
| | 19353 | 26277 | ExclusiveLock | t
19293 | 17144 | | 26277 | ShareRowExclusiveLock | t
| | 19354 | 26274 | ExclusiveLock | t
(5 rows)

> But, if prior to calling the function I execute a seperate statement using
> the same connection and same transaction then the LOCK TABLE does work.
>
> I think the expectation is that LOCK TABLE should actually lock the table
> even when included in a plpgsql function.
>
> I used JDBC (pg74.215.jdbc3.jar) to discover this behavior.

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/