Add `db-pool-query-wait-timeout` config

[steve-chavez]

Problem

• PostgREST doesn't have a timeout for how long a request should wait for obtaining a connection from the db pool.
• Nginx(or other proxy) has a request timeout.

When a timeout occurs, this usually causes an error on the client side that has poor information: usually just a 502 Bad Gateway or 408 Request Timeout. This happens on servers with high amount of traffic.

The problem can be reproduced with an RPC that has a pg_sleep() inside plus Nginx with the req timeout.

(I haven't checked if the request will still reach the database once the proxy timeout occurs, it's possible)

Proposal

Have a db-pool-query-wait-timeout config that produces a timeout from our side, similar to the pgbouncer query_wait_timeout. The pgbouncer config has a default of 120 but in our case it should be lower since Nginx has a 60 second default. Maybe it should be like 50 seconds.

On the http side, when this happens we should return a 529 Site is overloaded status. Optionally with a Retry-After header, maybe we can use the pool waiter queue size to determine a good value.

Edit: For now maybe we should just keep it simple and return a 504 Gateway Timeout with a descriptive error message.

---

This would require a patch in hasql-pool.

[steve-chavez]

To Reproduce

Start a postgrest with a sleep function:

nix-shell

cat << 'EOF' >> test/spec/fixtures/schema.sql

CREATE OR REPLACE FUNCTION sleep() returns void as $$
  select pg_sleep(50);
$$ language sql;

EOF

PGRST_DB_POOL=1 PGRST_DB_ANON_ROLE='postgrest_test_anonymous' postgrest-with-postgresql-14 postgrest-run

On other terminals do:

# take the single pool connection
curl 'localhost:3000/rpc/sleep'

# This one will wait 50 seconds until the sleep request finishes
curl 'localhost:3000/projects'