Using a connection pooler can hold locks forever

[codegoalie]

Hello!

We are using tern in-code to run migrations when our application boots. We have several instances of our application(s) and so the lock to prevent them stomping on each other is important. Additionally, we're using pgdog in a high availability setup with multiple instances. We've noticed that the pg_advisory_lock can get lost on a different back-end and held forever.

Would you be open to using pg_advisory_xact_lock instead?

Since the xact lock is automatically released at the end of the transaction, it makes the MigrateTo case tricky to implement; especially handling disable-tx. However, since the lock isn't needed to run the actual migrations and is used more as a mutex, we could open two connections. The first would hold the lock. The second would run the migrations in separate transactions (or not if disabled) and then update the schema version. Then the first would release the lock.

I'm happy to try my hand at implementing this too.

Thanks so much for this library and the whole pgx ecosystem!!

[jackc]

I'd prefer to avoid that complexity if possible. Multiple connections might make using tern as a library more tricky as well. IIRC, you can supply an existing connection so the core tern library doesn't have to have connection management. I don't have any experience with pgdog, but my general recommendation when running tern through a connection pooler has been to not do that. Connect directly to the master.

If that's not possible, my next choice would be to make locking more configurable. I've considered making the lock and unlock system just run arbitrary SQL. That would let tern work with PG-like databases that don't implement advisory locks. Maybe even just a complete LOCK on the schema_version table. Or to get really crazy, maybe even accept a shell script as the lock and unlock command.