Postgres timeout on Xojo but not pgAdmin

Databases
1

When Xojo (desktop app version 2013 r3.3) logs into my Postgres (9.1) database, I notice that when the database stays idle, the connection eventually times out (usually after about 5 or 10 minutes or so). Then, my next database operation fails (and I have to quit the Xojo app and start over again). I have had to close the database connection after each activity and re-open it for the next database call to avoid this.

Last night, I opened a connection using pgAdmin3 on my iMac and left it open and went to sleep. This morning, I typed a new command into the SQL Editor window and it worked instantly.

What’s going on here? How can pgAdmin keep the connection open successfully and Xojo not?

2

Have you tried this with 2017r1? I ask because I haven’t seen this issue and you might be experiencing a problem that has already been addressed. (Or perhaps one I’ve forgotten about.)

If not, perhaps pgAdmin is doing something behind the scenes to keep the connection alive. What if you implemented a Timer that periodically “pinged” the database with, say, a simple “SELECT 1”?

3

Also, I don’t know how pgAdmin works but when I use Valentina, it opens a new connection to the database every time I execute SQL through the SQL editor. Do you know for sure that pgAdmin is keeping the same connection open?

This simple test should confirm.

BEGIN;
SELECT some_column FROM table_that_does_not_exist;

If you run that twice, the first time will give the error about the table not existing but the next time should tell you that the database is in an uncertain state (or something like that), i.e., ROLLBACK hasn’t been issued yet after the error. But if it gives you the same error, it means pgAdmin opened a new connection.

4

I haven’t tried it in 2017r1 yet. I encountered this issue a couple of years ago (here) and both sides were discussed:

  1. a keep alive timer
  2. closing and reopening the connection

with the recommendation swaying to #2.

I guess I can try your suggestion today if I walk away from my computer for any period of time to see what is going on here.

Unfortunately, I have too many users on Mac OS 10.6.8 to move to Xojo 2017r1.

5

I’m not having this problem using Xojo 2015 R2.2 compiled for both Mac and Windows. Don’t know if this version will work on 10.6.8.

6

I just wrote a quick test app that opens a connection and leaves it idle. Every time I press a button it does a simple query and let’s me know if there was problem and how long since the last time I pushed the button.

2017-03-29 08:35:50: All good - 636,029 recs (0.0m)
2017-03-29 08:36:04: All good - 636,029 recs (0.2m)
2017-03-29 09:03:04: All good - 636,029 recs (27.1m)
7

Are you sure that pgAdmin is actually keeping the connection open David?

Good DB access practice is to connect, do the business and then disconnect and not keep a connection open longer than required, so there is a good chances that this is what is happening in pgAdmin in the same way Kem mentions with Valentina.

Is there a reason that you need the connection open all the time?

8

No. Not really. I was just curious what was going on here.

I am actually opening the connection, doing stuff, and closing the connection when I use Xojo.

I was just wondering what pgAdmin was doing - if it maintained the connection open or opens and closes the connection as needed.

9

So if you keep logging in and out of the database, how do you keep track of how many users are logged in at one time?

For example, I want someone to have a single user license. I can set Postgres to allow only one user at a time, but I am running into two problems.

  1. If the application logs in and out as needed, Postgres would never know how many computers the end user is using unless one user tries to do something before the other user’s task is finished.

  2. If the application crashes, it takes Postgres a while to release the logged in user (maybe 15 minutes or more), so the end user has trouble logging in again.