ODBC idle connection timeout

[nsec]

Hi, maybe we have facing an issue with the idle connection timeout on ASTERISK with UnixODBC and PostgreSQL.

Should the “sanitysql” on res_odbc.conf keep alived the connection with DB and avoid a idle connection timeout?

We can see the TCP Keepalived packets, probably sended from UnixODBC, but they are not sufficients.

Our odbcinst.ini:

	[ODBC]
	Pooling = No

	[PostgreSQL ANSI]
	Description=PostgreSQL ODBC driver (ANSI version)
	Driver=/usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so
	FileUsage=1
	Threading=2 

Our odbc.ini:

	[asterisk-connector]
	Description         = PostgreSQL connection
	Driver              = PostgreSQL ANSI
	Database            = asterisk
	Servername          = server
	UserName            = asterisk
	Password            = pass
	Port                = 5432
	KeepAlive           = 20
	KeepAliveInterval   = 10
	#KeepAliveIdle       = 5
	#KeepAliveCount      = 10
	ReadOnly            = No
	RowVersioning       = No
	ShowSystemTables    = No
	ShowOidColumn       = No
	FakeOidIndex        = No
	ConnSettings        =

And this is our res_odbc.conf:

	[asterisk]
	enabled => yes
	logging => yes
	dsn => asterisk-connector
	max_connections => 3
	sanitysql => select count(*) from my_table
	;connect_timeout => 10
	;negative_connection_cache => 10
	slow_query_limit => 100
	pre-connect => yes

Where are we wrong?

Any help will be apreccieted

Many thanks

[jcolp]

From an Asterisk perspective there isn’t a keep alive mechanism. When a connection is pulled from the pool the “connection dead” functionality of UnixODBC is used, and if that is unavailable for the driver then it falls back to sanitysql to do a query to make sure the connection is viable before giving it to the requester.

[nsec]

As per indications, we moved our analysis to the driver and we found the problem.

According to official odbc-postgresql documentation (How to specify as a connection option), the problem was the Keepalive options.

Those are the correct options that keep the connection alive:

...
KeepaliveTime=
KeepaliveInterval=
...