On 2025/3/14 02:10, Christoph Berg wrote:
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()
I tested the patch under macos. Abnormal work:
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size
| Free | Description
------------+--------+----------+-------------------+---------+--------+-------+-------------
pg_default | quanzl | | | | 23 MB
|23 TB |
pg_global | quanzl | | | | 556 kB
| 23 TB |
(2 rows)
Actually my disk is 1TB.
According to the statvfs documentation for macOS
f_frsize The size in bytes of the minimum unit of allocation on
this file system.
f_bsize The preferred length of I/O requests for files on this
file system.
I tweaked the code a little bit. See the attachment.
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size
| Free | Description
------------+--------+----------+-------------------+---------+--------+--------+-------------
pg_default | quanzl | | | | 22 MB
| 116 GB |
pg_global | quanzl | | | | 556 kB
| 116 GB |
(2 rows)
In addition, many systems use 1000 as 1k to represent the storage size.
Shouldn't we consider this factor as well?
Christoph
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 1c3810e1a04..c0758b9244f 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -30089,6 +30089,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..a2637953ce0 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,99 @@ 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;
+
+#if defined(__darwin__)
+ return fst.f_bavail * fst.f_frsize; /* available blocks times block
size */
+#else
+ return fst.f_bavail * fst.f_bsize; /* available blocks times block size
*/
+#endif /* __darwin__ */
+#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 890822eaf79..39c3b8c2552 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7683,6 +7683,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' },
