0

I have a frontend written in MS Access that connects to a Postgres backend through ODBC. Sometimes the number of sessions reaches 100 and at that point I get an "ODBC call failed" error. I use PG Admin version 6.13 to administer the database. I noticed under Database activity -> sessions the following after some heavy usage on the database:

enter image description here

There are over 70 of these in the session tab, which causes the application to crash. If I close the application, the entries also disappear. Running the SQL returns nothing, as there is no data type with the name "lo" in the table pg_type. The table pg_type is a system table, not one that I created. Any ideas how to fix this excessive number of session entries? And what is this "lo" type?

Improve this question
4
  • Those sessions are initiated by Access, the ODBC driver or your application's code - most likely your application does something which causes the driver to initiate new connections. "lo" is for large object. I wouldn't be surprised if Access uses "large objects" for "BLOB" columns instead of bytea because it isn't really compatible with Postgres Commented Oct 4, 2022 at 9:20
  • 1
    The Access application has a connection leak. This has to be fixed in the application code. Commented Oct 4, 2022 at 16:56
  • I make sure to close all my recordsets after opening them, to avoid leak problems. rs.close and Set rs = Nothing When I don't need a recordset anymore Commented Oct 5, 2022 at 9:39
  • 1
    If you really did close all connections properly, the sessions wouldn't pile up on the server. Commented Oct 5, 2022 at 11:33

1 Answer 1

Reset to default
0

That is showing the last query the session ran before it went idle. That is an introspection query, where the app is trying to figure out what are the capabilities of the database it is connected to. Access probably does that automatically whenever a new session to PostgreSQL is opened. So your app is opening sessions, automatically doing its introspection queries, then never doing anything else with the connection.

"lo" is a type created when the "lo" extension is installed (which apparently you have not done--but there is no reason to think you need to do so, either). The exact query probably has no significance, it just happens to be the last query on the list of introspection queries which get run.

Improve this answer
1
  • The only extensions my database has listed are plpgsql and unaccent. I went to the ODBC administration and unchecked "bytea as LO" which was checked. I haven't seen that session ever since. I will monitor the number of sessions in case something happens again Commented Oct 5, 2022 at 9:52

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.