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 "Access privileges",
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