Skip site navigation (1) Skip section navigation (2)

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

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Spencer Riddering <spencer(at)riddering(dot)net>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1550: LOCK TABLE in plpgsql function doesn't work.
Date: 2005-03-18 06:37:43
Message-ID: 20050318063743.GA26191@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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/

In response to

pgsql-bugs by date

Next:From: Michael MeskesDate: 2005-03-18 10:01:51
Subject: Re: [INTERFACES] libecpg (8.0 and CVS) hits a gcc bug on powerpc and amd64 (crash)
Previous:From: Tom LaneDate: 2005-03-18 06:17:07
Subject: Re: BUG #1550: LOCK TABLE in plpgsql function doesn't work.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group