Docs: Using CPTimeout = 0 does not consistently disable connection pooling

[gordthompson]

How to use connection pooling with unixODBC shows an example of two driver definitions that differ only in that one supports connection pooling and the other one doesn't.

[INTERBASE-P]
Description     = Easysoft Driver for Interbase
Driver      	= /usr/local/lib/libib6odbc.so
Setup       	= /usr/local/lib/libib6odbcS.so
FileUsage       = 1
DontDLClose     = 1
CPTimeout       = 120

[INTERBASE]
Description     = Easysoft Driver for Interbase
Driver      	= /usr/local/lib/libib6odbc.so
Setup       	= /usr/local/lib/libib6odbcS.so
FileUsage       = 1
DontDLClose     = 1
CPTimeout       = 0

along with the text "To pool a individual driver the 'CPTimeout' value is set to a non zero numeric value."

I was messing around with unixODBC 2.3.12 and msodbcsql18 (ODBC Driver 18 for SQL Server) and noticed that CPTimeout = 0 did not disable connection pooling, but CPTimeout = -1 did.

[gordthompson]

This appears to be related to the inherent imprecision of timing when a connection is closed and immediately reopened.

from time import sleep

import pyodbc

connection_string = (
    "Driver=ODBC Driver 18 for SQL Server;"
    "Server=192.168.0.199;"
    "Database=test;"
    "UID=scott;"
    "PWD=tiger^5HHH;"
    "TrustServerCertificate=yes;"
)

def get_trans_iso(cnx):
    return cnx.execute("""\
SELECT CASE transaction_isolation_level 
    WHEN 0 THEN 'Unspecified' 
    WHEN 1 THEN 'ReadUncommitted' 
    WHEN 2 THEN 'ReadCommitted' 
    WHEN 3 THEN 'Repeatable' 
    WHEN 4 THEN 'Serializable' 
    WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id = @@SPID
    """).fetchval()


pyodbc.pooling = True
pyodbc.odbcversion = "3.8"

passed = 0
failed = 0
for i in range(20):
    cnxn = pyodbc.connect(connection_string)
    iso = get_trans_iso(cnxn)
    assert iso == "ReadCommitted"
    cnxn.rollback()
    cnxn.set_attr(pyodbc.SQL_ATTR_TXN_ISOLATION, pyodbc.SQL_TXN_SERIALIZABLE)
    iso = get_trans_iso(cnxn)
    assert iso == "Serializable"
    cnxn.close()

    cnxn = pyodbc.connect(connection_string)
    iso = get_trans_iso(cnxn)
    if iso == "ReadCommitted":
        passed += 1
    else:
        failed += 1
    cnxn.close()
    sleep(1)
print(f"{passed=}, {failed=}")

With CPTimeout = 0 I see output like

passed=2, failed=18

whereas with CPTimeout = -1 I consistently get

passed=20, failed=0