Support read timeout for queries

[rajeshucsb]

Currently there is no way to specify a read timeout for queries.
The sql client has an option for statement_timeout
http://www.postgresql.org/docs/9.3/static/runtime-config-client.html

And JDBC has socketTimeout
https://jdbc.postgresql.org/documentation/head/connect.html

Something similar for golang pq driver will be very useful.

[johto]

The sql client has an option for statement_timeout
http://www.postgresql.org/docs/9.3/static/runtime-config-client.html

That's a server-side option and requires no support in the client. You can use statement_timeout with pq just fine.

And JDBC has socketTimeout
https://jdbc.postgresql.org/documentation/head/connect.html

Which is an absolute abomination, and only causes problems in my opinion.

Something similar for golang pq driver will be very useful.

What's the problem you're trying to solve?

[bobbytables]

So I'm looking at using statement_timeout, and a go-nuts post has

Controlling it at pool level is trivial; just set statement_timeout in the second argument to sql.Open().
See: https://groups.google.com/forum/#!topic/golang-nuts/l9vga1aAsHI

You can easily Exec the query to set the statement timeout option right when you connect but how would this behave with connection pooling?

[johto]

You can easily Exec the query to set the statement timeout option right when you connect but how would this behave with connection pooling?

It doesn't work. The connection pool doesn't tell you when it's connecting or re-connecting, and you're not even guaranteed to get the same connection on two subsequent executions.

However, you could use a transaction and SET LOCAL.

[bobbytables]

@johto Yeah that's what I figured. I took a gander at the connection options for postgres and it doesn't even seem possible at connection time to set this parameter.

[johto]

I took a gander at the connection options for postgres and it doesn't even seem possible at connection time to set this parameter.

Sure it is. But because database/sql only exposes a pool of connections, you have to have it for all connections in the pool. Just set it in the connection string like I explained in the post you linked to.

[jmscott]

with regard to timing out all queries in a connection pool, i am not
convinced adding the option "statement_timeout=" works. for example,
statement_timeout=1 is not timeing out my pooled queries. my version of pq
was built yesterday, pulled from github via "go get ...". the database is
pg9.6beta3.

-j

On Tue, Aug 9, 2016 at 11:03 PM, Marko Tiikkaja notifications@github.com
wrote:

I took a gander at the connection options for postgres and it doesn't even
seem possible at connection time to set this parameter.

Sure it is. But because database/sql only exposes a pool of connections,
you have to have it for all connections in the pool. Just set it in the
connection string like I explained in the post you linked to.

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
#450 (comment), or mute
the thread
https://github.com/notifications/unsubscribe-auth/ABWsKDEKaenZmzEn2B8Ue5shH1v9hovoks5qeU13gaJpZM4H6DGs
.

[peter-mogensen]

Just a (possibly off-topic) note ... server-side statement_timeout doesn't help much if the issue is the network. I have problems making queries timeout on dead network connections - like when a NAT router somewhere decides to forget my connection.

[pneisen]

We were having the same issue where statement_timeout was not working for us if our RDS instance failed over or if the network went down. We would have connections hang indefinitely. After a failed pull request to add read and write timeouts to lib/pq, I wrote this driver wrapper: https://github.com/Kount/pq-timeouts

It is working well for us, and might work well for you.

[chrispassas]

I've been running into this issue several times a day. I'm using statement_timeout but a few times a day the message never gets back to the client. The result is postgres stops interacting with the client and I get hung on rows.Next() indefinitely.

Even if I use a switch / timer to stop waiting the goroutine that ran the query is hung for ever. This is happening in an API so I can't just restart it every time this happens.

[peter-mogensen]

It's possible to write a wrapper around the postgres driver to overwrite the Dial and DialTimeout functions to create net.Conn which are wrappers around another net.Conn setting timeouts on read and write.

[kafan1986]

I wanted to have different timeouts for different queries using statement_timeout. But parallel calls to these APIs were affecting the timeout of all the connections between my server and db. Seems like you can not have different timeouts using statement_timeout alone, one way is to put the entire query inside a transaction and use "set local statement_timeout".

[clark-archer]

@kafan1986 Or you can have multiple connection pools if you only have a few different timeout requirements.

[u007]

how about connection pool full time out? in my application, since connection is initiated on start of server, there is not way to timeout if the db pool is full for a long period of time