Re: Available disk space per tablespace

I'm picking this up again. Attached is version 5 of the
pg_tablespace_avail() patch.

Difference to v4 is that the \db+ query used in psql is now checking
tablespace permissions before blindly calling the function. This
avoids raising errors when some tablespace is not accessible.

postgres =# \db+
/**** INTERNAL QUERY ****/
/* Get matching tablespaces */
SELECT spcname AS "Name",
  pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
  pg_catalog.pg_tablespace_location(tblspc.oid) AS "Location",
  CASE WHEN pg_catalog.array_length(spcacl, 1) = 0 THEN '(none)' ELSE pg_catalog.array_to_string(spcacl, E'\n') END AS
"Accessprivileges",
 
  spcoptions AS "Options",
  CASE WHEN dbsub.dattablespace OPERATOR(pg_catalog.=) tblspc.oid OR
               pg_catalog.has_tablespace_privilege(tblspc.oid, 'CREATE') OR
               pg_catalog.pg_has_role('pg_read_all_stats', 'USAGE')
       THEN pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(tblspc.oid))
       ELSE 'No Access'  END as "Size",
  CASE WHEN dbsub.dattablespace OPERATOR(pg_catalog.=) tblspc.oid OR
               pg_catalog.has_tablespace_privilege(tblspc.oid, 'CREATE') OR
               pg_catalog.pg_has_role('pg_read_all_stats', 'USAGE')
       THEN pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_avail(tblspc.oid))
       ELSE 'No Access'  END as "Free",
  pg_catalog.shobj_description(tblspc.oid, 'pg_tablespace') AS "Description"
FROM pg_catalog.pg_tablespace tblspc
CROSS JOIN (SELECT dattablespace FROM pg_catalog.pg_database db
  wHERE db.datname OPERATOR(pg_catalog.=) pg_catalog.current_database()) dbsub
ORDER BY 1;
/************************/

The logic is the same as in pg_tablespace_size (which wasn't guarded in psql before):
* this database's default tablespace is ok
* having CREATE is ok
* rold pg_read_all_stats is ok

                                     List of tablespaces
    Name    │ Owner │ Location │ Access privileges │ Options │  Size  │  Free  │ Description 
────────────┼───────┼──────────┼───────────────────┼─────────┼────────┼────────┼─────────────
 pg_default │ myon  │          │ ∅                 │ ∅       │ 24 MB  │ 365 GB │ ∅
 pg_global  │ myon  │          │ ∅                 │ ∅       │ 549 kB │ 365 GB │ ∅
(2 rows)

I think this patch is useful as-is and could be committed.


As a followup, I would like to include pg_wal in this list since it
can be moved to a separate disk. There are several ways forward:

1) include a pg_wal entry in pg_tablespace. Together with a trivial
   addition to get_tablespace_location:

+   if (tablespaceOid == WALTABLESPACE_OID)
+       snprintf(sourcepath, sizeof(sourcepath), "%s", XLOGDIR);

  this makes the \db+ query report size/free out of the box. This
  seemed very clean to me until I discovered the downside that it
  required not-so-trivial guarding against WALTABLESPACE_OID being
  used as tablespace in SQL commands in many code places.

2) add new pg_wal_size() and pg_wal_avail() functions

3) reserve a special value that makes a combination of
   get_tablespace_location, pg_tablespace_size and pg_tablespace_avail
   work on pg_wal even when that's not registered in pg_tablespace.

Not sure what way is best, perhaps something between 2 and 3?

Christoph