Problem
Read replicas don’t support LISTEN/NOTIFY(ref). Thus, PostgREST NOTIFY reloading won’t work in them.
As a workaround, we can kill the pool connections:
select pg_terminate_backend(pid)
from pg_stat_activity
where application_name = 'postgrest';
(can be enabled conditionally with pg_is_in_recovery())
This will cause a schema cache reload because of Automatic Recovery.
However, the recovery won't be graceful. Two requests have to be done to start recovery.
$ curl localhost:3000/projects -i
HTTP/1.1 500 Internal Server Error
Transfer-Encoding: chunked
Date: Fri, 12 May 2023 02:13:02 GMT
Server: postgrest/11.0.1 (4197d2f)
Content-Type: application/json; charset=utf-8
{"code":"57P01","details":null,"hint":null,"message":"terminating connection due to administrator command"}
$ curl localhost:3000/projects -i
HTTP/1.1 503 Service Unavailable
Transfer-Encoding: chunked
Date: Fri, 12 May 2023 02:13:10 GMT
Server: postgrest/11.0.1 (4197d2f)
Retry-After: 0
Content-Type: application/json; charset=utf-8
{"code":"PGRST001","details":"no connection to the server\n","hint":null,"message":"Database client error. Retrying the connection."}
$ curl localhost:3000/projects -i
HTTP/1.1 200 OK
Transfer-Encoding: chunked
Date: Fri, 12 May 2023 02:13:14 GMT
Server: postgrest/11.0.1 (4197d2f)
Content-Range: 0-4/*
Content-Location: /projects
Content-Type: application/json; charset=utf-8
[{"id":1,"name":"Windows 7","client_id":1},
{"id":2,"name":"Windows 10","client_id":1},
{"id":3,"name":"IOS","client_id":2},
{"id":4,"name":"OSX","client_id":2},
{"id":5,"name":"Orphan","client_id":null}]Solution
- Interpret the
57P01 code as a 503 to start the recovery. Only one request would fail and the second will succeed.
- Thanks to recent refactors(see
runDbHandler) it might possible to retry the query before getting the 503 response. We could bound the time to wait for a recover by the acquisition timeout. This way no request would fail.
- Document the limitation and update the event trigger in docs.
Problem
Read replicas don’t support LISTEN/NOTIFY(ref). Thus, PostgREST NOTIFY reloading won’t work in them.
As a workaround, we can kill the pool connections:
(can be enabled conditionally with
pg_is_in_recovery())This will cause a schema cache reload because of Automatic Recovery.
However, the recovery won't be graceful. Two requests have to be done to start recovery.
Solution
57P01code as a 503 to start the recovery. Only one request would fail and the second will succeed.runDbHandler) it might possible to retry the query before getting the 503 response. We could bound the time to wait for a recover by the acquisition timeout. This way no request would fail.