PostgREST
Handle `connection limit` gracefully
Problem
Having:
# in test/spec/fixtures/schema.sql
alter user postgrest_test_authenticator with connection limit 2;
PGRST_DB_CHANNEL_ENABLED=false PGRST_DB_POOL=3 postgrest-with-postgresql-16 -f test/spec/fixtures/load.sql \
postgrest-run
Makes PostgREST reset its schema cache and pool when exceeding the connection limit:
[nix-shell:~/Projects/postgrest]$ postgrest-parallel-curl 3 localhost:3000/projects
19/Jan/2024:13:28:40 -0500: {"code":"PGRST000","details":"connection to server on socket \"/run/user/1000/postgrest/postgrest-with-postgresql-16-tWK/socket/.s.PGSQL.5432\" failed: FATAL:
too many connections for role \"postgrest_test_authenticator\"\n","hint":null,"message":"Database connection error. Retrying the connection."}
19/Jan/2024:13:28:40 -0500: Attempting to connect to the database...
127.0.0.1 - postgrest_test_anonymous [19/Jan/2024:13:28:41 -0500] "GET /projects HTTP/1.1" 503 - "" "curl/7.88.1"
19/Jan/2024:13:28:40 -0500: Connection successful
19/Jan/2024:13:28:40 -0500: Config reloaded
19/Jan/2024:13:28:41 -0500: Schema cache loaded
PostgREST still works normally but there's unnecessary work done.
Solution
Log the too many connections error like above but don't reset the schema cache or pool.
Alternatives
Limit the db-pool size based on CONNECTION LIMIT?
Log the too many connections error like above but don't reset the schema cache or pool.
+1
Limit the db-pool size based on CONNECTION LIMIT?
i.e. silently? -1
I sure want to know by looking at the logs, that somehow my configuration is off.
@steve-chavez @wolfgangwalther @laurenceisla Where exactly in the code schema cache and pool are reset? I understand that are reloaded but I can't seem to find any place where they are reset.
I understand that are reloaded but I can't seem to find any place where they are reset.
I think that reloaded = reset in this case. There is no "default" value for the schema cache or connection to be reset to AFAIK.
