I have a remote Postgres database and am using a Xojo desktop app to log in.
I was getting an error when users opened the Xojo app and left it open, so now I call myDbase.Connect when I need to perform a database operation and myDbase.Close when each operation has completed.
This actually works quite well, but brings up another question.
I want to limit a customer to a specific number of simultaneous users. In Postgres, I can set the maximum number of connections for a user - however, if I am constantly closing the database after each operation, am I actually giving my customers many more “simultaneous” users?
For example, let’s say I set a specific Postgres user (customer) to have the maximum number of connections as one - and this customer decides to run the Xojo app on 10 workstations in his office. Am I actually giving this user the ability to run the Xojo app in all 10 workstations, just as long as database operations are not taking place simultaneously?
Since each database operation takes a few seconds at most, once records are loaded (and the db is subsequently closed), does this now give another user of the app on another workstation the opportunity to use the database, thus defeating my attempts to limit database use?
And, let’s say a user actually does try to log in and reaches the maximum number of users, if he tries the operation again in a second or two, will that operation then be successful even other workstations have the Xojo app open but the activity on the workstation is currently idle?
If I am correct in my assumption, what is the best way to limit database use?
I would write a license server, something running on the same server as the postgresql server. Your client app would contact it, get authorization and go. At exit, your clients should tell the license server that that license were dismissed, and it could be returned to the pool and be reused by another user. Your client should “ping” the license server every minute to inform it it’s idle state, or the license should be auto-dismissed, freed to return to the pool due to timeout. (e.g. User closed the laptop and got away without proper disconnection.) It’s a licensing/session controller.
As Rick says - a license server would be the way to go.
Controlling a licensing model the way you’ve described isn’t really going to cut it. I only open database connections as I need them, make my query/queries then close the connection straight away - I don’t like to have sessions hanging around.
What would be the best way to periodically remove timed-out licenses (not logged out properly)? Is this a script of some sort that runs every minute or so on the server?
Your server will be just managing/serving your licenses. Who polls the server, as I already stated, is the client, your app. A timer ticking each 60 seconds should be enough, if your client stops sending an “I still here” to the server after, let’s say, 5 minutes, you cancel his session on the server, your server should check each minute if some session is unresponsive for 5 minutes (for example) and remove his session from the pool. If your client can’t contact your license server in 5 minutes, you deny some functionality or say something and close your app. Things like this.