For review: dbsize patch

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 patch

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/dbsiz
e/dbsize.s

ql.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.

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