In pgjdbc we have:
loginTimeoutconnectTimeoutsocketTimeoutcancelSignalTimeoutBut it isn't clear to me what's the difference (when are they applied) between loginTimeout, connectTimeout and socketTimeout.
As documented in the PostgreSQL JDBC documentation:
loginTimeout = int
Specify how long to wait for establishment of a database connection. The timeout is specified in seconds.
connectTimeout = int
The timeout value used for socket connect operations. If connecting to the server takes longer than this value, the connection is broken. The timeout is specified in seconds and a value of zero means that it is disabled.
socketTimeout = int
The timeout value used for socket read operations. If reading from the server takes longer than this value, the connection is closed. This can be used as both a brute force global query timeout and a method of detecting network problems. The timeout is specified in seconds and a value of zero means that it is disabled.
cancelSignalTimeout = int
Cancel command is sent out of band over its own connection, so cancel message can itself get stuck. This property controls "connect timeout" and "socket timeout" used for cancel commands. The timeout is specified in seconds. Default value is 10 seconds.
The connectTimeout and socketTimeout are timeouts on low-level socket operations. The connectTimeout governs the time needed to establish a TCP socket connection. Establishing a TCP connection doesn't guarantee a login (it doesn't even guarantee that you're connecting to a PostgreSQL server, just that you connected to something that accepted your TCP connection). A socketTimeout governs the time a socket can be blocked waiting to read from a socket. This involves all reads from the server, not just during connect, but also during subsequent interaction with the server (eg executing queries).
On the other hand loginTimeout governs the PostgreSQL protocol operation of connecting and authenticating to the PostgreSQL server. This involves establishing a TCP connection followed by one or more exchanges of packets for the handshake and authentication to the PostgreSQL server (I'm not familiar with the details of the PostgreSQL protocol, so I can't be very specific).
Exchanging these packets can take additional time, or if you connected to something that isn't a PostgreSQL server the packet exchange may stall. It might be possible to solve this with careful control of both connectTimeout and socketTimeout, but there are no guarantees (eg data is being exchanged, but the login never completes). In addition, as the socketTimeout also governs all other operations on the connection, you may want to set it higher (eg for other operations that take a long time to get a response back) than you are willing to wait for the login to complete.
The cancelSignalTimeout is used as the connect and socket timeout of the separate TCP connection used for cancel commands.