Add pg_table_size() and pg_indexes_size() to provide more user-friendly

wrappers around the pg_relation_size() function.

Bernd Helmle, reviewed by Greg Smith

4 files changed, 133 insertions, 33 deletions

diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c
index d8d2aaa238c..47d1df88fa8 100644
--- a/src/backend/utils/adt/dbsize.c
+++ b/src/backend/utils/adt/dbsize.c
@@ -5,7 +5,7 @@
  * Copyright (c) 2002-2010, PostgreSQL Global Development Group
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/utils/adt/dbsize.c,v 1.26 2010/01/12 02:42:52 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/adt/dbsize.c,v 1.27 2010/01/19 05:50:18 tgl Exp $
  *
  */
 
@@ -246,7 +246,7 @@ pg_tablespace_size_name(PG_FUNCTION_ARGS)
 
 
 /*
- * calculate size of a relation
+ * calculate size of (one fork of) a relation
  */
 static int64
 calculate_relation_size(RelFileNode *rfn, ForkNumber forknum)
@@ -302,54 +302,148 @@ pg_relation_size(PG_FUNCTION_ARGS)
 	PG_RETURN_INT64(size);
 }
 
+/*
+ * Calculate total on-disk size of a TOAST relation, including its index.
+ * Must not be applied to non-TOAST relations.
+ */
+static int64
+calculate_toast_table_size(Oid toastrelid)
+{
+	int64      size = 0;
+	Relation   toastRel;
+	Relation   toastIdxRel;
+	ForkNumber forkNum;
+
+	toastRel = relation_open(toastrelid, AccessShareLock);
+
+	/* toast heap size, including FSM and VM size */
+	for (forkNum = 0; forkNum <= MAX_FORKNUM; forkNum++)
+		size += calculate_relation_size(&(toastRel->rd_node), forkNum);
+
+	/* toast index size, including FSM and VM size */
+	toastIdxRel = relation_open(toastRel->rd_rel->reltoastidxid, AccessShareLock);
+	for (forkNum = 0; forkNum <= MAX_FORKNUM; forkNum++)
+		size += calculate_relation_size(&(toastIdxRel->rd_node), forkNum);
+
+	relation_close(toastIdxRel, AccessShareLock);
+	relation_close(toastRel, AccessShareLock);
+
+	return size;
+}
 
 /*
- *	Compute the on-disk size of files for the relation according to the
- *	stat function, including heap data, index data, and toast data.
+ * Calculate total on-disk size of a given table,
+ * including FSM and VM, plus TOAST table if any.
+ * Indexes other than the TOAST table's index are not included.
+ *
+ * Note that this also behaves sanely if applied to an index or toast table;
+ * those won't have attached toast tables, but they can have multiple forks.
  */
 static int64
-calculate_total_relation_size(Oid Relid)
+calculate_table_size(Oid relOid)
 {
-	Relation	heapRel;
-	Oid			toastOid;
-	int64		size;
-	ListCell   *cell;
-	ForkNumber	forkNum;
+	int64      size = 0;
+	Relation   rel;
+	ForkNumber forkNum;
 
-	heapRel = relation_open(Relid, AccessShareLock);
-	toastOid = heapRel->rd_rel->reltoastrelid;
+	rel = relation_open(relOid, AccessShareLock);
 
-	/* Get the heap size */
-	size = 0;
+	/*
+	 * heap size, including FSM and VM
+	 */
 	for (forkNum = 0; forkNum <= MAX_FORKNUM; forkNum++)
-		size += calculate_relation_size(&(heapRel->rd_node), forkNum);
+		size += calculate_relation_size(&(rel->rd_node), forkNum);
+
+	/*
+	 * Size of toast relation
+	 */
+	if (OidIsValid(rel->rd_rel->reltoastrelid))
+		size += calculate_toast_table_size(rel->rd_rel->reltoastrelid);
+
+	relation_close(rel, AccessShareLock);
+
+	return size;
+}
+
+/*
+ * Calculate total on-disk size of all indexes attached to the given table.
+ *
+ * Can be applied safely to an index, but you'll just get zero.
+ */
+static int64
+calculate_indexes_size(Oid relOid)
+{
+	int64    size = 0;
+	Relation rel;
 
-	/* Include any dependent indexes */
-	if (heapRel->rd_rel->relhasindex)
+	rel = relation_open(relOid, AccessShareLock);
+
+	/*
+	 * Aggregate all indexes on the given relation
+	 */
+	if (rel->rd_rel->relhasindex)
 	{
-		List	   *index_oids = RelationGetIndexList(heapRel);
+		List	 *index_oids = RelationGetIndexList(rel);
+		ListCell *cell;
 
 		foreach(cell, index_oids)
 		{
 			Oid			idxOid = lfirst_oid(cell);
-			Relation	iRel;
+			Relation	idxRel;
+			ForkNumber  forkNum;
 
-			iRel = relation_open(idxOid, AccessShareLock);
+			idxRel = relation_open(idxOid, AccessShareLock);
 
 			for (forkNum = 0; forkNum <= MAX_FORKNUM; forkNum++)
-				size += calculate_relation_size(&(iRel->rd_node), forkNum);
+				size += calculate_relation_size(&(idxRel->rd_node), forkNum);
 
-			relation_close(iRel, AccessShareLock);
+			relation_close(idxRel, AccessShareLock);
 		}
 
 		list_free(index_oids);
 	}
 
-	/* Recursively include toast table (and index) size */
-	if (OidIsValid(toastOid))
-		size += calculate_total_relation_size(toastOid);
+	relation_close(rel, AccessShareLock);
+
+	return size;
+}
+
+Datum
+pg_table_size(PG_FUNCTION_ARGS)
+{
+	Oid			relOid = PG_GETARG_OID(0);
+
+	PG_RETURN_INT64(calculate_table_size(relOid));
+}
+
+Datum
+pg_indexes_size(PG_FUNCTION_ARGS)
+{
+	Oid			relOid = PG_GETARG_OID(0);
+
+	PG_RETURN_INT64(calculate_indexes_size(relOid));
+}
+
+/*
+ *	Compute the on-disk size of all files for the relation,
+ *	including heap data, index data, toast data, FSM, VM.
+ */
+static int64
+calculate_total_relation_size(Oid Relid)
+{
+	int64		size;
+
+	/*
+	 * Aggregate the table size, this includes size of
+	 * the heap, toast and toast index with free space
+	 * and visibility map
+	 */
+	size = calculate_table_size(Relid);
 
-	relation_close(heapRel, AccessShareLock);
+	/*
+	 * Add size of all attached indexes as well
+	 */
+	size += calculate_indexes_size(Relid);
 
 	return size;
 }
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 1740b2b38c4..9b36997499a 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -37,7 +37,7 @@
  * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.574 2010/01/17 22:56:23 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.575 2010/01/19 05:50:18 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	201001171
+#define CATALOG_VERSION_NO	201001181
 
 #endif
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index a498cc51720..e5d6b402b70 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.562 2010/01/15 09:19:07 heikki Exp $
+ * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.563 2010/01/19 05:50:18 tgl Exp $
  *
  * NOTES
  *	  The script catalog/genbki.pl reads this file and generates .bki
@@ -3702,13 +3702,17 @@ DESCR("total disk space usage for the specified database");
 DATA(insert OID = 2168 ( pg_database_size		PGNSP PGUID 12 1 0 0 f f f t f v 1 0 20 "19" _null_ _null_ _null_ _null_ pg_database_size_name _null_ _null_ _null_ ));
 DESCR("total disk space usage for the specified database");
 DATA(insert OID = 2325 ( pg_relation_size		PGNSP PGUID 14 1 0 0 f f f t f v 1 0 20 "2205" _null_ _null_ _null_ _null_ "select pg_catalog.pg_relation_size($1, ''main'')" _null_ _null_ _null_ ));
-DESCR("disk space usage for the specified table or index");
+DESCR("disk space usage for the main fork of the specified table or index");
 DATA(insert OID = 2332 ( pg_relation_size		PGNSP PGUID 12 1 0 0 f f f t f v 2 0 20 "2205 25" _null_ _null_ _null_ _null_ pg_relation_size _null_ _null_ _null_ ));
 DESCR("disk space usage for the specified fork of a table or index");
-DATA(insert OID = 2286 ( pg_total_relation_size		PGNSP PGUID 12 1 0 0 f f f t f v 1 0 20 "2205" _null_ _null_ _null_ _null_ pg_total_relation_size _null_ _null_ _null_ ));
-DESCR("total disk space usage for the specified table and associated indexes and toast tables");
+DATA(insert OID = 2286 ( pg_total_relation_size	PGNSP PGUID 12 1 0 0 f f f t f v 1 0 20 "2205" _null_ _null_ _null_ _null_ pg_total_relation_size _null_ _null_ _null_ ));
+DESCR("total disk space usage for the specified table and associated indexes");
 DATA(insert OID = 2288 ( pg_size_pretty			PGNSP PGUID 12 1 0 0 f f f t f v 1 0 25 "20" _null_ _null_ _null_ _null_ pg_size_pretty _null_ _null_ _null_ ));
 DESCR("convert a long int to a human readable text using size units");
+DATA(insert OID = 2997 ( pg_table_size			PGNSP PGUID 12 1 0 0 f f f t f v 1 0 20 "2205" _null_ _null_ _null_ _null_ pg_table_size _null_ _null_ _null_ ));
+DESCR("disk space usage for the specified table, including TOAST, free space and visibility map");
+DATA(insert OID = 2998 ( pg_indexes_size		PGNSP PGUID 12 1 0 0 f f f t f v 1 0 20 "2205" _null_ _null_ _null_ _null_ pg_indexes_size _null_ _null_ _null_ ));
+DESCR("disk space usage for all indexes attached to the specified table");
 
 DATA(insert OID = 2316 ( postgresql_fdw_validator PGNSP PGUID 12 1 0 0 f f f t f i 2 0 16 "1009 26" _null_ _null_ _null_ _null_ postgresql_fdw_validator _null_ _null_ _null_));
 
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 4989f51b16a..758aeee79fd 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.343 2010/01/02 16:58:10 momjian Exp $
+ * $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.344 2010/01/19 05:50:18 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -433,6 +433,8 @@ extern Datum pg_database_size_name(PG_FUNCTION_ARGS);
 extern Datum pg_relation_size(PG_FUNCTION_ARGS);
 extern Datum pg_total_relation_size(PG_FUNCTION_ARGS);
 extern Datum pg_size_pretty(PG_FUNCTION_ARGS);
+extern Datum pg_table_size(PG_FUNCTION_ARGS);
+extern Datum pg_indexes_size(PG_FUNCTION_ARGS);
 
 /* genfile.c */
 extern Datum pg_stat_file(PG_FUNCTION_ARGS);