The following bug has been logged online:

Bug reference: 1550
Logged by: Spencer Riddering
Email address: spencer@riddering.net
PostgreSQL version: 7.4.6
Operating system: Debian Woody ( Postgresql from backports.org)
Description: LOCK TABLE in plpgsql function doesn't work.
Details:

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

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.



/***************** FC_PROCESS_ORDER ****************/
DECLARE
in_receipt ALIAS FOR $1;
in_familyName ALIAS FOR $2;
in_givenName ALIAS FOR $3;
in_address1 ALIAS FOR $4;
in_address2 ALIAS FOR $5;
in_zipCode ALIAS FOR $6;
in_area ALIAS FOR $7;
in_areaDetail ALIAS FOR $8;
in_emailAddress ALIAS FOR $9;
in_product ALIAS FOR $10;
in_phone ALIAS FOR $11;
in_country ALIAS FOR $12;


p_curtime timestamp;
p_payment_record RECORD;
p_payment_consumed RECORD;
p_updated_oid oid; -- set to NULL
p_order_id int4; -- set to NULL
p_customer_id int4; -- set to NULL
p_tmp_order_record RECORD;

BEGIN
-- LOCK TABLE orders IN SHARE ROW EXCLUSIVE MODE;
-- LOCK TABLE payments IN SHARE ROW EXCLUSIVE MODE;

p_curtime := 'now';

-- Determine wether payment has occured.
SELECT INTO p_payment_record * from payments where in_receipt =
payments.receipt;
IF NOT FOUND THEN
RETURN -101; -- PAYMENT_NOT_FOUND
END IF;


-- *** Payment was recieved ***


-- Make sure that the payment is not used.
-- SELECT INTO p_tmp_order_record * FROM orders WHERE payment_id =
p_payment_record.id;
SELECT INTO p_tmp_order_record * FROM orders WHERE payment_id =
p_payment_record.id;
IF FOUND THEN
RETURN -102; -- PAYMENT_CONSUMED
END IF;

-- *** Payment is available ***

-- Add user data.
INSERT INTO customers (family_name, given_name, address_1,
address_2, zip_code, area, area_detail, email , phone ,
country)
VALUES (in_familyName, in_givenName, in_address1,
in_address2, in_zipCode, in_area, in_areaDetail, in_emailAddress, in_phone,
in_country);


-- Find the newly created id.
GET DIAGNOSTICS p_updated_oid = RESULT_OID;
SELECT INTO p_customer_id id from customers where OID = p_updated_oid;

-- *** customers record added *** ---

-- *** Add orders Record *** ---

INSERT INTO orders (customer_id, payment_id , product_id)
VALUES (p_customer_id, p_payment_record.id, in_product);

-- *** orders record added *** ---

GET DIAGNOSTICS p_updated_oid = RESULT_OID;
SELECT INTO p_order_id id from orders where OID = p_updated_oid;

RETURN p_order_id;

END;
/***********************************************/

/***********************************************/
/***********************************************/
/***********************************************/
/***********************************************/
/***********************************************/
/***********************************************/
/***********************************************/

/****************** Java Code ******************/
// Get Result code/transaction id.
int int_transactId;

Connection conn = null;

try {
conn = ds.getConnection();
conn.setAutoCommit(false);
// This is good. We see updates after they are commited.

conn.setTransactionIsolation(Connection.TRANSACTIO N_READ_COMMITTED);

// Call out to database
CallableStatement callstat = null;
Statement stat = null;
ResultSet rs = null;

try {
// I had to add these lines to actually
// Lock the tables.
stat = conn.createStatement();
stat.executeUpdate("LOCK TABLE orders IN SHARE ROW EXCLUSIVE
MODE");
stat.close();

stat = conn.createStatement();
stat.executeUpdate("LOCK TABLE payments IN SHARE ROW
EXCLUSIVE MODE");
stat.close();

// 1 2 3 4 5 6 7 8 9 10 11 12 13

callstat = conn
.prepareCall("{ ? = call FC_PROCESS_ORDER(?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?) }");
callstat.registerOutParameter(1, java.sql.Types.INTEGER);
callstat.setString(2, receipt);
callstat.setString(3, familyName);
callstat.setString(4, givenName);
callstat.setString(5, address1);
callstat.setString(6, address2);
callstat.setInt(7, zipCode);
callstat.setString(8, area);
callstat.setString(9, areaDetail);
callstat.setString(10, emailAddress);
callstat.setInt(11, product_id);
callstat.setString(12, phone);
callstat.setString(13, country);
if (!callstat.execute()) { // A failure occured, either an
// update count or no result was
// returned.
// Package and then delagate the exception.
throw new OrderException(
"The stored procedure FC_PROCESS_ORDER failed to
return expected results.");
}

// *** Executed with out error ***

// Catch warnings durring debugging.
if (log.isDebugEnabled()) {
printWarnings(callstat.getWarnings());
}

int_transactId = callstat.getInt(1);
conn.commit();
} finally {
if (callstat != null) {
try {
callstat.close();
} catch (SQLException err) {
log.warn("Failed to properly close CallableStatement
object.",err);
}
}
}

} catch (SQLException e) {
while (e != null) {
log.error("\nSQL Exception: \n ANSI-92 SQL State: "
+ e.getSQLState() + "\n Vendor Error Code: "
+ e.getErrorCode(), e);
e = e.getNextException();
}
try {
conn.rollback();
} catch (SQLException e1) {
log.warn("Failed to rollback transaction.",e1);
}
throw new OrderException("Unable to retrieve data from
database.");
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e1) {
log.warn("Failed to properly close connection object.",
e1);
}
}
}
/*************************************************/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

"Spencer Riddering" <spencer@riddering.net> writes:
> When a LOCK TABLE statement is included in a plpgsql function it does not
> actually lock the table.

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:

> 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/

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