Fork me on GitHub
#sql
<
2020-02-06
>
Ben Hammond13:02:30

I have stumbled upon an interesting edge-case and I'm trying to figure out if I am misinterpreting the facts: • I am using testcontainers to setup postgres for `clojure.tests` • In one of my tests I perform the following steps • i) get 2 seperate PgConnection out of Hikari Connection Pool • ii) query _`pg_try_advisory_lock`_ from connection a). It returns true. • iii) query _`pg_advisory_lock`_ from within a `future` on connection b). It blocks as expected • iv) query _`pg_advisory_unlock`_on connection b). I expected this to return false; *but actually it hangs* This surprises me. Does it surprise all/any of you? Java stack traces reveals that it is blocked on ```org.postgresql.core.v3.QueryExecutorImpl public synchronized void execute(Query query, ParameterList parameters, ResultHandler handler, int maxRows, int fetchSize, int flags) throws SQLException { waitOnLock();``` which is the natural consequence of calling ``` -- :name advisory-unlock :? :1 SELECT (pg_advisory_unlock(:big_figure, :little_figure));``` as a HugSQL query So the fact that there is an acquireLock outstanding means that *all further queries block* until that lock is satisfied; even an unlock This is an edge case; I don't need it In Real Life but I'd like to understand what is happening, and how I can mitigate it.

Ben Hammond14:02:57

I have updated my test to wrap the pg_advisory_unlock inside a future and then take a friendly interest in when it becomes realized? and that's probably the best I can do

Cora (she/her)14:02:23

that's expected behavior, imo, it's how it works in other languages I've used advisory locks from

👍 4