pg_try_advisory_lock and pg_advisory_unlock always return false

Question

I have a node.js app that uses PostgreSQL as a database.

When I try to run a migration file (using node-pg-migrate package), I keep getting an error: Another migration is already running.

Below is the piece of source code of the package that's throwing the error.

I've tried running these commands manually and both of them return false:

select pg_try_advisory_lock(7241865325823964) as "lockObtained";

select pg_advisory_unlock(7241865325823964) as "lockReleased";

After some googling I've found the query below that should show the blocking queries:

SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

However, it gives me 0 results.

---

What should I do here to release this advisory lock by its id? Why could this happened? And what should actually be done to avoid this error in future?

Answer 1

Someone else holds the lock. Figure out who and why, and maybe terminate that backend.

pg_try_advisory_lock returns false because it can't get the lock as someone else already holds it, and it is not willing to wait because that is what try means here.

pg_advisory_unlock returns false because you can't release other sessions locks, only your own.

You can't find any blocking queries because there are none. That is what the try means: don't block, just immediately return false. If there is no one being blocked, then there is no one doing the blocking.

Modify your query to look for who is holding advisory locks, even if they are not blocking anyone:

select * from pg_locks join pg_stat_activity using (pid) where locktype='advisory';

Alternatively, open two sessions and in one take the lock without the try_, and in the other session query for who is blocking the first one.