BUG #1550: LOCK TABLE in plpgsql function doesn't work. - Mailing list pgsql-bugs

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