Thread

add age for databases list in psql

Ioseph Kim <pgsql-kr@postgresql.kr> — 2019-12-03T02:51:55Z

Hi.

I need to check database's age in psql, so I want to append that.


like this,


(12) [postgres@ioseph-centos7 ~]$ psql -E
psql (12.0)
Type "help" for help.

(12.0) postgres@postgres=# \l
********* QUERY **********
SELECT d.datname as "Name",
        pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
        pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
        d.datcollate as "Collate",
        d.datctype as "Ctype",
        pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************

                                 List of databases
    Name    |  Owner   | Encoding | Collate |    Ctype |   Access 
privileges
-----------+----------+----------+---------+-------------+-----------------------
  postgres  | postgres | UTF8     | C       | ko_KR.UTF-8 |
  template0 | postgres | UTF8     | C       | ko_KR.UTF-8 | 
=c/postgres          +
            |          |          |         | | postgres=CTc/postgres
  template1 | postgres | UTF8     | C       | ko_KR.UTF-8 | 
=c/postgres          +
            |          |          |         | | postgres=CTc/postgres
(3 rows)

(12.0) postgres@postgres=# \l+
********* QUERY **********
SELECT d.datname as "Name",
        pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
        pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
        d.datcollate as "Collate",
        d.datctype as "Ctype",
        pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges",
        CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
             THEN 
pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
             ELSE 'No Access'
        END as "Size",
        age(d.datfrozenxid) as "Age",
        t.spcname as "Tablespace",
        pg_catalog.shobj_description(d.oid, 'pg_database') as "Description"
FROM pg_catalog.pg_database d
   JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
ORDER BY 1;
**************************

List of databases
    Name    |  Owner   | Encoding | Collate |    Ctype |   Access 
privileges   |  Size   | Age | Tablespace |                Description
-----------+----------+----------+---------+-------------+-----------------------+---------+-----+------------+--------------------------------------------
  postgres  | postgres | UTF8     | C       | ko_KR.UTF-8 
|                       | 8201 kB |  26 | pg_default | default 
administrative connection database
  template0 | postgres | UTF8     | C       | ko_KR.UTF-8 | 
=c/postgres          +| 8049 kB |  26 | pg_default | unmodifiable empty 
database
            |          |          |         | | postgres=CTc/postgres 
|         |     |            |
  template1 | postgres | UTF8     | C       | ko_KR.UTF-8 | 
=c/postgres          +| 8049 kB |  26 | pg_default | default template 
for new databases
            |          |          |         | | postgres=CTc/postgres 
|         |     |            |
(3 rows)

--- patch content

--- bin/psql/describe.c.orig    2019-12-03 11:42:22.628743691 +0900
+++ bin/psql/describe.c    2019-12-03 11:36:21.652722682 +0900
@@ -890,6 +890,10 @@
                            gettext_noop("Size"));
      if (verbose && pset.sversion >= 80000)
          appendPQExpBuffer(&buf,
+                          ",\n       age(d.datfrozenxid) as \"%s\"",
+                          gettext_noop("Age"));
+    if (verbose && pset.sversion >= 80000)
+        appendPQExpBuffer(&buf,
                            ",\n       t.spcname as \"%s\"",
                            gettext_noop("Tablespace"));
      if (verbose && pset.sversion >= 80200)


-----

Please review it.


Regards ioseph