Unable to set timeout - defaults to 15 minutes :(

[aleaficionado]

Hi,

We're getting intermittent timeout error while querying Redshift: Error: Failed to prepare query: SSL SYSCALL error: Connection timed out.

While this occurred, I checked the Redshift connection list and no connections were present. While I know there are keepalive settings to make this occur less frequently, there doesn't seem to be a way to set the client timeout so that if e.g. a connection is dropped by a firewall, the user does not end up waiting for 15 minutes.

I guess I am looking for the equivalent of the socketTimeout parameter for the JDBC driver.

For what it's worth, I have also tested the connect_timeout parameter which only applied during connection (as per the docs). Additionally, statement_timeout doesn't seem to fit the bill as that refers to the overall execution time of a query.

[krlmlr]

Thanks. To repro this, do I need to connect, wait 15 minutes, and then issue a query?

[aleaficionado]

I suspect the steps are:

1. connect
2. sever the connection on the network [e.g. assume the flow is Laptop->LAN->Internet->Redshift, the connection should be severed on a device on the lan or upstream, or on a linux box set the keep alive to something like 1 day and wait].
3. Once the driver appears connected on the client side, but the connection disappears from the Redshift box, issue a query
4. the driver would then wait 15 minutes or so before receiving no response and raising the error above.

I'm guessing about #2 above but all the other steps are what is occasionally happening.

I've unsuccessfully tried to have a look at the DBI or Postgres package source code, but couldn't pinpoint the exact location where the connection is actually opened. I suspect whatever library is used to do so, would have a parameter like a socket timeout to close it if a reply isn't received within the allotted time - this probably defaults to 15 minutes.

Also had a look at the pq driver and sadly there doesn't seem to be any such option so if you're using it, this might be one for the driver team.

Thank you, A

[krlmlr]

Thanks. I don't understand item 3 -- how do I check that condition? How do I check the Redshift connection list?

[aleaficionado]

You can use stv_sessions. I think the Postgres equivalent would be pg_stat_activity.

[krlmlr]

Are you suggesting to run stv_sessions() in a second connection? I don't fully understand "disappears from the Redshift box".

[krlmlr]

Were you able to resolve this?