pg.ddx.io GitHub: postgresql-interfaces/psqlodbc (issues + PRs as email threads)help / color / mirror / Atom feed
[postgresql-interfaces/psqlodbc] issue #224: psqlODBC driver or MS System.Data.Odbc do not report connection lost 5+ messages / 2 participants [nested] [flat]
* [postgresql-interfaces/psqlodbc] issue #224: psqlODBC driver or MS System.Data.Odbc do not report connection lost @ 2026-10-26 07:06 "sergey-krasnogorov (@sergey-krasnogorov)" <noreply+sergey-krasnogorov@github.com> 0 siblings, 0 replies; 5+ messages in thread From: sergey-krasnogorov (@sergey-krasnogorov) @ 2026-10-26 07:06 UTC (permalink / raw) To: postgresql-interfaces/psqlodbc <noreply+postgresql-interfaces-psqlodbc@github.com> Dear psqlODBC maintainers, I would like to ask a couple of questions about MS ODBC API / psqlodbc driver interaction and whether it is expected behavior. Summary We are observing intermittent lost connection issues in a .NET application using: PostgreSQL: 18.6 running as Windows Service psqlODBC: 18.00.0005 Windows: x64 Driver: PostgreSQL Unicode (x64) The failure occurs after replacing entire database cluster directory on filesystem with another one (created earlier, same database, same settings, kind of “restoring database to the previously saved state”). Scenario #1: 1. Stop database service 2. Replace database cluster directory with another one 3. Start database service 4. Immediately open OdbcConnection 5. OdbcConnection.State is Open 6. Immediately execute System.Data.Odbc.OdbcCommand 7. Exception: The connection has been disabled. 8. OdbcConnection.State is Closed That is somehow Ok, connection state is determined, reconnecting helped. Scenario #2: 1. Stop database service 2. Replace database cluster directory with another one 3. Start database service 4. Immediately open OdbcConnection 5. OdbcConnection.State is Open 6. Immediately execute System.Data.Odbc.OdbcCommand 7a. Exception: ERROR [08S01] The connection is down 7b. Exception: ERROR [08S01] SQLExecDirectW unable due to the connection lost 9. OdbcConnection.State is Open Reported connection state is wrong, no reconnecting performed, all subsequent queries result in “ERROR [08S01] SQLExecDirectW unable due to the connection lost”. Questions: 1. Might it be some timing issue after replacing database cluster? E.g. if we delay connection/query for some measurable time (2-10 sec) we never observed such type of unexpected disconnecting. 2. Why OdbcConnection.State remains Open after numerous query failures? Shouldn’t the driver detect it after the first unsuccessful query due to connection lost? Thank you for your time and for maintaining psqlODBC. Best regards, Sergey Krasnogorov ^ permalink raw [nested|flat] 5+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #224: psqlODBC driver or MS System.Data.Odbc do not report connection lost @ 2026-10-27 14:56 "davecramer (@davecramer)" <noreply+davecramer@github.com> 3 siblings, 0 replies; 5+ messages in thread From: davecramer (@davecramer) @ 2026-10-27 14:56 UTC (permalink / raw) To: postgresql-interfaces/psqlodbc <noreply+postgresql-interfaces-psqlodbc@github.com> Timing: probably yes. The connection is accepted and then dropped by the server, which points at server-side activity right after startup. The PostgreSQL server log at that moment is the evidence to look for: FATAL / terminating connection lines, or a postmaster restart. Why State stays Open: according to the code it shouldn't. We need logs to see what SQL_ATTR_CONNECTION_DEAD actually returned. Can you get me logs: psqlODBC MyLog and CommLog for a scenario 2 run. The CONNECTION_DEAD status=%d log line shows exactly what the driver told .NET. Possibly the server log for the same moment. Can you tell me: whether Windows ODBC connection pooling is enabled for the driver, their .NET version, and the connection string (especially ConnSettings). Whether the app shares one OdbcConnection across threads. ^ permalink raw [nested|flat] 5+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #224: psqlODBC driver or MS System.Data.Odbc do not report connection lost @ 2026-10-29 14:28 "sergey-krasnogorov (@sergey-krasnogorov)" <noreply+sergey-krasnogorov@github.com> 3 siblings, 0 replies; 5+ messages in thread From: sergey-krasnogorov (@sergey-krasnogorov) @ 2026-10-29 14:28 UTC (permalink / raw) To: postgresql-interfaces/psqlodbc <noreply+postgresql-interfaces-psqlodbc@github.com> Thank you for reply Dave, Answering your questions: Connection pooling is enabled for the driver, set to 60s. Client app is built against .NET 4.7.2. Connection string is widely shared in attached logs I think. Yes, app does share one OdbcConnection across threads but uses custom in-house implemented concurrency handling mechanism. Moreover, 'connection lost' errors occurred right after service startup/is_ready/connect, on first/second/third SELECT depending on how happy we are. Attached are logs we were able to collect for Scenario #2 (connection lost), but they don't tell us much. What else should be specifically configured for logging to get information we want to see there? I "updated" postgresql-2026-10-28_170320.log by inserting part of app log there. Seems like the server was unresponsive at that time (?) [#224_logs_01.zip](https://github.com/user-attachments/files/34137155/224_logs_01.zip) Best regards, Sergey Krasnogorov ^ permalink raw [nested|flat] 5+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #224: psqlODBC driver or MS System.Data.Odbc do not report connection lost @ 2026-11-05 16:55 "davecramer (@davecramer)" <noreply+davecramer@github.com> 3 siblings, 0 replies; 5+ messages in thread From: davecramer (@davecramer) @ 2026-11-05 16:55 UTC (permalink / raw) To: postgresql-interfaces/psqlodbc <noreply+postgresql-interfaces-psqlodbc@github.com> Hi Sergey, Thanks for the logs and the answers. Unfortunately the logs don't cover the failure: - `mylog_EchoLoader_10820.log` ends after 41 lines, while the driver is still reading the connection string. The file was last written at 16:57, and the failure in your server log is at 17:03:30, so it's from an earlier run. - `mylog_odbcad32_28136.log` and `psqlodbc_odbcad32_28136.log` come from the ODBC Data Source Administrator (`odbcad32`), not from your application. The server log is still useful. It shows no `FATAL` or `terminating connection` messages, so the server didn't drop your connection. And there's no new connection from your application at 17:03:30, when the `SELECT` failed. With connection pooling enabled, that would fit the driver manager handing your application a pooled connection that was opened before the server restarted. To see what happens, could you reproduce scenario 2 with the following enabled, and send the logs from that run? 1. **The driver's log (MyLog) for your application.** Add `Debug=1;CommLog=1` to the application's connection string, or tick MyLog and CommLog in the driver's DSN settings, and keep them on for the whole run. The driver writes `mylog_EchoLoader_<pid>.log` and `psqlodbc_EchoLoader_<pid>.log`. Please send the pair whose process ID matches the run that failed, and note the time it failed. In that log, `CONNECTION_DEAD status=…` lines show what the driver answered each time .NET asked whether the connection was dead. 2. **ODBC tracing,** from the Tracing tab of the ODBC Data Source Administrator, for the same run. It records every call between .NET, the driver manager and the driver, including the `SQLGetConnectAttr` calls for `SQL_ATTR_CONNECTION_DEAD` (attribute 1209) and what they returned. That also shows when the driver manager takes a connection out of its pool. 3. **The PostgreSQL server log** for the same period, as before. If you can, please also run the same test once with connection pooling turned off for the driver. If scenario 2 goes away, that tells us the stale connections are coming from the pool. Thanks, Dave ^ permalink raw [nested|flat] 5+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #224: psqlODBC driver or MS System.Data.Odbc do not report connection lost @ 2026-11-16 07:25 "sergey-krasnogorov (@sergey-krasnogorov)" <noreply+sergey-krasnogorov@github.com> 3 siblings, 0 replies; 5+ messages in thread From: sergey-krasnogorov (@sergey-krasnogorov) @ 2026-11-16 07:25 UTC (permalink / raw) To: postgresql-interfaces/psqlodbc <noreply+postgresql-interfaces-psqlodbc@github.com> Hi Dave, Thanks for your comments and suggestions. Please see attached logs. They cover three separate different SW runs/scenarios: 1. **error** Scenario: start db service, check `pg_isready`, in a few seconds start `SELECT`ing. Error (connection is down) caught at third attempt (third db service start). Unfortunately, based on our app internal log, it is random: first `SELECT` might be Ok, even second Ok, then it lost connection. Please note: `psqlodbc_EchoLoader_<pid>.log` even not written. I never saw it created in such a scenario. 2. **no-pooling-no-error** Scenario: exactly same as **1. error** but with driver connection pooling is off. Please note: `psqlodbc_EchoLoader_<pid>.log` is there regardless of how "fast" I started `SELECT`ing. 3. **no-rush-no-error** Scenario: start db service, check `pg_isready`, **wait** for min 30s, then start `SELECT`ing. Please note: `psqlodbc_EchoLoader_<pid>.log` of course is there. Looking forward to your opinion on what it might be. Thanks, Sergey [#224_logs_02.zip](https://github.com/user-attachments/files/34902200/224_logs_02.zip) ^ permalink raw [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-11-16 07:25 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-10-26 07:06 [postgresql-interfaces/psqlodbc] issue #224: psqlODBC driver or MS System.Data.Odbc do not report connection lost "sergey-krasnogorov (@sergey-krasnogorov)" <noreply+sergey-krasnogorov@github.com> 2026-10-27 14:56 ` "davecramer (@davecramer)" <noreply+davecramer@github.com> 2026-10-29 14:28 ` "sergey-krasnogorov (@sergey-krasnogorov)" <noreply+sergey-krasnogorov@github.com> 2026-11-05 16:55 ` "davecramer (@davecramer)" <noreply+davecramer@github.com> 2026-11-16 07:25 ` "sergey-krasnogorov (@sergey-krasnogorov)" <noreply+sergey-krasnogorov@github.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox