Using a connection pooler can hold locks forever

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!!

Start at the MigrateTo flow and locate the current pg_advisory_lock handling, then trace how the disable-tx option affects transactions. Reproduce the behavior with multiple application instances through pgdog, and compare the lock and migration connections. Done means migrations remain mutually exclusive without a lock being held forever, including when transactions are disabled.

Written by the indexing model from the issue text.