Postgresql - how to know if there is an existing connection?

Databases
1

Hello all,

Is there a way to check the Database object to see if there is already a connection from a particular instance of an object?

Thanks,
Tim

2

Hello Tim,
I’m not sure if this is what you mean, but with this query you get all connections of a specific database and user:

("SELECT * FROM pg_stat_activity where datname = '" + NameofDB + "' AND usename = '" + UserName + "' ORDER BY query_start;")
1 Like
3

You can also set the application name for the postgresql connection so you can see what connected
Execute the following sql instruction from your code on your postgresql server
SET application_name=‘myobjectname (Instancenr)’
Then in for instance pgadmin you will see the name next to the connection

4

Thanks guys.

There are some more caveats to this.

  1. There are multiple apps that all use the same login/password combination.
  2. Each of these does include the app name
  3. With the particular app I am working with, there are multiple objects each that when instantiated, creates a new, distinct connection. So in this instance there could be several db connections.

I suppose I could give each of these objects a different AppName/ID to differentiate it from any other?

Also, from your responses, I guess that there is no "IsConnected type of property of the Db. Is that correct? I could not find one, but that doesn’t always mean that I got it correct!

Thanks,
TIm

**Edit - What would be the ‘pg_stat_activity’ call to get the AppName?

5

Hi All,

If I used something like this:

SELECT * FROM pg_stat_activity where datname = ‘axcys’ AND usename = ‘axcys’ AND application_name = ‘AxcysNotifier’ + * ORDER BY query_start;

How can I add a wildcard to the application_name so that all instances that start with “AxcysNotifier” would be included?

Thanks,
Tim

6

If I remember correctly:

In PostgreSQL, you can use the LIKE operator with a wildcard (%) to filter rows where a column’s value matches a pattern. To include all instances of application_name that start with “AxcysNotifier,” you can modify your query as follows:

SELECT *
FROM pg_stat_activity
WHERE datname = ‘axcys’
AND usename = ‘axcys’
AND application_name LIKE ‘AxcysNotifier%’
ORDER BY query_start;

7

And if you want it Case Insensitive then use ILIKE instead of LIKE

2 Likes
8

Thank you guys!
Tim