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/