distal-attribute
distal-attribute
distal-attribute
distal-attribute

PostgreSQL should not set Query Timeout by default or increase the default

NaX posted 3 months ago in General

I have been very excited about the adding of PostgreSQL support to HeidiSQL and have been using and testing it out for about a year now.

HeidiSQL is my preferred DB client for anything and the latest update 9.4 improved the PostgreSQL support a great deal, however we always had a problem with databases more than a few dozen tables or tables with large numbers of columns were would get statement timeouts and the table list would not populate.

This forced us to use jump between PGAdmin3 and HeidiSQL depending on the db we were working on. Recently this frustration lead to some experimentation with HeidiSQL advanced connection settings leading us to find that if we increased the Query Timeout from 30 to 300 the problem was hugely reduced and would only occurred in some very rare cases. If we set it to zero (0) it disappeared completely.

While experimenting we saw that the Query Timeout connection setting in the UI would call a statement_timeout.

SET statement_timeout TO 0;

Based on the postgreSQL docs the statement_timeout value is set in milliseconds and the HeidiSQL default of 30 is way to low. The default value is zero and it is recommend by the docs to not set this value at all.

Ref: www.postgresql.org/docs/9.5/static/runtime-config-client.html

statement_timeout (integer)
Abort any statement that takes more than the specified number of milliseconds, starting from the time the command arrives at the server from the client. 

If log_min_error_statement is set to ERROR or lower, the statement that timed out will also be logged. A value of zero (the default) turns this off.

Setting statement_timeout in postgresql.conf is not recommended because it would affect all sessions.

Anybody having problems with HeidiSQL and PostgreSQL would be advised to change the query timeout setting to zero and I would like to put forward the suggestion that the default value for new connection be change to zero as per the recommendation in the docs.

Thanks for a great DB Client.

ansgar posted 3 months ago

r5133 multiplies the timeout setting by 1000 before executing that SET statement_timeout TO X query.

Please login to leave a reply, or register at first.