For review: dbsize patch
The attached patch is an update of the dbsize integration patch
discussed last week. This version includes the following functions:
pg_relation_size(text) - Get relation size by name/schema.name
pg_relation_size(oid) - Get relation size by OID
pg_tablespace_size(name) - Get tablespace size by name
pg_tablespace_size(oid) - Get tablespace size by OID
pg_database_size(name) - Get database size by name
pg_database_size(oid) - Get database size by OID
pg_size_pretty(int8) - Pretty print (and round) the byte size
specified (eg, 123456 = 121KB)
The only remaining function that last week's brief discussion indicated
was required is a replacement for total_relation_size() (or
pg_table_size() as it might now be called). I didn't realise until a few
minutes ago that this function (which is actually broken because it
doesn't handle schemas) was only committed a couple of months ago (v1.5,
http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/dbsize/dbsize.s
ql.in) and has therefore never been in a release version.
So should we include this new feature, and if so, how is it best added -
rewrite in C, or one long line in pg_proc?
Regards, Dave
Attachments:
dbsize.patchapplication/octet-stream; name=dbsize.patchDownload
? src/backend/utils/adt/dbsize.c
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.260
diff -c -r1.260 func.sgml
*** doc/src/sgml/func.sgml 26 Jun 2005 22:05:35 -0000 1.260
--- doc/src/sgml/func.sgml 27 Jun 2005 14:41:53 -0000
***************
*** 9021,9029 ****
processes on the server with <application>ps</>.
</para>
- <indexterm zone="functions-admin">
- <primary>pg_start_backup</primary>
- </indexterm>
<indexterm zone="functions-admin">
<primary>pg_stop_backup</primary>
--- 9021,9026 ----
***************
*** 9090,9095 ****
--- 9087,9179 ----
For details about proper usage of these functions, see
<xref linkend="backup-online">.
</para>
+
+ <para>
+ The functions shown in <xref
+ linkend="functions-admin-dbsize"> calculate the actual disk space
+ usage of database objects.
+ </para>
+
+ <table id="functions-admin-dbsize">
+ <title>Database Object Size Functions</title>
+ <tgroup cols="3">
+ <thead>
+ <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry>
+ <literal><function>pg_tablespace_size</function>(<parameter>oid</parameter>)</literal>
+ </entry>
+ <entry><type>int8</type></entry>
+ <entry>Calculates total disk space of the tablespace with the specified OID</entry>
+ </row>
+ <row>
+ <entry>
+ <literal><function>pg_tablespace_size</function>(<parameter>name</parameter>)</literal>
+ </entry>
+ <entry><type>int8</type></entry>
+ <entry>Calculates total disk space of the tablespace with the specified name</entry>
+ </row>
+ <row>
+ <entry>
+ <literal><function>pg_database_size</function>(<parameter>oid</parameter>)</literal>
+ </entry>
+ <entry><type>int8</type></entry>
+ <entry>Calculates total disk space of the database with the specified OID</entry>
+ </row>
+ <row>
+ <entry>
+ <literal><function>pg_database_size</function>(<parameter>name</parameter>)</literal>
+ </entry>
+ <entry><type>int8</type></entry>
+ <entry>Calculates total disk space of the database with the specified name</entry>
+ </row>
+ <row>
+ <entry>
+ <literal><function>pg_relation_size</function>(<parameter>oid</parameter>)</literal>
+ </entry>
+ <entry><type>int8</type></entry>
+ <entry>Calculates total disk space of the relation with the specified OID</entry>
+ </row>
+ <row>
+ <entry>
+ <literal><function>pg_relation_size</function>(<parameter>text</parameter>)</literal>
+ </entry>
+ <entry><type>int8</type></entry>
+ <entry>Calculates total disk space of the relation with the specified name. The name may be prefixed with a schema name if required</entry>
+ </row>
+ <row>
+ <entry>
+ <literal><function>pg_size_pretty</function>(<parameter>size_int8</parameter>)</literal>
+ </entry>
+ <entry><type>text</type></entry>
+ <entry>Formats the size in a human readable format with size units </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ <function>pg_tablespace_size</> and <function>pg_database_size</> accept an
+ oid or name of a tablespace or database, and return the disk space usage of the specified object.
+ </para>
+
+ <indexterm zone="functions-admin">
+ <primary>pg_relation_size</primary>
+ </indexterm>
+ <para>
+ <function>pg_relation_size</> accepts the oid or name of a table, index or
+ toast table. To calculate the total disk space usage of a table including all
+ its indexes and toast tables, each component should be added together.
+ </para>
+ <para>
+ <function>pg_size_pretty</> can be used to format the size of the
+ database objects in a human readable way, using kB, MB, GB or TB as appropriate.
+ </para>
+
</sect1>
</chapter>
Index: src/backend/utils/adt/Makefile
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/Makefile,v
retrieving revision 1.57
diff -c -r1.57 Makefile
*** src/backend/utils/adt/Makefile 1 Apr 2004 21:28:45 -0000 1.57
--- src/backend/utils/adt/Makefile 27 Jun 2005 10:29:49 -0000
***************
*** 24,30 ****
tid.o timestamp.o varbit.o varchar.o varlena.o version.o xid.o \
network.o mac.o inet_net_ntop.o inet_net_pton.o \
ri_triggers.o pg_lzcompress.o pg_locale.o formatting.o \
! ascii.o quote.o pgstatfuncs.o encode.o
like.o: like.c like_match.c
--- 24,30 ----
tid.o timestamp.o varbit.o varchar.o varlena.o version.o xid.o \
network.o mac.o inet_net_ntop.o inet_net_pton.o \
ri_triggers.o pg_lzcompress.o pg_locale.o formatting.o \
! ascii.o quote.o pgstatfuncs.o encode.o dbsize.o
like.o: like.c like_match.c
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.371
diff -c -r1.371 pg_proc.h
*** src/include/catalog/pg_proc.h 26 Jun 2005 03:04:01 -0000 1.371
--- src/include/catalog/pg_proc.h 27 Jun 2005 14:16:21 -0000
***************
*** 3031,3036 ****
--- 3031,3052 ----
DESCR("Finish taking an online backup");
+ DATA(insert OID = 1248 ( pg_tablespace_size PGNSP PGUID 12 f f t f v 1 20 "26" _null_ _null_ _null_ pg_tablespace_size_oid - _null_ ));
+ DESCR("Calculate total disk space usage for tablespace");
+ DATA(insert OID = 1250 ( pg_tablespace_size PGNSP PGUID 12 f f t f v 1 20 "19" _null_ _null_ _null_ pg_tablespace_size_name - _null_ ));
+ DESCR("Calculate total disk space usage for tablespace");
+ DATA(insert OID = 1269 ( pg_database_size PGNSP PGUID 12 f f t f v 1 20 "26" _null_ _null_ _null_ pg_database_size_oid - _null_ ));
+ DESCR("Calculate total disk space usage for database");
+ DATA(insert OID = 1295 ( pg_database_size PGNSP PGUID 12 f f t f v 1 20 "19" _null_ _null_ _null_ pg_database_size_name - _null_ ));
+ DESCR("Calculate total disk space usage for database");
+ DATA(insert OID = 2168 ( pg_relation_size PGNSP PGUID 12 f f t f v 1 20 "26" _null_ _null_ _null_ pg_relation_size_oid - _null_ ));
+ DESCR("Calculate total disk space usage for relation");
+ DATA(insert OID = 2285 ( pg_relation_size PGNSP PGUID 12 f f t f v 1 20 "25" _null_ _null_ _null_ pg_relation_size_name - _null_ ));
+ DESCR("Calculate total disk space usage for relation");
+ DATA(insert OID = 2286 ( pg_size_pretty PGNSP PGUID 12 f f t f v 1 25 "20" _null_ _null_ _null_ pg_size_pretty - _null_ ));
+ DESCR("Convert a long int to a human readable text using size units");
+
+
/* Aggregates (moved here from pg_aggregate for 7.3) */
DATA(insert OID = 2100 ( avg PGNSP PGUID 12 t f f f i 1 1700 "20" _null_ _null_ _null_ aggregate_dummy - _null_ ));
Index: src/include/utils/builtins.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/utils/builtins.h,v
retrieving revision 1.258
diff -c -r1.258 builtins.h
*** src/include/utils/builtins.h 17 Jun 2005 22:32:50 -0000 1.258
--- src/include/utils/builtins.h 27 Jun 2005 12:13:08 -0000
***************
*** 357,362 ****
--- 357,371 ----
extern Datum float84gt(PG_FUNCTION_ARGS);
extern Datum float84ge(PG_FUNCTION_ARGS);
+ /* dbsize.c */
+ extern Datum pg_tablespace_size_oid(PG_FUNCTION_ARGS);
+ extern Datum pg_tablespace_size_name(PG_FUNCTION_ARGS);
+ extern Datum pg_database_size_oid(PG_FUNCTION_ARGS);
+ extern Datum pg_database_size_name(PG_FUNCTION_ARGS);
+ extern Datum pg_relation_size_oid(PG_FUNCTION_ARGS);
+ extern Datum pg_relation_size_name(PG_FUNCTION_ARGS);
+ extern Datum pg_size_pretty(PG_FUNCTION_ARGS);
+
/* misc.c */
extern Datum nullvalue(PG_FUNCTION_ARGS);
extern Datum nonnullvalue(PG_FUNCTION_ARGS);
Dave Page wrote:
The only remaining function that last week's brief discussion indicated
was required is a replacement for total_relation_size() (or
pg_table_size() as it might now be called). I didn't realise until a
few minutes ago that this function (which is actually broken because it
doesn't handle schemas) was only committed a couple of months ago
(v1.5,
http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/dbsize/dbsize.sql.in) >
and has therefore never been in a release version.So should we include this new feature, and if so, how is it best added > -
rewrite in C, or one long line in pg_proc?
IIRC the initially submitted patch for this contained a function written in
C. It was only afterwards converted to SQL because of a comment by someone
else. I will have a look in the archives.
What I would like to have is a function that returns the table size (+
toast) + indexes. If it would be called pg_table_size(), that would be ok.
We should have one with oid and another with text.
Best Regards,
Michael Paesold
Dave Page wrote:
The attached patch is an update of the dbsize integration patch
discussed last week. This version includes the following functions:pg_relation_size(text) - Get relation size by name/schema.name
pg_relation_size(oid) - Get relation size by OID
pg_tablespace_size(name) - Get tablespace size by name
pg_tablespace_size(oid) - Get tablespace size by OID
pg_database_size(name) - Get database size by name
pg_database_size(oid) - Get database size by OID
pg_size_pretty(int8) - Pretty print (and round) the byte size
specified (eg, 123456 = 121KB)The only remaining function that last week's brief discussion indicated
was required is a replacement for total_relation_size() (or
pg_table_size() as it might now be called). I didn't realise until a few
minutes ago that this function (which is actually broken because it
doesn't handle schemas) was only committed a couple of months ago (v1.5,
http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/dbsize/dbsize.s
ql.in) and has therefore never been in a release version.
Uh, do any of these include the index size? TOAST size?
So should we include this new feature, and if so, how is it best added -
rewrite in C, or one long line in pg_proc?
I would follow whatever we do in pg_proc now.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 28 June 2005 00:58
To: Dave Page
Cc: PostgreSQL-development
Subject: Re: [HACKERS] For review: dbsize patchDave Page wrote:
The attached patch is an update of the dbsize integration patch
discussed last week. This version includes the following functions:pg_relation_size(text) - Get relation size by name/schema.name
pg_relation_size(oid) - Get relation size by OID
pg_tablespace_size(name) - Get tablespace size by name
pg_tablespace_size(oid) - Get tablespace size by OID
pg_database_size(name) - Get database size by name
pg_database_size(oid) - Get database size by OID
pg_size_pretty(int8) - Pretty print (and round) the byte size
specified (eg, 123456 = 121KB)The only remaining function that last week's brief
discussion indicated
was required is a replacement for total_relation_size() (or
pg_table_size() as it might now be called). I didn'trealise until a few
minutes ago that this function (which is actually broken because it
doesn't handle schemas) was only committed a couple ofmonths ago (v1.5,
http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/dbsiz
e/dbsize.sql.in) and has therefore never been in a release version.
Uh, do any of these include the index size? TOAST size?
No, only total_relation_size() does that.
So should we include this new feature, and if so, how is it
best added -
rewrite in C, or one long line in pg_proc?
I would follow whatever we do in pg_proc now.
There are a couple of SQL functions in there, but they are nowhere near
as long as this one. I'll look at implementing it in C.
Regards, Dave.
Import Notes
Resolved by subject fallback
Dave Page wrote:
Dave Page wrote:
The attached patch is an update of the dbsize integration patch
discussed last week. This version includes the following functions:pg_relation_size(text) - Get relation size by name/schema.name
pg_relation_size(oid) - Get relation size by OID
pg_tablespace_size(name) - Get tablespace size by name
pg_tablespace_size(oid) - Get tablespace size by OID
pg_database_size(name) - Get database size by name
pg_database_size(oid) - Get database size by OID
pg_size_pretty(int8) - Pretty print (and round) the byte size
specified (eg, 123456 = 121KB)
...
Uh, do any of these include the index size? TOAST size?
No, only total_relation_size() does that.
And we are dropping total_relation_size() in this patch, right?
I do like the new redesign --- it is very clear and consistent, and it
is clear you are looking at relation/tablespace/database levels in the
API.
Can we rename pg_relation_size to be pg_object_size(), because it
handles indexes and TOAST, and use pg_relation_size to return the total
usage of relations, and error if called with a TOAST or index?
I would like to give some way to report a total without having to query
the system catalogs.
So should we include this new feature, and if so, how is it
best added -
rewrite in C, or one long line in pg_proc?
I would follow whatever we do in pg_proc now.
There are a couple of SQL functions in there, but they are nowhere near
as long as this one. I'll look at implementing it in C.Regards, Dave.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073