Subject: Psql meta-command conninfo+

From: Maiquel Grassi <[email-masked]>
Date: Tue, 6 Feb 2024 17:27:01 +0000

Hi,

I'm seeking to improve the \conninfo meta-command in psql. Currently, it provides limited information about the current connection. I believe that expanding it using the concept of "plus" [+] could ease the work of DBAs, SysAdmins, DevOps, etc., who manage a large volume of databases and/or multiple PostgreSQL servers. The objective of this enhancement is to obtain quick information about the current connection (session). I believe that for a PostgreSQL administrator, it is not feasible to write a plpgsql function and apply it to all databases (for example, imagine managing over 200 databases). I have an example on GitHub https://github.com/maiquelgrassi/DBA-toolkit/blob/main/cluster/dba_whoami_function.sql of a plpgsql function demonstrating exactly what I believe is impractical for the daily routine of a PostgreSQL professional. I see psql's meta-commands as significant allies in daily work in productive environments.

Note: As this is a prototype, I will adjust the rest (documentation, tests, etc.) once an agreement is reached.

Use cases for both the current and improved command bellow.

Connection 1 ("remote server"):

[postgres@localhost bin]$ ./psql -h 192.168.0.5 -p 5433 -U postgres -d postgres

psql (17devel, server 16.1)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "192.168.0.5" at port "5433".
postgres=# \conninfo+
 Current Connection Information
   Attribute    |     Value
----------------+----------------
 Database       | postgres
 User           | postgres
 Server Version | 16.1
 Server Address | 192.168.0.5/32
 Server Port    | 5433
 Client Address | 192.168.0.5/32
 Client Port    | 52716
 Session PID    | 21624
(8 rows)

Connection 2 (socket):

[postgres@localhost bin]$ ./psql

psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".
postgres=# \conninfo+
      Current Connection Information
    Attribute     |         Value
------------------+-----------------------
 Info             | Connected via socket!
 Database         | postgres
 User             | postgres
 Socket Directory | /tmp
 Server Version   | 17devel
 Server Port      | 5432
 Session PID      | 27586
(7 rows)

Connection 3 (localhost):
[postgres@localhost bin]$ ./psql -h localhost
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" (address "::1") at port "5432".
postgres=# \conninfo+
Current Connection Information
   Attribute    |   Value
----------------+-----------
 Database       | postgres
 User           | postgres
 Host           | localhost
 Server Version | 17devel
 Server Address | ::1/128
 Server Port    | 5432
 Client Address | ::1/128
 Client Port    | 46824
 Session PID    | 27598
(9 rows)

Connection 4 (127.0.0.1):
[postgres@localhost bin]$ ./psql -h 127.0.0.1
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "127.0.0.1" at port "5432".
postgres=# \conninfo+
Current Connection Information
   Attribute    |    Value
----------------+--------------
 Database       | postgres
 User           | postgres
 Server Version | 17devel
 Server Address | 127.0.0.1/32
 Server Port    | 5432
 Client Address | 127.0.0.1/32
 Client Port    | 34876
 Session PID    | 27624
(8 rows)

Regards,
Maiquel O. Grassi.

---

From: Nathan Bossart <[email-masked]>
Date: Tue, 6 Feb 2024 12:19:19 -0600

On Tue, Feb 06, 2024 at 05:27:01PM +0000, Maiquel Grassi wrote:
> I'm seeking to improve the \conninfo meta-command in psql. Currently, it
> provides limited information about the current connection. I believe that
> expanding it using the concept of "plus" [+] could ease the work of DBAs,
> SysAdmins, DevOps, etc., who manage a large volume of databases and/or
> multiple PostgreSQL servers. The objective of this enhancement is to
> obtain quick information about the current connection (session). I
> believe that for a PostgreSQL administrator, it is not feasible to write
> a plpgsql function and apply it to all databases (for example, imagine
> managing over 200 databases). I have an example on GitHub
> https://github.com/maiquelgrassi/DBA-toolkit/blob/main/cluster/dba_whoami_function.sql
> of a plpgsql function demonstrating exactly what I believe is impractical
> for the daily routine of a PostgreSQL professional. I see psql's
> meta-commands as significant allies in daily work in productive
> environments.

This seems like a reasonable idea to me.

>  Current Connection Information

My first reaction is that this should instead return a single row with the
same set of columns for any connection type (the not-applicable ones would
just be set to NULL).  That would match the other meta-commands like \l and
\du, and you could still get an expanded display with \x if needed.  Also,
I think it would simplify the code a bit.

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

---

From: Erik Wienhold <[email-masked]>
Date: Tue, 6 Feb 2024 20:10:54 +0100

> I think it would simplify the code a bit.

+1 for a single-row result and triggering expanded display with \x for
consistency with other commands.

-- 
Erik

---

From: Maiquel Grassi <[email-masked]>
Date: Tue, 6 Feb 2024 20:52:09 +0000

--//--

I made the adjustment in the code and updated the patch. I believe this is the format suggested by you all. Would this be it?

[postgres@localhost bin]$ ./psql -h 192.168.0.220 -p 5433 -U postgres -d postgres
psql (17devel, server 16.1)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "192.168.0.220" at port "5433".
postgres=# \conninfo+
  Current Connection Information
   Attribute    |      Value
----------------+------------------
 Database       | postgres
 User           | postgres
 Server Version | 16.1
 Server Address | 192.168.0.220/32
 Server Port    | 5433
 Client Address | 192.168.0.220/32
 Client Port    | 56606
 Session PID    | 2424
(8 rows)

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".
postgres=# \conninfo+
Current Connection Information
   Attribute    |  Value
----------------+----------
 Database       | postgres
 User           | postgres
 Server Version | 17devel
 Server Address |
 Server Port    | 5432
 Client Address |
 Client Port    |
 Session PID    | 30216
(8 rows)

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" (address "::1") at port "5432".
postgres=# \conninfo+
Current Connection Information
   Attribute    |  Value
----------------+----------
 Database       | postgres
 User           | postgres
 Server Version | 17devel
 Server Address | ::1/128
 Server Port    | 5432
 Client Address | ::1/128
 Client Port    | 46872
 Session PID    | 30220
(8 rows)

postgres=# \conninfo
 Client Port    | 34924
 Session PID    | 30223
(8 rows)

Regards,
Maiquel O. Grassi.