New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add Connection Keep-Alive Support #310

Closed
jakob opened this Issue Jan 12, 2017 · 3 comments

Comments

Projects
None yet
3 participants
@jakob
Owner

jakob commented Jan 12, 2017

A couple of users have reported issues that the connection drops after a certain time of inactivity. This can be caused by a number of reasons; a possible cause are Firewalls that automatically drop idle connections.

Postico reconnects automatically when the connection is lost. However, sometimes it can take a long time for Postico to detect that a connection is lost. This is especially true for SSH connections (#66). Reducing timeouts is not really an option, since it would cause issues when using Postico over slow connections.

I've tried to address this issue by adding a setting that preemptively disconnects from the server after a period of inactivity, but that has been causing issues with temporary tables and settings like search_path (#142).

I believe the best way to go forward is to add a Keep-Alive mechanism to Postico. This would ensure that connections aren't dropped by over-protective firewalls, and it would also allow Postico to detect a lost connection more quickly. Such a keep-alive can be implemented on various levels (TCP, SSH, PostgreSQL). I think the best way to implement it is on the highest level, ie. by sending a no-op query like SELECT 1; to the PostgreSQL server -- this way we can be sure everything works.

There is a potential downside to a Keep-Alive mechanism: If you have an unreliable connection with intermittent connectivity, Postico will close the connection, even if the connection issues are only temporary. (Real Live Example: I'm on a train, entering a tunnel. There is no connection inside the tunnel. If Postico tries to send a Keep-Alive query while the train is in the tunnel, it will think the connection is lost, and I will need to reconnect later. Without Keep-Alive, Postico wouldn't ever know that we drove through a tunnel)

Another disadvantage of Keep-Alives is that Postico would need to periodically run code, increasing idle CPU usage. However, I assume that sending a single command to the server eg. once per minute should have minimal impact.

@atdfairfax

This comment has been minimized.

Show comment
Hide comment
@atdfairfax

atdfairfax Apr 4, 2017

It would be great if you could hook that check back into some kind of status notification in the UI as very often when Postico looses connection to the server, the just spinner keeps spinning and I have no way of telling if the query is still running or if it has dropped (without cancelling the query first).

atdfairfax commented Apr 4, 2017

It would be great if you could hook that check back into some kind of status notification in the UI as very often when Postico looses connection to the server, the just spinner keeps spinning and I have no way of telling if the query is still running or if it has dropped (without cancelling the query first).

@Gwildor

This comment has been minimized.

Show comment
Hide comment
@Gwildor

Gwildor May 19, 2017

I mostly use Postico with AWS RDS instances with SSH tunnels to EC2 instances, which have a very low timeout (most of the times as low as a minute). Therefore I get a lot of timeouts, just sitting behind my desk. For using the terminal I solved this by setting ServerAliveInterval 50 in my SSH config, which works fine. This might be a separate feature request, but it would be nice if Postico can listen to that, or if I can set it manually when editing the connecting.

It might even be a great feature if Postico could listen to my SSH config as is, so that I only have to enter the name of a host I've defined there, and Postico picks up the user and hostname to use for the tunnel.

Gwildor commented May 19, 2017

I mostly use Postico with AWS RDS instances with SSH tunnels to EC2 instances, which have a very low timeout (most of the times as low as a minute). Therefore I get a lot of timeouts, just sitting behind my desk. For using the terminal I solved this by setting ServerAliveInterval 50 in my SSH config, which works fine. This might be a separate feature request, but it would be nice if Postico can listen to that, or if I can set it manually when editing the connecting.

It might even be a great feature if Postico could listen to my SSH config as is, so that I only have to enter the name of a host I've defined there, and Postico picks up the user and hostname to use for the tunnel.

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Jun 28, 2017

Owner

I've now added Connection Keep-Alive support to Postico. I'd love to hear your comments on build 1703 (download here: https://eggerapps.at/postico/builds/)

The setting is enabled by default, but can be disabled in the preferences. It sends a command every 45 seconds (if the connection is idle). This should fix the problem in most cases. (One possible issue are long running queries -- a very strict firewall might close the connection while Postico is waiting for a long-running query to return)

Owner

jakob commented Jun 28, 2017

I've now added Connection Keep-Alive support to Postico. I'd love to hear your comments on build 1703 (download here: https://eggerapps.at/postico/builds/)

The setting is enabled by default, but can be disabled in the preferences. It sends a command every 45 seconds (if the connection is idle). This should fix the problem in most cases. (One possible issue are long running queries -- a very strict firewall might close the connection while Postico is waiting for a long-running query to return)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment