From: davecramer (@davecramer) <noreply+davecramer@github.com>
To: postgresql-interfaces/psqlodbc <noreply+postgresql-interfaces-psqlodbc@github.com>
Subject: [postgresql-interfaces/psqlodbc] PR #225: Report dead connection when libpq has given up on it (#224)
Date: Tue, 27 Oct 2026 15:21:56 +0000
Message-ID: <gh-postgresql-interfaces-psqlodbc-225@github.com> (raw)
Refs #224.
System.Data.Odbc closes an `OdbcConnection` after a failed call only when `SQL_ATTR_CONNECTION_DEAD` returns `SQL_CD_TRUE`. Otherwise it throws the driver's error and leaves `State` at `Open`, which is what #224 reports.
## Changes
- `SQL_ATTR_CONNECTION_DEAD`, in both `PGAPI_GetConnectAttr` and `PGAPI_GetConnectOption`, now also returns true when `PQstatus()` is `CONNECTION_BAD`. Before this, the answer came from `conn->status` alone. A connection libpq had already given up on was reported alive until some code path called `CC_on_abort()`.
- When `PQsendQuery()` fails in `CC_send_query_append()` and `PQstatus()` is `CONNECTION_BAD`, the driver now calls `CC_on_abort(conn, CONN_DEAD)`. Before this, it set the error and kept the connection marked up, so every later query failed while the application kept the connection open.
## What this does not establish
This PR doesn't confirm the root cause of scenario 2 in #224. Both messages in that report, "The connection is down" and "SQLExecDirectW unable due to the connection lost", appear only when `conn->pqconn` is NULL. After connect, the only code that clears `pqconn` is `CC_on_abort(CONN_DEAD)`, which sets `CONN_DOWN` first, so `SQL_ATTR_CONNECTION_DEAD` should already have answered true. Telling which case the reporter hit needs a MyLog from a failing run, which records the status behind each `SQL_ATTR_CONNECTION_DEAD` answer.
## Test
`connection-dead` terminates the test connection's backend from a second connection. It then checks `SQL_ATTR_CONNECTION_DEAD` before termination and after each of two failing queries. It passes with and without the driver change, because a terminated backend is detected through `PQgetResult()`, a path that already called `CC_on_abort()`. So it guards the dead-connection answer against regressions rather than reproducing the change. The `PQsendQuery()` failure path depends on socket timing and isn't exercised.
The full suite (68 tests) passes against PostgreSQL 18 on macOS.
inline patch 1: connection.c, connection.h, options.c and 4 more (+147/-3 lines) download inline-1.patchdiff --git a/connection.c b/connection.cindex 61ca82cb..0b14e643 100644--- a/connection.c+++ b/connection.c@@ -2033,6 +2033,8 @@ CC_send_query_append(ConnectionClass *self, const char *query, QueryInfo *qi, UD
char *errmsg = PQerrorMessage(self->pqconn);
QLOG(0, "\nCommunication Error: %s\n", SAFE_STR(errmsg));
CC_set_error(self, CONNECTION_COMMUNICATION_ERROR, errmsg, func);
+ if (PQstatus(self->pqconn) == CONNECTION_BAD)+ CC_on_abort(self, CONN_DEAD); /* give up the connection */
goto cleanup;
}
PQsetSingleRowMode(self->pqconn);
diff --git a/connection.h b/connection.hindex a74f32d4..2df05d18 100644--- a/connection.h+++ b/connection.h@@ -80,6 +80,14 @@ typedef enum
/* not connected yet || already disconnected */
#define CC_not_connected(x) (!(x) || CONN_DOWN == (x)->status || CONN_NOT_CONNECTED == (x)->status)
+/*+ * Answer for SQL_ATTR_CONNECTION_DEAD. libpq's view is consulted too, so+ * a socket that libpq has already given up on is reported dead even if no+ * code path has called CC_on_abort() yet; applications such as+ * System.Data.Odbc only reconnect when this reports SQL_CD_TRUE.+ * PQstatus(NULL) is CONNECTION_BAD.+ */+#define CC_connection_dead(x) (CC_not_connected(x) || CONNECTION_BAD == PQstatus((x)->pqconn))
/* AutoCommit functions */
#define CC_is_in_autocommit(x) (x->transact_status & CONN_IN_AUTOCOMMIT)
diff --git a/options.c b/options.cindex b12b1955..b267ecbc 100644--- a/options.c+++ b/options.c@@ -552,7 +552,7 @@ PGAPI_GetConnectOption(HDBC hdbc,
case 1209:
#endif /* SQL_ATTR_CONNECTION_DEAD */
MYLOG(0, "CONNECTION_DEAD status=%d", conn->status);
- *((SQLUINTEGER *) pvParam) = CC_not_connected(conn);+ *((SQLUINTEGER *) pvParam) = CC_connection_dead(conn);
MYPRINTF(0, " val=" FORMAT_UINTEGER "\n", *((SQLUINTEGER *) pvParam));
break;
diff --git a/pgapi30.c b/pgapi30.cindex a17e5d9d..65ee5bd7 100644--- a/pgapi30.c+++ b/pgapi30.c@@ -418,7 +418,7 @@ PGAPI_GetConnectAttr(HDBC ConnectionHandle,
*((SQLINTEGER *) Value) = SQL_FALSE;
break;
case SQL_ATTR_CONNECTION_DEAD:
- *((SQLUINTEGER *) Value) = CC_not_connected(conn);+ *((SQLUINTEGER *) Value) = CC_connection_dead(conn);
break;
case SQL_ATTR_CONNECTION_TIMEOUT:
*((SQLUINTEGER *) Value) = 0;
diff --git a/test/expected/connection-dead.out b/test/expected/connection-dead.outnew file mode 100644index 00000000..bdea2fb0--- /dev/null+++ b/test/expected/connection-dead.out@@ -0,0 +1,7 @@+connected+connection dead before termination: 0+first query failed as expected+connection dead after first failure: 1+second query failed as expected+connection dead after second failure: 1+disconnectingdiff --git a/test/src/connection-dead-test.c b/test/src/connection-dead-test.cnew file mode 100644index 00000000..d5360d7a--- /dev/null+++ b/test/src/connection-dead-test.c@@ -0,0 +1,126 @@+/*+ * Test that SQL_ATTR_CONNECTION_DEAD reports SQL_CD_TRUE once the server+ * has dropped the connection, and keeps doing so on later calls.+ * System.Data.Odbc only closes an OdbcConnection when this attribute+ * says the connection is dead (issue #224).+ *+ * The backend of the test connection is terminated from a second+ * connection, so the driver only learns about it on the next query.+ */+#include <stdio.h>+#include <stdlib.h>++#include "common.h"++static SQLUINTEGER+get_connection_dead(void)+{+ SQLRETURN rc;+ SQLUINTEGER dead = 99;++ rc = SQLGetConnectAttr(conn, SQL_ATTR_CONNECTION_DEAD, &dead, 0, NULL);+ CHECK_CONN_RESULT(rc, "SQLGetConnectAttr(SQL_ATTR_CONNECTION_DEAD) failed", conn);+ return dead;+}++static void+run_query_expect_failure(const char *label)+{+ SQLRETURN rc;+ HSTMT hstmt = SQL_NULL_HSTMT;++ rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);+ CHECK_CONN_RESULT(rc, "failed to allocate stmt handle", conn);+ rc = SQLExecDirect(hstmt, (SQLCHAR *) "SELECT 1", SQL_NTS);+ if (SQL_SUCCEEDED(rc))+ {+ printf("FAIL: %s query succeeded on a terminated connection\n", label);+ exit(1);+ }+ printf("%s query failed as expected\n", label);+ SQLFreeHandle(SQL_HANDLE_STMT, hstmt);+}++int main(int argc, char **argv)+{+ SQLRETURN rc;+ HSTMT hstmt = SQL_NULL_HSTMT;+ SQLHENV env2;+ SQLHDBC conn2;+ HSTMT hstmt2 = SQL_NULL_HSTMT;+ SQLINTEGER pid;+ SQLINTEGER remaining;+ char dsn[1024];+ char *envvar;+ char sql[128];+ int i;++ test_connect();++ rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);+ CHECK_CONN_RESULT(rc, "failed to allocate stmt handle", conn);+ rc = SQLExecDirect(hstmt, (SQLCHAR *) "SELECT pg_backend_pid()", SQL_NTS);+ CHECK_STMT_RESULT(rc, "SELECT pg_backend_pid() failed", hstmt);+ rc = SQLFetch(hstmt);+ CHECK_STMT_RESULT(rc, "SQLFetch failed", hstmt);+ rc = SQLGetData(hstmt, 1, SQL_C_SLONG, &pid, 0, NULL);+ CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);+ SQLFreeHandle(SQL_HANDLE_STMT, hstmt);++ printf("connection dead before termination: %u\n", (unsigned int) get_connection_dead());++ /* Terminate the first connection's backend from a second connection */+ envvar = getenv("COMMON_CONNECTION_STRING_FOR_REGRESSION_TEST");+ snprintf(dsn, sizeof(dsn), "DSN=%s;%s", get_test_dsn(), envvar ? envvar : "");+ SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env2);+ SQLSetEnvAttr(env2, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);+ SQLAllocHandle(SQL_HANDLE_DBC, env2, &conn2);+ rc = SQLDriverConnect(conn2, NULL, (SQLCHAR *) dsn, SQL_NTS,+ NULL, 0, NULL, SQL_DRIVER_NOPROMPT);+ CHECK_CONN_RESULT(rc, "second SQLDriverConnect failed", conn2);+ rc = SQLAllocHandle(SQL_HANDLE_STMT, conn2, &hstmt2);+ CHECK_CONN_RESULT(rc, "failed to allocate stmt handle", conn2);++ snprintf(sql, sizeof(sql), "SELECT pg_terminate_backend(%d)", (int) pid);+ rc = SQLExecDirect(hstmt2, (SQLCHAR *) sql, SQL_NTS);+ CHECK_STMT_RESULT(rc, "pg_terminate_backend failed", hstmt2);+ SQLFreeStmt(hstmt2, SQL_CLOSE);++ /* pg_terminate_backend() only signals; wait until the backend is gone */+ snprintf(sql, sizeof(sql), "SELECT count(*) FROM pg_stat_activity WHERE pid = %d", (int) pid);+ for (i = 0; i < 100; i++)+ {+ rc = SQLExecDirect(hstmt2, (SQLCHAR *) sql, SQL_NTS);+ CHECK_STMT_RESULT(rc, "pg_stat_activity query failed", hstmt2);+ rc = SQLFetch(hstmt2);+ CHECK_STMT_RESULT(rc, "SQLFetch failed", hstmt2);+ rc = SQLGetData(hstmt2, 1, SQL_C_SLONG, &remaining, 0, NULL);+ CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt2);+ SQLFreeStmt(hstmt2, SQL_CLOSE);+ if (0 == remaining)+ break;+ rc = SQLExecDirect(hstmt2, (SQLCHAR *) "SELECT pg_sleep(0.1)", SQL_NTS);+ CHECK_STMT_RESULT(rc, "pg_sleep failed", hstmt2);+ SQLFreeStmt(hstmt2, SQL_CLOSE);+ }+ if (0 != remaining)+ {+ printf("FAIL: backend %d was not terminated\n", (int) pid);+ exit(1);+ }++ SQLFreeHandle(SQL_HANDLE_STMT, hstmt2);+ SQLDisconnect(conn2);+ SQLFreeHandle(SQL_HANDLE_DBC, conn2);+ SQLFreeHandle(SQL_HANDLE_ENV, env2);++ run_query_expect_failure("first");+ printf("connection dead after first failure: %u\n", (unsigned int) get_connection_dead());++ run_query_expect_failure("second");+ printf("connection dead after second failure: %u\n", (unsigned int) get_connection_dead());++ test_disconnect();++ return 0;+}diff --git a/test/tests b/test/testsindex e18bc7ed..6878dc64 100644--- a/test/tests+++ b/test/tests@@ -73,4 +73,5 @@ TESTBINS = exe/connect-test \
exe/connstring-escape-test \
exe/dbms-version-test \
exe/surrogate-pair-test \
- exe/many-params-test+ exe/many-params-test \+ exe/connection-dead-test