Bruce Momjian wrote:
Patch applied. Thanks.
Can I get some documentation in the README for all the new
functionality.
Here it is.
Regards,
Andreas
Index: README.dbsize
===================================================================
RCS file: /projects/cvsroot/pgsql-server/contrib/dbsize/README.dbsize,v
retrieving revision 1.2
diff -u -r1.2 README.dbsize
--- README.dbsize 23 Jun 2002 20:09:23 -0000 1.2
+++ README.dbsize 2 Sep 2004 08:28:10 -0000
@@ -1,15 +1,51 @@
-This module contains two functions that report the size of a given
-database or relation. E.g.,
+This module contains several functions that report the size of a given
+database object.
+
+int8 database_size(name)
+int8 relation_size(text)
+int8 pg_database_size(oid)
+int8 pg_tablespace_size(oid)
+int8 pg_relation_size(oid)
+text pg_size_pretty(int8)
+
+These functions come in two flavours. The old style takes the name of the
+object, and supports databases and tables. These where the only functions
+supported for PostgreSQL up to 7.4.x.
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.
+Please note that for relation_size() only the pure table file usage is
+computed, not the space used by indexes and toast tables.
+
+Starting with PostgreSQL 8.0, additional functions taking the oid of the
+object where added.
+
+SELECT pg_database_size(1); -- template1 database
+SELECT pg_tablespace_size(1663); -- pg_default tablespace
+SELECT pg_relation_size(1259); -- pg_class table size
+
+pg_relation_size will report the size of for table, index and toast table
+OIDs, but won't add them automatically. To obtain the total size of a table
+including all helper files you'd have to do something like
+
+SELECT pg_relation_size(cl.oid) AS tablesize,
+ CASE WHEN reltoastrelid=0 THEN 0
+ ELSE pg_relation_size(reltoastrelid) END AS toastsize,
+ SUM(pg_relation_size(indexrelid)) AS indexsize,
+ pg_size_pretty(pg_relation_size(cl.oid)
+ + pg_relation_size(reltoastrelid)
+ + SUM(pg_relation_size(indexrelid))::int8) AS totalsize
+ FROM pg_class cl
+ JOIN pg_index ON cl.oid=indrelid
+ WHERE relname = 'pg_rewrite'
+ GROUP BY 1,2
+
+This sample query utilizes the helper function pg_size_pretty(int8), which
+formats the number of bytes into a convenient string using kB, MB, GB, TB.
+It is also contained in this module.
+
Copy this directory to contrib/dbsize in your PostgreSQL source tree.
Then just run make; make install. Finally, load the functions into any
database using dbsize.sql.
-
-When computing the size of a table, it does not include TOAST or index
-disk space.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster