RE: Psql meta-command conninfo+

  On 2024-02-07 05:13 +0100, Maiquel Grassi wrote:
  >   On Tue, Feb 06, 2024 at 09:45:54PM +0000, Maiquel Grassi wrote:
  >   > My initial idea has always been that they should continue to appear
  >   > because \conninfo+ should show all the things that \conninfo shows and
  >   > add more information. I think that's the purpose of the 'plus.' Now we're
  >   > on a better path than the initial one. We can still add the socket
  >   > directory and the host.
  >
  >   Agreed.
  >
  > --//--
  >
  > I believe it's resolved reasonably well this way:
  >
  > SELECT
  >   pg_catalog.current_database() AS "Database",
  >   current_user AS "User",
  >   pg_catalog.current_setting('server_version') AS "Server Version",
  >   CASE
  >     WHEN pg_catalog.inet_server_addr() IS NULL
  >     THEN 'NULL'
  >     ELSE pg_catalog.inet_server_addr()::text
  >   END AS "Server Address",

  Should be NULL instead of string 'NULL'.  So the entire CASE expression
  is redundant and you can just return pg_catalog.inet_server_addr().

  >   pg_catalog.current_setting('port') AS "Port",
  >   CASE
  >     WHEN pg_catalog.inet_client_addr() IS NULL
  >     THEN 'NULL'
  >     ELSE pg_catalog.inet_client_addr()::text
  >   END AS "Client Address",
  >   CASE
  >     WHEN pg_catalog.inet_client_port() IS NULL
  >      THEN 'NULL'
  >     ELSE pg_catalog.inet_client_port()::text
  >   END AS "Client Port",

  Same here.

  >   pg_catalog.pg_backend_pid() AS "Session PID",
  >   CASE
  >     WHEN pg_catalog.current_setting('unix_socket_directories') = ''
  >     THEN 'NULL'
  >     ELSE pg_catalog.current_setting('unix_socket_directories')
  >   END AS "Socket Directory",

  The CASE expression can be simplified to:

          nullif(pg_catalog.current_setting('unix_socket_directories'), '')

  >   CASE
  >     WHEN
  >       pg_catalog.inet_server_addr() IS NULL
  >       AND pg_catalog.inet_client_addr() IS NULL
  >     THEN 'NULL'
  >     WHEN
  >       pg_catalog.inet_server_addr() = pg_catalog.inet_client_addr()
  >     THEN 'localhost'

  Is it safe to assume localhost here?  \conninfo prints localhost only
  when I connect with psql -hlocalhost:

          $ psql -hlocalhost postgres
          psql (16.1)
          postgres=# \conninfo
          You are connected to database "postgres" as user "ewie" on host "localhost" (address "::1") at port "5432".
          postgres=# \q

          $ psql -h127.0.0.1 postgres
          psql (16.1)
          postgres=# \conninfo
          You are connected to database "postgres" as user "ewie" on host "127.0.0.1" at port "5432".

  >     ELSE pg_catalog.inet_server_addr()::text
  >   END AS "Host";

--//--

There really was no need for the CASES. However, they helped visualize the psql output since for the null value, no word is printed on the screen. I made the adjustment by removing this redundancy.

Regarding the "Host" column, the most reliable way to solve this, I believe, is by using the "host" variable. So it's necessary to declare char *host = PQhost(pset.db); in listConnectionInformation() and use it in the SQL (see patch v5). This way, we have the same return from \conninfo reliably.

Once again, I ran a series of tests.

[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
 Database |   User   | Server Version | Server Address | Server Port | Client Address | Client Port | Session PID | Socket Directory | Host
----------+----------+----------------+----------------+-------------+----------------+-------------+-------------+------------------+------
 postgres | postgres | 17devel        |                | 5432        |                |             |       15898 | /tmp             |
(1 row)

postgres=# \q
[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
 Database |   User   | Server Version | Server Address | Server Port | Client Address | Client Port | Session PID | Socket Directory |   Host
----------+----------+----------------+----------------+-------------+----------------+-------------+-------------+------------------+-----------
 postgres | postgres | 17devel        | ::1            | 5432        | ::1            |       47012 |       15900 | /tmp             | localhost
(1 row)

postgres=# \q
[postgres@localhost bin]$ ./psql -h ::1
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "::1" at port "5432".
postgres=# \conninfo+
                                                       Current Connection Information
 Database |   User   | Server Version | Server Address | Server Port | Client Address | Client Port | Session PID | Socket Directory | Host
----------+----------+----------------+----------------+-------------+----------------+-------------+-------------+------------------+------
 postgres | postgres | 17devel        | ::1            | 5432        | ::1            |       47014 |       15905 | /tmp             | ::1
(1 row)

postgres=# \q
[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
 Database |   User   | Server Version | Server Address | Server Port | Client Address | Client Port | Session PID | Socket Directory |   Host
----------+----------+----------------+----------------+-------------+----------------+-------------+-------------+------------------+-----------
 postgres | postgres | 17devel        | 127.0.0.1      | 5432        | 127.0.0.1      |       35066 |       15908 | /tmp             | 127.0.0.1
(1 row)

postgres=# \q
[postgres@localhost bin]$ ./psql -h 192.168.0.5 -p 5432 -d postgres -U postgres
psql (17devel, server 14.3)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "192.168.0.5" at port "5432".
postgres=# \conninfo+
                                                          Current Connection Information
 Database |   User   | Server Version | Server Address | Server Port | Client Address | Client Port | Session PID | Socket Directory |    Host
----------+----------+----------------+----------------+-------------+----------------+-------------+-------------+------------------+-------------
 postgres | postgres | 14.3           | 192.168.0.5    | 5432        | 192.168.0.5    |       60904 |       29264 |                  | 192.168.0.5

Regards,
Maiquel O. Grassi.