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

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.TRANSACTION_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);
                }
            }
        }
/*************************************************/