Statement Timeout and Locking - Mailing list pgsql-jdbc

Hello,

In one of our maintainance apps that shuffles some data across
independent databases, we need to enshure data consistency, and decided
to do this by acquiring a lock in the target database. Now we do not
want the application to wait indefinitely for this lock, but fail and
complain loudly whenever locking fails.

When using the SQL statement
  SET statement_timeout TO 5000; LOCK TABLE exampletable IN EXCLUSIVE
  MODE; SET statement_timeout TO DEFAULT;
in psql, everything works fine. If someone else has the lock, it aborts with
  ERROR:  canceling query due to user request
after waiting 5 seconds.

However, the attached java file shows a different behaviour, it seems to
wait forever (at least it waited three minutes, and I'll keep it running
over lunch).

I use PostgreSQL 8.0 JDBC3 with SSL (build 311) and a debian PostgreSQL
7.4.7-5 server.

Does someone have an explanation for this, or even better, a fix or
workaround?

Markus


import java.sql.Connection;
import java.sql.DriverManager;

public class LockTest {

    public static void main(String[] args) throws Exception {
        System.out.println("using: "+org.postgresql.Driver.getVersion());
        Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/locktest", "test", "blubb");
        conn.setAutoCommit(false);
        conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
        conn.setReadOnly(false);
        conn.createStatement().execute("SET statement_timeout TO 5000; LOCK TABLE exampletable IN EXCLUSIVE MODE; SET
statement_timeoutTO DEFAULT;"); 
        System.out.println("Lock acquired");
        Thread.sleep(10000000);
    }
}