Deadlocked/hung pool when connected to Postgres through Supavisor

[nikhilro]

Hey @porsager, thanks for the package.

Creating this issue mostly in hopes that someone else has ran into this issue.

We use Supabase for our Postgres DB and connect through their pooling service Supavisor in transaction mode; Supavisor is an alternative to pgBouncer (link). On postgres.js side, we set prepare: false.

What we're seeing is that the postgres.js pooler will start swallowing queries at some point. Imagine a function like:

async function userGet(id: string) {
  console.log("trying to get the user");
  const users = await db<User[]>`SELECT * FROM user WHERE id=${id}`;
  console.log("got the user!");
  return users;
}

After a while, this will print "trying to get the user" but not "got the user!". Does this ring any bells?

P.S. been trying to find an isolated script to reproduce but unsuccessful so far. the only reproduction is live production traffic.

[nikhilro]

I'm more confident that is an issue with Supavisor under load. A thing that would be helpful is postgres.js statement limit.

I know we have:

   connection: {
     statement_timeout: 1000 * 60 * 0.5, // 30 seconds, pg expects milliseconds
   },

But, that is not useful when connecting to transaction mode poolers.

Is there an easy way to "abort" the query if it's taking too long?