Timeout settings for PostgreSQL via ODBC-Connector Package

[Sonja_Bauernfeind]

Default timeout values can cause the PostgreSQL connection query to stop after 30 seconds. This article explains how to set up timeouts using the PostgreSQL driver via the ODBC-Connector Package.

Environment

Qlik ODBC Connector Package 
QlikView 

Resolution

The timeout attribute and timeout value need to be set up in the ODBC connection Advanced settings. See Fig 1.

Fig 1

Name: This is the attribute
Value: This is the timeout value.

Example: Name = "QT" and Value = "-1" will result that the QueryTimeout (QT) does not time out at all. Additional timeout attributes can be combined and added as well.

The following timeouts are available:

Load Balance Timeout

Attribute LoadBalanceTimeout (LBT)

Purpose 

Specifies the number of seconds to keep inactive connections open in a connection pool. An
inactive connection is a database session that is not associated with an ODBC connection handle,
that is, a connection in the pool that is not in use by an application.

Valid Values
0 | x
where:
x
is a positive integer that specifies a number of seconds.

Behavior
If set to 0, inactive connections are kept open.

DataDirect Connect® Series for ODBC: User's Guide:Version 7.1.2 413
If set to x, inactive connections are closed after the specified number of seconds passes.
Notes
• The Min Pool Size option may cause some connections to ignore this value.
• This connection option can affect performance.
Default
0

Login Timeout

Attribute LoginTimeout (LT)

Purpose

The number of seconds the driver waits for a connection to be established before returning control
to the application and generating a timeout error.To override the value that is set by this connection
option for an individual connection, set a different value in the SQL_ATTR_LOGIN_TIMEOUT
connection attribute using the SQLSetConnectAttr() function.

Valid Values
-1 | 0 | x
where:
x
is a positive integer that represents a number of seconds.
Behavior
If set to -1, the connection request does not time out. The driver silently ignores the
SQL_ATTR_LOGIN_TIMEOUT attribute.
If set to 0, the connection request does not time out, but the driver responds to the
SQL_ATTR_LOGIN_TIMEOUT attribute.
If set to x, the connection request times out after the specified number of seconds unless the
application overrides this setting with the SQL_ATTR_LOGIN_TIMEOUT attribute.
Default
15

Query Timeout

Attribute QueryTimeout (QT)

Purpose

The number of seconds for the default query timeout for all statements that are created by a
connection. To override the value set by this connection option for an individual statement, set a
different value in the SQL_ATTR_QUERY_TIMEOUT statement attribute on the SQLSetStmtAttr()
function.

Valid Values
-1 | 0 | x Load Balance Timeout
Attribute
LoadBalanceTimeout (LBT)
Purpose
Specifies the number of seconds to keep inactive connections open in a connection pool. An
inactive connection is a database session that is not associated with an ODBC connection handle,
that is, a connection in the pool that is not in use by an application.
Valid Values
0 | x
where:
x
is a positive integer that specifies a number of seconds.
Behavior
If set to 0, inactive connections are kept open.
DataDirect Connect® Series for ODBC: User's Guide:Version 7.1.2 413
If set to x, inactive connections are closed after the specified number of seconds passes.
Notes
• The Min Pool Size option may cause some connections to ignore this value.
• This connection option can affect performance.
Default
0

Login Timeout

Attribute LoginTimeout (LT)

Purpose

The number of seconds the driver waits for a connection to be established before returning control
to the application and generating a timeout error.To override the value that is set by this connection
option for an individual connection, set a different value in the SQL_ATTR_LOGIN_TIMEOUT
connection attribute using the SQLSetConnectAttr() function.

Valid Values
-1 | 0 | x
where:
x
is a positive integer that represents a number of seconds.
Behavior
If set to -1, the connection request does not time out. The driver silently ignores the
SQL_ATTR_LOGIN_TIMEOUT attribute.
If set to 0, the connection request does not time out, but the driver responds to the
SQL_ATTR_LOGIN_TIMEOUT attribute.
If set to x, the connection request times out after the specified number of seconds unless the
application overrides this setting with the SQL_ATTR_LOGIN_TIMEOUT attribute.
Default
15

Query Timeout

Attribute QueryTimeout (QT)

Purpose

The number of seconds for the default query timeout for all statements that are created by a
connection. To override the value set by this connection option for an individual statement, set a
different value in the SQL_ATTR_QUERY_TIMEOUT statement attribute on the SQLSetStmtAttr()
function.

Valid Values
-1 | 0 | x
where:
x
DataDirect Connect® Series for ODBC: User's Guide:Version 7.1.2 419
is a positive integer that specifies a number of seconds.
Behavior
If set to -1, the query does not time out. The driver silently ignores the
SQL_ATTR_QUERY_TIMEOUT attribute.
If set to 0, the query does not time out, but the driver responds to the
SQL_ATTR_QUERY_TIMEOUT attribute.
If set to x, all queries time out after the specified number of seconds unless the application overrides
this value by setting the SQL_ATTR_QUERY_TIMEOUT attribute.
Default
0
where:
x
DataDirect Connect® Series for ODBC: User's Guide:Version 7.1.2 419
is a positive integer that specifies a number of seconds.
Behavior
If set to -1, the query does not time out. The driver silently ignores the
SQL_ATTR_QUERY_TIMEOUT attribute.
If set to 0, the query does not time out, but the driver responds to the
SQL_ATTR_QUERY_TIMEOUT attribute.
If set to x, all queries time out after the specified number of seconds unless the application overrides
this value by setting the SQL_ATTR_QUERY_TIMEOUT attribute.
Default
0