Re: Hibernate / other ORM and pg_advisory_lock

Peter Kovacs wrote:
>> -First: thankfully polling is not required. One of the nice things about pg's advisory locks is
>> the ability for the client to block on a lock. If the app wants to wait on an advisory lock all it
>> has to do is attempt to acquire the lock; the thread will block until a result is returned (ie the
>> lock is obtained).
>
>> If the user gets bored of waiting the UI thread can just signal the blocked DB thread to abort
>> the query and get on with something else.
>
> Is this mechanism supported by the PostgreSQL JDBC driver?

Good question. The answer is yes, as shown by the attached test class,
but it's definitely a good idea to test rather than assume these things.
The test should be run while some other job, like a psql interactive
session, holds pg_advisory_lock(1). You'd need to update the connection
string to use it.

Canceling the query works fine, at least on 8.3 with the JDBC4 8.3
driver. The JDBC driver handles the cancel() call by establishing a new
connection and issuing a cancel query request with it.

Of course, I can't guarantee that the JDBC driver isn't polling
internally, but it's not hitting the database to do it so for my use I
don't much care. According to wireshark there's no traffic on port 5432
during the 10 second wait interval. I'd be surprised if it wasn't
actually blocked on the network read, though I haven't gone digging to
verify this.

--
Craig Ringer
import java.sql.SQLException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;

public class JdbcCancelTest {

    Connection c;
    CallableStatement st;

    static final String PG_ERR_QUERY_CANCELLED = "57014";

    class Waiter extends Thread {
        @Override
        public void run() {
            try {
                st = c.prepareCall("SELECT pg_advisory_lock(1);");
                System.out.println("[Waiter] Sending query...");
                st.execute();
                System.out.println("[Waiter] Query finished OK");
            } catch (SQLException ex) {
                if (ex.getSQLState().equals(PG_ERR_QUERY_CANCELLED)) {
                    System.out.println("[Waiter] Query cancelled successfully");
                } else {
                    System.out.println("[Waiter] DB error string: " + ex.getMessage() + " (SQLState: " +
ex.getSQLState()+ ")"); 
                }
            }
        }
    }

    class Killer extends Thread {
        @Override
        public void run() {
            try {
                System.out.println("[Killer] Cancelling query...");
                st.cancel();
                System.out.println("[Killer] Query cancel sent");
            } catch (SQLException ex) {
                System.out.println("[Killer] Unexpected exception: " + ex);
            }
        }
    }

    void runtest() throws InterruptedException {
        try {
            c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/DBNAME?user=USERNAME&password=PASSWORD");
            Waiter w = new Waiter();
            Killer k = new Killer();
            w.start();
            Thread.sleep(10000);
            k.start();
        } catch ( SQLException ex ) {
            System.out.println(ex);
        }
    }

    public static void main(String[] args) throws ClassNotFoundException, InterruptedException {
        Class.forName("org.postgresql.Driver");
        JdbcCancelTest t = new JdbcCancelTest();
        t.runtest();
    }
}