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

"Automatically disconnect when idle" should check for temp tables #142

Closed
qwesda opened this Issue Dec 7, 2015 · 6 comments

Comments

Projects
None yet
2 participants
@qwesda

qwesda commented Dec 7, 2015

I've run into an issue where one could potentially have unexpected data loss when working with temp tables. While it is perfectly logical, that some state could be lost, it can be unexpected. Disconnection on idle makes sense most of the time, but maybe there is a way for you to check if temp tables exist before cutting the connection.

Something similar probably is true for SET and especially for SET search_path, which might lead to a query unexpectedly failing after the user has been inactive. Maybe there is a way to save this state before disconnecting and re-establishing the connection with the same configuration.

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Dec 7, 2015

Owner

You have some good points. Especially the search_path issue is dangerous, since you might end up executing queries against the wrong data. I'm not sure how easy it is to save state, I'd need to send a few SQL queries before disconnecting to get all the necessary state. I guess that's okay for idle timeouts, but it's not the best thing to do when sleeping.

So maybe Postico shouldn't actually close the connection, but just mark it as "stale" after a certain timeout. The next time the user tries to execute a query on a stale connection, Postico could send a test query (SELECT 1) to the server to test if the connection still works, and if not, ask the user if they want to reconnect.

What do you think?

Owner

jakob commented Dec 7, 2015

You have some good points. Especially the search_path issue is dangerous, since you might end up executing queries against the wrong data. I'm not sure how easy it is to save state, I'd need to send a few SQL queries before disconnecting to get all the necessary state. I guess that's okay for idle timeouts, but it's not the best thing to do when sleeping.

So maybe Postico shouldn't actually close the connection, but just mark it as "stale" after a certain timeout. The next time the user tries to execute a query on a stale connection, Postico could send a test query (SELECT 1) to the server to test if the connection still works, and if not, ask the user if they want to reconnect.

What do you think?

@qwesda

This comment has been minimized.

Show comment
Hide comment
@qwesda

qwesda Dec 7, 2015

I think that saving and re-establishing the connection state might be the better solution in the long run. I often leave 5-10 postico windows open over a long time and it's nice that they don't block resources on the server.

SELECT * FROM "pg_catalog"."pg_settings" WHERE "source" = 'session';
should be a good start for that.

qwesda commented Dec 7, 2015

I think that saving and re-establishing the connection state might be the better solution in the long run. I often leave 5-10 postico windows open over a long time and it's nice that they don't block resources on the server.

SELECT * FROM "pg_catalog"."pg_settings" WHERE "source" = 'session';
should be a good start for that.

@qwesda

This comment has been minimized.

Show comment
Hide comment
@qwesda

qwesda Dec 7, 2015

Related to the search_path issue:

I think it would be good if there would be something like a token field above the editor representing the current search_path (https://developer.apple.com/library/mac/documentation/UserExperience/Conceptual/OSXHIGuidelines/ControlsText.html#//apple_ref/doc/uid/20000957-CH51-SW4). This would give the user some context about how the query will be executed and allow for easy insertion, deletion and re-arraging of the schema names.

qwesda commented Dec 7, 2015

Related to the search_path issue:

I think it would be good if there would be something like a token field above the editor representing the current search_path (https://developer.apple.com/library/mac/documentation/UserExperience/Conceptual/OSXHIGuidelines/ControlsText.html#//apple_ref/doc/uid/20000957-CH51-SW4). This would give the user some context about how the query will be executed and allow for easy insertion, deletion and re-arraging of the schema names.

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Dec 7, 2015

Owner

I've been thinking about offering a control to edit the search_path for some time, and never thought of NSTokenField... that's brilliant.

Also, I wasn't aware of pg_settings; I thought it was only possibly to check settings using SHOW. pg_settings should make the saving/restoring part trivial. Thanks!

Owner

jakob commented Dec 7, 2015

I've been thinking about offering a control to edit the search_path for some time, and never thought of NSTokenField... that's brilliant.

Also, I wasn't aware of pg_settings; I thought it was only possibly to check settings using SHOW. pg_settings should make the saving/restoring part trivial. Thanks!

@qwesda

This comment has been minimized.

Show comment
Hide comment
@qwesda

qwesda Dec 7, 2015

you're welcome.

One issue that I can think of with a search_path-bar would arise if the search_path is changed on the server. I don't know if it is possible to "subscribe" to config changes.

This could be circumvented by setting the search_path to the same value as the system default after establishing the connection. Then the setting is set at the session level and will not be overwritten by higher level config changes.

qwesda commented Dec 7, 2015

you're welcome.

One issue that I can think of with a search_path-bar would arise if the search_path is changed on the server. I don't know if it is possible to "subscribe" to config changes.

This could be circumvented by setting the search_path to the same value as the system default after establishing the connection. Then the setting is set at the session level and will not be overwritten by higher level config changes.

@jakob

This comment has been minimized.

Show comment
Hide comment
@jakob

jakob Aug 3, 2017

Owner

Postico 1.2 will no longer close the connection automatically. Instead, it will try to keep connections alive.

In addition, it fetches server settings when sending the keep-alive commands, so that it can restore them when reconnecting after a lost connection (settings like search_path, datestyle, etc.)

Owner

jakob commented Aug 3, 2017

Postico 1.2 will no longer close the connection automatically. Instead, it will try to keep connections alive.

In addition, it fetches server settings when sending the keep-alive commands, so that it can restore them when reconnecting after a lost connection (settings like search_path, datestyle, etc.)

@jakob jakob closed this Aug 3, 2017

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