Hi,
I'm picking up a 5 year old patch again:
https://www.postgresql.org/message-id/flat/20191108132419.GG8017%40msg.df7cb.de
Users will be interested in knowing how much extra data they can load
into a database, but PG currently does not expose that number. This
patch introduces a new function pg_tablespace_avail() that takes a
tablespace name or oid, and returns the number of bytes "available"
there. This is the number without any reserved blocks (Unix, f_avail)
or available to the current user (Windows).
(This is not meant to replace a full-fledged OS monitoring system that
has much more numbers about disks and everything, it is filling a UX
gap.)
Compared to the last patch, this just returns a single number so it's
easier to use - total space isn't all that interesting, we just return
the number the user wants.
The free space is included in \db+ output:
postgres =# \db+
List of tablespaces
Name │ Owner │ Location │ Access privileges │ Options │ Size │ Free
│ Description
────────────┼───────┼──────────┼───────────────────┼─────────┼─────────┼────────┼─────────────
pg_default │ myon │ │ ∅ │ ∅ │ 23 MB │ 538 GB
│ ∅
pg_global │ myon │ │ ∅ │ ∅ │ 556 kB │ 538 GB
│ ∅
spc │ myon │ /tmp/spc │ ∅ │ ∅ │ 0 bytes │ 31 GB
│ ∅
(3 rows)
The patch has also been tested on Windows.
TODO: Figure out which systems need statfs() vs statvfs()
Christoph
>From 455640e375e7142d4bef2e4f47f678e3712a5a27 Mon Sep 17 00:00:00 2001
From: Christoph Berg <m...@debian.org>
Date: Fri, 8 Nov 2019 14:12:35 +0100
Subject: [PATCH] Add pg_tablespace_avail() functions
This exposes the f_avail value from statvfs() on tablespace directories
on the SQL level, allowing monitoring of free disk space from within the
server. On windows, GetDiskFreeSpaceEx() is used.
Permissions required match those from pg_tablespace_size.
In psql, include a new "Free" column in \db+ output.
---
doc/src/sgml/func.sgml | 21 ++++++++
doc/src/sgml/ref/psql-ref.sgml | 2 +-
src/backend/utils/adt/dbsize.c | 94 +++++++++++++++++++++++++++++++++
src/bin/psql/describe.c | 11 ++--
src/include/catalog/pg_proc.dat | 8 +++
5 files changed, 132 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 51dd8ad6571..0b4456ad958 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -30093,6 +30093,27 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_tablespace_avail</primary>
+ </indexterm>
+ <function>pg_tablespace_avail</function> ( <type>name</type> )
+ <returnvalue>bigint</returnvalue>
+ </para>
+ <para role="func_signature">
+ <function>pg_tablespace_avail</function> ( <type>oid</type> )
+ <returnvalue>bigint</returnvalue>
+ </para>
+ <para>
+ Returns the available disk space in the tablespace with the
+ specified name or OID. To use this function, you must
+ have <literal>CREATE</literal> privilege on the specified tablespace
+ or have privileges of the <literal>pg_read_all_stats</literal> role,
+ unless it is the default tablespace for the current database.
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index cedccc14129..9e1bec0b422 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1492,7 +1492,7 @@ SELECT $1 \parse stmt1
If <literal>x</literal> is appended to the command name, the results
are displayed in expanded mode.
If <literal>+</literal> is appended to the command name, each tablespace
- is listed with its associated options, on-disk size, permissions and
+ is listed with its associated options, on-disk size and free disk space, permissions and
description.
</para>
</listitem>
diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c
index 25865b660ef..3a2f47c50ec 100644
--- a/src/backend/utils/adt/dbsize.c
+++ b/src/backend/utils/adt/dbsize.c
@@ -12,6 +12,11 @@
#include "postgres.h"
#include <sys/stat.h>
+#ifdef WIN32
+#include <fileapi.h>
+#else
+#include <sys/statvfs.h>
+#endif
#include "access/htup_details.h"
#include "access/relation.h"
@@ -316,6 +321,95 @@ pg_tablespace_size_name(PG_FUNCTION_ARGS)
}
+/*
+ * Return available disk space of tablespace. Returns -1 if the tablespace
+ * directory cannot be found.
+ */
+static int64
+calculate_tablespace_avail(Oid tblspcOid)
+{
+ char tblspcPath[MAXPGPATH];
+ AclResult aclresult;
+#ifdef WIN32
+ ULARGE_INTEGER lpFreeBytesAvailable;
+#else
+ struct statvfs fst;
+#endif
+
+ /*
+ * User must have privileges of pg_read_all_stats or have CREATE privilege
+ * for target tablespace, either explicitly granted or implicitly because
+ * it is default for current database.
+ */
+ if (tblspcOid != MyDatabaseTableSpace &&
+ !has_privs_of_role(GetUserId(), ROLE_PG_READ_ALL_STATS))
+ {
+ aclresult = object_aclcheck(TableSpaceRelationId, tblspcOid, GetUserId(), ACL_CREATE);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, OBJECT_TABLESPACE,
+ get_tablespace_name(tblspcOid));
+ }
+
+ if (tblspcOid == DEFAULTTABLESPACE_OID)
+ snprintf(tblspcPath, MAXPGPATH, "base");
+ else if (tblspcOid == GLOBALTABLESPACE_OID)
+ snprintf(tblspcPath, MAXPGPATH, "global");
+ else
+ snprintf(tblspcPath, MAXPGPATH, "%s/%u/%s", PG_TBLSPC_DIR, tblspcOid,
+ TABLESPACE_VERSION_DIRECTORY);
+
+#ifdef WIN32
+ if (GetDiskFreeSpaceEx(tblspcPath, &lpFreeBytesAvailable, NULL, NULL) == false)
+ return -1;
+
+ return lpFreeBytesAvailable.QuadPart; /* ULONGLONG part of ULARGE_INTEGER */
+#else
+ if (statvfs(tblspcPath, &fst) < 0)
+ return -1;
+
+ return fst.f_bavail * fst.f_bsize; /* available blocks times block size */
+#endif
+}
+
+Datum
+pg_tablespace_avail_oid(PG_FUNCTION_ARGS)
+{
+ Oid tblspcOid = PG_GETARG_OID(0);
+ int64 avail;
+
+ /*
+ * Not needed for correctness, but avoid non-user-facing error message
+ * later if the tablespace doesn't exist.
+ */
+ if (!SearchSysCacheExists1(TABLESPACEOID, ObjectIdGetDatum(tblspcOid)))
+ ereport(ERROR,
+ errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("tablespace with OID %u does not exist", tblspcOid));
+
+ avail = calculate_tablespace_avail(tblspcOid);
+
+ if (avail < 0)
+ PG_RETURN_NULL();
+
+ PG_RETURN_INT64(avail);
+}
+
+Datum
+pg_tablespace_avail_name(PG_FUNCTION_ARGS)
+{
+ Name tblspcName = PG_GETARG_NAME(0);
+ Oid tblspcOid = get_tablespace_oid(NameStr(*tblspcName), false);
+ int64 avail;
+
+ avail = calculate_tablespace_avail(tblspcOid);
+
+ if (avail < 0)
+ PG_RETURN_NULL();
+
+ PG_RETURN_INT64(avail);
+}
+
+
/*
* calculate size of (one fork of) a relation
*
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e6cf468ac9e..8c52a126ac1 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -241,10 +241,15 @@ describeTablespaces(const char *pattern, bool verbose)
printACLColumn(&buf, "spcacl");
appendPQExpBuffer(&buf,
",\n spcoptions AS \"%s\""
- ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\""
- ",\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",
+ ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"",
gettext_noop("Options"),
- gettext_noop("Size"),
+ gettext_noop("Size"));
+ if (pset.sversion >= 180000)
+ appendPQExpBuffer(&buf,
+ ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_avail(oid)) AS \"%s\"",
+ gettext_noop("Free"));
+ appendPQExpBuffer(&buf,
+ ",\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",
gettext_noop("Description"));
}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 42e427f8fe8..9d64da6bfb8 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7680,6 +7680,14 @@
descr => 'total disk space usage for the specified tablespace',
proname => 'pg_tablespace_size', provolatile => 'v', prorettype => 'int8',
proargtypes => 'name', prosrc => 'pg_tablespace_size_name' },
+{ oid => '6015',
+ descr => 'disk stats for the specified tablespace',
+ proname => 'pg_tablespace_avail', provolatile => 'v', prorettype => 'int8',
+ proargtypes => 'oid', prosrc => 'pg_tablespace_avail_oid' },
+{ oid => '6016',
+ descr => 'disk stats for the specified tablespace',
+ proname => 'pg_tablespace_avail', provolatile => 'v', prorettype => 'int8',
+ proargtypes => 'name', prosrc => 'pg_tablespace_avail_name' },
{ oid => '2324', descr => 'total disk space usage for the specified database',
proname => 'pg_database_size', provolatile => 'v', prorettype => 'int8',
proargtypes => 'oid', prosrc => 'pg_database_size_oid' },
--
2.47.2
