Re: Hibernate / other ORM and pg_advisory_lock

I'm overlooking the cancellation of the query on a separate thread with
the JDBC cancel() method.  Though that method always seems to have
issues associated with it across various drivers.  But still, for what
you are talking about, I think a long running transaction is still going
to be your biggest problem rather than a long running query.
> I can understand how you would release the advisory lock by running
> another SQL statement in another thread to do so, but I do not know if
> that is of much use to you.  As far as I know you can't tell a running
> JDBC query to abort or cancel a running query.  Such calls, either at
> the JDBC level or at the JPA level are blocking calls for the client
> and about the only influence you have on it is by setting the query
> time out before executing (which has its own set of issues in the
> postgresql driver
> (http://www.nabble.com/Implementing-setQueryTimeout()-td15537669.html)).
> Of course the UI client can always manage calls to its access layer
> via another thread and kill that thread if the end user becomes
> impatient and not want to wait.  But from my understanding, that query
> with the back end database process will still be running and the DB
> connection will still be in use, including all of the associated locks
> and DB threads.  So a subsequent attempt on a long running query will
> also block.  However it the queries are all short running but are part
> of a long running transaction, then you can rollback the transaction
> at any point, but any other calls in a separate transaction dependent
> on those locks held by the running transaction will result in the
> aforementioned blocked call.  You have to remember that at the ORM
> level (which is just a wrapper to the JDBC), or at the JDBC level, you
> do not have fine grain control of what is happening in the back end,
> and in my mind you should be looking at what JDBC provides, not what
> the underlying DB may be able to provide, and in using pg advisory
> locks, you are mixing the two.
Cheers,

Andy