Postgres error: "you don't own a lock of type ExclusiveLock" about rihanna HOT 5 CLOSED

Thanks for the report @tombh

from rihanna.

I'm getting this too after updating to the latest version.

v1.3.1...v1.3.4

Unsure what the problem is here, but there's very little code to explore!

from rihanna.

@lpil I'm not certain this issue is resolved. I'm still getting a boatload of these errors in production with version 1.3.5. All coming with a similar stacktrace to what @tombh mentioned, following a mark_successful.

from rihanna.

Actually... chalk this one up to novice DBA-ing right here. I'm gonna leave my notes in case anyone else runs into this.

Short answer: Advisory locks were sometimes not being held onto because of my pg_bouncer connection pool settings, leading to the following symptoms:

• An inordinate number of multiply-executed jobs
• Jobs being "orphaned" in the database with no failed_at but seemingly being "skipped over" by the workers (...because the DB was holding a lock for a process that didn't close it)
• The error @tombh mentioned above showing up in logs from Rihanna.Job.release_lock/2

My solution was specific to my DBaaS provider (Digital Ocean), and may be translate-able to other scenarios:

• The default Pooling Mode for pg_bouncer was set to Transaction Mode, which I believe is what was causing the orphaning above
• In the text for my seemingly-successful switch, Session Mode, their documentation states:

Session mode is useful when your application uses prepared statements, advisory locks, listen/notify, or other features which operate on a session level rather than a transaction level.

...which has seemed to me to be the key here.

I'm not certain this is the fix. But it seems to be working at the moment. If this is an uncommon occurrence, then my note here may help folks out. And if it's something we suspect folks might run into with other DB providers, maybe there's room to flag it as a gotcha for folks in documentation?

from rihanna.

@lperiodbose Yup when I saw this my first thought was pgbouncer, which would break any application that relies on advisory locks.

Would love to see a PR adding this to the README or similar!

from rihanna.