LISTEN/NOTIFY doesn't work on read replicas

[steve-chavez]

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.

[steve-chavez]

Now that NOTIFY pgrst also restarts the pool connections(#2639). Terminating the backends could be an effective replacement I think. Though NOTIFY would still be more graceful as requests won't suddenly have to wait.

[steve-chavez]

We could bound the time to wait for a recover by the acquisition timeout. This way no request would fail.

Doing the above would imply having a Thread.delay to avoid the failed request. That's a big risk(an error could make the thread wait forever), so I'll not do that an instead fail fast with the Retry header. Clients can then follow the retry hint.

Edit: There might be a way forward on #1766 (comment), will reopen

[steve-chavez]

This issue would be solved/avoided by #2429 since it would give us native read replica support. No need to start a postgrest for each read replica.

[steve-chavez]

Currently we silenty fail when LISTEning on a replica diogob/hasql-notifications#24, likely why we never got a report about this.