I've written on like that a while ago:

http://webmail.postgresql.org/~petere/dbsize.html

The tarball can be rolled into contrib -- now that I think of it I don't
know why I never did that.

Never imagined this would have anything to do with that TODO item,
though.
I figured oid2name accomplished that.

Peter Eisentraut   peter_e@gmx.net

6 files changed, 203 insertions, 2 deletions

diff --git a/contrib/Makefile b/contrib/Makefile
index f31f604ed33..fc265216e11 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -1,4 +1,4 @@
-# $Header: /cvsroot/pgsql/contrib/Makefile,v 1.30 2001/10/12 23:19:09 tgl Exp $
+# $Header: /cvsroot/pgsql/contrib/Makefile,v 1.31 2002/02/22 23:05:34 momjian Exp $
 
 subdir = contrib
 top_builddir = ..
@@ -11,6 +11,7 @@ WANTED_DIRS = \
 		cube		\
 		dbase		\
 		dblink		\
+		dbsize		\
 		earthdistance	\
 		findoidjoins	\
 		fulltextindex	\
diff --git a/contrib/README b/contrib/README
index c5ff55c2f10..ec9c04d3c3d 100644
--- a/contrib/README
+++ b/contrib/README
@@ -48,7 +48,11 @@ dbase -
 
 dblink -
 	Allows remote query execution
-	by  Joe Conway, joe.conway@mail.com
+	by  Joe Conway <joe.conway@mail.com>
+
+dbsize -
+	Reports database and table disk space
+	by Peter Eisentraut <peter_e@gmx.net>
 
 earthdistance -
 	Operator for computing earth distance for two points
diff --git a/contrib/dbsize/Makefile b/contrib/dbsize/Makefile
new file mode 100644
index 00000000000..ad72a12be21
--- /dev/null
+++ b/contrib/dbsize/Makefile
@@ -0,0 +1,9 @@
+subdir = contrib/dbsize
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+
+MODULES = dbsize
+DATA_built = dbsize.sql
+DOCS = README.dbsize
+
+include $(top_srcdir)/contrib/contrib-global.mk
diff --git a/contrib/dbsize/README.dbsize b/contrib/dbsize/README.dbsize
new file mode 100644
index 00000000000..879341cd02e
--- /dev/null
+++ b/contrib/dbsize/README.dbsize
@@ -0,0 +1,11 @@
+This module contains two functions that report the size of a given
+database or relation.  E.g.,
+
+SELECT database_size('template1');
+SELECT relation_size('pg_class');
+
+These functions report the actual file system space.  Thus, users can
+avoid digging through the details of the database directories.
+
+Copy this directory to contrib/dbsize in your PostgreSQL source tree.
+Then just run make; make install.
diff --git a/contrib/dbsize/dbsize.c b/contrib/dbsize/dbsize.c
new file mode 100644
index 00000000000..597b307b068
--- /dev/null
+++ b/contrib/dbsize/dbsize.c
@@ -0,0 +1,169 @@
+#include "postgres.h"
+#include "fmgr.h"
+
+#include "access/heapam.h"
+#include "catalog/catalog.h"
+#include "catalog/catname.h"
+#include "catalog/pg_database.h"
+#include "utils/fmgroids.h"
+
+#include <stdlib.h>
+#include <sys/types.h>
+#include <dirent.h>
+#include <sys/stat.h>
+#include <unistd.h>
+#include <errno.h>
+
+
+static char *
+psnprintf(size_t len, const char *fmt,...)
+{
+	va_list		ap;
+	char	   *buf;
+
+	buf = palloc(len);
+
+	va_start(ap, fmt);
+	vsnprintf(buf, len, fmt, ap);
+	va_end(ap);
+
+	return buf;
+}
+
+
+
+/*
+ * SQL function: database_size(name) returns bigint
+ */
+
+PG_FUNCTION_INFO_V1(database_size);
+
+Datum
+database_size(PG_FUNCTION_ARGS)
+{
+	Name		dbname = PG_GETARG_NAME(0);
+
+	HeapTuple	tuple;
+	Relation	relation;
+	ScanKeyData	scanKey;
+	HeapScanDesc scan;
+	Oid			dbid;
+	char	   *dbpath;
+	DIR		   *dirdesc;
+	struct dirent *direntry;
+	int64		totalsize;
+
+	relation = heap_openr(DatabaseRelationName, AccessShareLock);
+	ScanKeyEntryInitialize(&scanKey, 0, Anum_pg_database_datname,
+						   F_NAMEEQ, NameGetDatum(dbname));
+	scan = heap_beginscan(relation, 0, SnapshotNow, 1, &scanKey);
+	tuple = heap_getnext(scan, 0);
+
+	if (!HeapTupleIsValid(tuple))
+		elog(ERROR, "database %s does not exist", NameStr(*dbname));
+
+	dbid = tuple->t_data->t_oid;
+	if (dbid == InvalidOid)
+		elog(ERROR, "invalid database id");
+
+	heap_endscan(scan);
+	heap_close(relation, NoLock);
+
+	dbpath = GetDatabasePath(dbid);
+
+	dirdesc = opendir(dbpath);
+	if (!dirdesc)
+		elog(ERROR, "could not open directory %s: %s", dbpath, strerror(errno));
+
+	totalsize = 0;
+	for (;;)
+	{
+		char	   *fullname;
+		struct stat statbuf;
+
+		errno = 0;
+		direntry = readdir(dirdesc);
+		if (!direntry)
+		{
+			if (errno)
+				elog(ERROR, "error reading directory: %s", strerror(errno));
+			else
+				break;
+		}
+
+		fullname = psnprintf(strlen(dbpath) + 1 + strlen(direntry->d_name) + 1,
+							 "%s/%s", dbpath, direntry->d_name);
+		if (stat(fullname, &statbuf) == -1)
+			elog(ERROR, "could not stat %s: %s", fullname, strerror(errno));
+		totalsize += statbuf.st_size;
+		pfree(fullname);
+	}
+
+	closedir(dirdesc);
+
+	PG_RETURN_INT64(totalsize);
+}
+
+
+
+/*
+ * SQL function: relation_size(name) returns bigint
+ */
+
+PG_FUNCTION_INFO_V1(relation_size);
+
+Datum
+relation_size(PG_FUNCTION_ARGS)
+{
+	Name		relname = PG_GETARG_NAME(0);
+
+	HeapTuple	tuple;
+	Relation	relation;
+	ScanKeyData	scanKey;
+	HeapScanDesc scan;
+	Oid			relnode;
+	int64		totalsize;
+	unsigned int segcount;
+
+	relation = heap_openr(RelationRelationName, AccessShareLock);
+	ScanKeyEntryInitialize(&scanKey, 0, Anum_pg_class_relname,
+						   F_NAMEEQ, NameGetDatum(relname));
+	scan = heap_beginscan(relation, 0, SnapshotNow, 1, &scanKey);
+	tuple = heap_getnext(scan, 0);
+
+	if (!HeapTupleIsValid(tuple))
+		elog(ERROR, "relation %s does not exist", NameStr(*relname));
+
+	relnode = ((Form_pg_class) GETSTRUCT(tuple))->relfilenode;
+	if (relnode == InvalidOid)
+		elog(ERROR, "invalid relation node id");
+
+	heap_endscan(scan);
+	heap_close(relation, NoLock);
+
+	totalsize = 0;
+	segcount = 0;
+	for (;;)
+	{
+		char	   *fullname;
+		struct stat statbuf;
+
+		if (segcount == 0)
+			fullname = psnprintf(25, "%u", (unsigned) relnode);
+		else
+			fullname = psnprintf(50, "%u.%u", (unsigned) relnode, segcount);
+
+		if (stat(fullname, &statbuf) == -1)
+		{
+			if (errno == ENOENT)
+				break;
+			else
+				elog(ERROR, "could not stat %s: %s", fullname, strerror(errno));
+		}
+		totalsize += statbuf.st_size;
+		pfree(fullname);
+		segcount++;
+	}
+
+	PG_RETURN_INT64(totalsize);
+}
diff --git a/contrib/dbsize/dbsize.sql.in b/contrib/dbsize/dbsize.sql.in
new file mode 100644
index 00000000000..d4d4d325252
--- /dev/null
+++ b/contrib/dbsize/dbsize.sql.in
@@ -0,0 +1,7 @@
+CREATE FUNCTION database_size (name) RETURNS bigint
+    AS '@MODULE_FILENAME@', 'database_size'
+    LANGUAGE C WITH (isstrict);
+
+CREATE FUNCTION relation_size (name) RETURNS bigint
+    AS '@MODULE_FILENAME@', 'relation_size'
+    LANGUAGE C WITH (isstrict);