RE: Psql meta-command conninfo+ - Mailing list pgsql-hackers
| From | Maiquel Grassi |
|---|---|
| Subject | RE: Psql meta-command conninfo+ |
| Date | |
| Msg-id | CP8P284MB249690D959F698EF3187D8AEEC452@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM Whole thread Raw |
| In response to | Re: Psql meta-command conninfo+ (Erik Wienhold <ewie@ewie.name>) |
| List | pgsql-hackers |
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.
> 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.
Regards,
Maiquel O. Grassi.
Attachment
pgsql-hackers by date: