[GENERAL] pg_stat_activity extra Fields are NULL - Mailing list pgsql-general

Dear Members!

Windows Clients, PG 9.4 on Linux.

I used [PID + Client IP + Port + BackEnd Start] for unique identification of a connection.

Every connection store it's unique connection info in a table (per DataBase).

F.e:

"8574|195.12.4.3|50120|2017-01-01 08:12:15"

Yesterday I realized that BackEnd Start + Client IP + Port would be NULL in a query.

I checked it and it seems to be access limitation.

Extra fields are on or off based on the account and rights.

As I see the logic:

- postgres (admin) can see everything (db1, db2, db3)

- db owner can see the connections on his db (user1 can see db1, but not db2)

- other users can see only their connections (user_x can see his row only)

Rows are not limited, only fields are set to NULL for "non-accessable" connections.

If I use the db owners only for the connections, the technic will works.

But if it is a "member of" kind user, it would break my logic to uniqueness.

The actual connection can join his unique string, because everybody can see himself.

But in this wrong case the another connection can see only:

"8574|||"

So we know only that the PID is living, but we don't know it's same PID, or it closed, and reused by a new connection.

Is there a way to we can read these extra informations by extra call?

Or is there any way to we can determine it's same PID or a newer (from a different connection)?

The always visible fields are "datname, usename, pid, app_name", the extras (creation time) would be NULL for non-accessable objects.

The appname is seems to be limited (I see it's text, but when I tried to set it longer with an extra GUID, it truncated).

Thank you for all advice!

Best wishes

  dd