pg_timetable running against read-only replica

[seanzechman]

We have pg_timetable daemon running locally on all of our postgres servers. We are testing a POC and noticed that on read-only replicas pg_timetable takes all of available connections. Is this by design and is there a way to stop the build up of sessions? We would like pg_timetable to work after a failover to a standby node but right now our standby node has issues because max_connections is reached due to this problem

[notice:Cannot obtain lock on a replica. Please, use the primary node] [severity:NOTICE] #33[0mNotice received

pg_timetable:
Version: 5.3.0
DB Schema: 00534
Git Commit: e6a13ee
Built: 2023-03-13T11:38:30Z

[pashagolub]

Hello.

Sounds like a bug. Are there any specific steps to reproduce such a behavior?

Thanks in advance

[seanzechman]

You could set up replication - and have pg_timetable start against the read-only cluster

[seanzechman]

I think it is because it is logging to the table and it cannot do that in a read-only cluster

[seanzechman]

I think the session needs to disconnect if it cannot get a lock but it stays connected - and every minute the sessions pile up

[seanzechman]

I think there is a bug as we get a proper error but then the session stays connected but idle. After [max_connections] number of minutes the cluster runs out of non reserved connection slots.. I think proper functionality is the session should exit when it errors.

[notice:Cannot obtain lock on a replica. Please, use the primary node] [severity:NOTICE] https://github.com/cybertec-postgresql/pg_timetable/issues/33[0mNotice received

[seanzechman]

Question - if we have both local pg_timetable services connecting to pgpool VIP - would chains and tasks run twice or would one node see the job has already run?

[pashagolub]

Chains are able to run only on primary node. Because there are some writes. It's impossible to run pg_timetable against replica at the moment

[pashagolub]

Would you please try fix in #568

[seanzechman]

This seems to work - we get messages like this and the sessions do not remain

{"error":"Cannot obtain lock for a session","level":"error","msg":"Connection failed","time":"2023-05-23T14:26:37Z"}
{"level":"info","msg":"Sleeping before reconnecting...","time":"2023-05-23T14:26:37Z"}

May I ask if there are any consequences to this for the active node? What is the effect of timeout=0?

[pashagolub]

Timeout=0 means try to reconnect always. In other words to work in a daemon mode. One can set finite timeout after which pg_timetable will exit with an exit code > 0. It might useful for monitoring, e.g. if database isn't responding during 5min this indicates fatal failure

[pashagolub]

Solved by #568! Thanks a lot for your help!