So far I’m able to connect to PostgreSQL as external data source and create RDF Views on top of this connection.
By the way I get sometimes an error message: “… connection is dead”. Do you have any idea on why this happens?
thanks
This is the error message:
| SQL State | S1000 |
|---|---|
| SQL Message | VD052: Remote DSN PostgreSQLU: [OpenLink][ODBC][PostgreSQL Server]connection is dead in _sql_tables:(BIF), DB.DBA.sql_tables, vc_after_data_bind_catalog_list, vc_data_bind, __udt_method_call:(BIF), vc_data_bind, __udt_method_call:(BIF), vc_data_bind, __udt_method_call:(BIF), vc_data_bind, __udt_method_call:(BIF), vc_data_bind, __udt_method_call:(BIF) |
The connection is dead error message generally occurs when the backend PostgreSQL database is not responding to requests even after an attempt to reconnect. Please create and provide an ODBC trace of such an occurrence of this error so we can see at which point it is occurring. The PostgreSQL logs should also be checked to see if there might any messages there indicating the server might have had problems or on the network itself.
Thanks for the answer. Will check with our DB esperts
How do I generate this ODBC Trace from Virtuoso?
I did do trace_on(‘dsn’,‘error’)
ODBC tracing is a generic process applicable all to ODBC Drivers generally, with details on how to create ODBC traces available.
For a Virtuoso external linked table data source issue the trace_on() may also be useful for a given connection attempt to see what Virtuoso is doing at the time. Although trace_on() with all options enable is probably more useful initially to see all activity. You can dynamically turn off with the trace_off() call.
Hi,
We did some extensive trial and error for this problem.
The source of the problem is the killing of connections at the Postgress end during DB maintenance workflows. (Some operations require to have no connections to a database, so all connections are killed)
Once the connection is killed the ODBC driver is no longer able to recover from the ‘Dead Connection’ a restart of Virtuoso fixes the problem, but feels less then elegant.
I have created a trace and debug log of the test scenario which I could share in private.
Bart
Note in the VDB section of the Virtuoso documentation there is the VDBDisconnectTimeout parameter, that controls the time in secs after which an idle VDB connection is considered timed out and closed. The default is 1000 secs, but you could reduce this value to force connections to be closed sooner based on the frequency of these Postgres dead connections and reconnecting for new requests, to eliminate the need to restart Virtuoso.
There is also a ReconnectOnFailure which is set to “1” will automatically reconnecting without reporting the error and could mitigate against the condition also.
Hi Hugh,
We’ve tried the ReconnectOnFailure = 1 setting but that does not change the behaviour.
We still get the Connection is Dead error from Postgress where the only help is restarting Virtuoso
The VDBDisconnectTimeout on a low value does help, but probably is impacting performance and killing a running query results in a cached dead connection.
Any hints appreciated
Bart
When you made those Virtuoso configuration file changes did you restart Virtuoso, which is required for the changes to take effect ?
Yes we did the restart of the server
Then you are back to trying to create ODBC traces and Virtuoso additional traces logs as detailed in on of my previous posts, so we can see if any additional information can be obtained in the virtuoso logs and also if any PostgreSQL ODBC drivers errors might be occurring leading up to these problems: