Dbsize backend integration

Dave Page wrote:

The attached patch integrates dbsize functions into the backend, as per
discussion on -hackers. The following functions are included:

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_table_size(text) - Get table size (including all indexes and
toast tables) by name/schema.name
pg_table_size(oid) - Get table size (including all indexes and
toast tables) by OID
pg_size_pretty(int8) - Pretty print (and round) the byte size
specified (eg, 123456 = 121KB)

This is based on the dbsize contrib module, and previous patches from
Andreas Pflug and Ed L.

The dbsize module should be removed once this is applied, and the
catalog version incremented as I haven't included that in the patch.

OK, so you went with relation as heap/index/toast only, and table as the
total of them. I am not sure that makes sense because we usually equate
relation with table, and an index isn't a relation, really.

Do we have to use pg_object_size? Is there a better name? Are
indexes/toasts even objects?

Of course, these issues are all minor, but we might as well get them
resolved.

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

Bruce Momjian wrote:

Dave Page wrote:

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_table_size(text) - Get table size (including all indexes and
toast tables) by name/schema.name
pg_table_size(oid) - Get table size (including all indexes and
toast tables) by OID
pg_size_pretty(int8) - Pretty print (and round) the byte size
specified (eg, 123456 = 121KB)

OK, so you went with relation as heap/index/toast only, and table as the
total of them. I am not sure that makes sense because we usually equate
relation with table, and an index isn't a relation, really.

Do we have to use pg_object_size? Is there a better name? Are
indexes/toasts even objects?

Relation is not an ideal names, but I heard people talk about heap relation
and index relation. Indexes and tables (and sequences) are treated in a
similar way quite often. Think of ALTER TABLE example_index RENAME TO
another_index. This is even less obvious. Of course in relational theory,
an index would not be a relation, because an index is just implementation
detail.

I don't like object_size any better, since that makes me rather think of
large objects or rows as objects (object id...).

Perhaps pg_table_size should be split into pg_table_size and
pg_indexes_size, where pg_indexes_size is the aggregate of all indexes on a
table und pg_table_size is just table+toast+toast-index.

If noone has a better idea for pg_relation_size, I would rather keep it for
consistency with the contrib module, and because it's not too far off.

Best Regards,
Michael Paesold

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Wed 6/29/2005 2:16 AM
To: Dave Page
Cc: PostgreSQL-patches; PostgreSQL-development
Subject: Re: [PATCHES] Dbsize backend integration

OK, so you went with relation as heap/index/toast only, and table as the
total of them. I am not sure that makes sense because we usually equate
relation with table, and an index isn't a relation, really.

Err, yes - posted that before I got your reply!

Do we have to use pg_object_size? Is there a better name? Are
indexes/toasts even objects?

Yeah, I think perhaps pg_object_size is better in some ways than pg_relation_size, however I stuck with relation because (certainly in pgAdmin world) we tend to think of pretty much anything as an object. I could go either way on that though, however Michael doesn't seem so keen.

So, one for pg_object_size, one on the fench and one against :-). Anyone else got a preference?

Regards, Dave.

Dave Page wrote:

-----Original Message----- From: Bruce Momjian
[mailto:pgman@candle.pha.pa.us] Sent: Wed 6/29/2005 2:16 AM To: Dave
Page Cc: PostgreSQL-patches; PostgreSQL-development Subject: Re:
[PATCHES] Dbsize backend integration

OK, so you went with relation as heap/index/toast only, and table as the
total of them. I am not sure that makes sense because we usually equate
relation with table, and an index isn't a relation, really.

Err, yes - posted that before I got your reply!

Do we have to use pg_object_size? Is there a better name? Are
indexes/toasts even objects?

Yeah, I think perhaps pg_object_size is better in some ways than
pg_relation_size, however I stuck with relation because (certainly in
pgAdmin world) we tend to think of pretty much anything as an object.
I could go either way on that though, however Michael doesn't seem so
keen.

So, one for pg_object_size, one on the fench and one against :-). Anyone
else got a preference?

I have a new idea --- pg_storage_size(). That would do just the
toast/index/heap, and pg_relation_size() gets a total of them all, and
only works on heap, no index or toast.

How is that?

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

Michael Paesold wrote:

Do we have to use pg_object_size? Is there a better name? Are
indexes/toasts even objects?

Relation is not an ideal names, but I heard people talk about heap relation
and index relation. Indexes and tables (and sequences) are treated in a
similar way quite often. Think of ALTER TABLE example_index RENAME TO
another_index. This is even less obvious. Of course in relational theory,
an index would not be a relation, because an index is just implementation
detail.

I don't like object_size any better, since that makes me rather think of
large objects or rows as objects (object id...).

Perhaps pg_table_size should be split into pg_table_size and
pg_indexes_size, where pg_indexes_size is the aggregate of all indexes on a
table und pg_table_size is just table+toast+toast-index.

If noone has a better idea for pg_relation_size, I would rather keep it for
consistency with the contrib module, and because it's not too far off.

Yea, but then we have toast and we would need another name. I suggested
pg_storage_size() because it relates to a storage unit (index, toast,
etc), and not a real object or relation.

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

Bruce Momjian wrote:

Yea, but then we have toast and we would need another name. I suggested
pg_storage_size() because it relates to a storage unit (index, toast,
etc), and not a real object or relation.

I'm not really happy that all functions change their names (more
versioning handling in pgadmin), but pg_storage_size is certainly the
most precise name.

Regards,
Andreas

Andreas Pflug <pgadmin@pse-consulting.de> writes:

I'm not really happy that all functions change their names (more
versioning handling in pgadmin), but pg_storage_size is certainly the
most precise name.

Actually, it seems excessively imprecise to me: the name conveys nothing
at all to help you remember what the definition is. "storage" could
mean any of the different definitions that have been kicked around in
this thread.

regards, tom lane

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 29 June 2005 12:46
To: Dave Page
Cc: PostgreSQL-patches; PostgreSQL-development
Subject: Re: [PATCHES] Dbsize backend integration

I have a new idea --- pg_storage_size().

I'm not against that one, but I think Tom's point is vaild. I cannot
think of anything better at the moment though (maybe pg_component_size,
but that's equally random) :-(

Anyone else? Please? Someone? Anyone? :-)

That would do just the
toast/index/heap, and pg_relation_size() gets a total of them all, and
only works on heap, no index or toast.

The totalling version (whatever it ends up being called) should
definitely work on toast tables, as it is a legitimate use case to want
to see the size of such a table and it's indexes, independent of the
owner table. There is no need for it to work on an index though,
however, it will return the right answer if it is used that way, so I
think that trying to prevent it will be unecessary code that simply
slows down the majority of invocations of the function for no benefit.

Regards, Dave.

On Jun 30, 2005, at 5:48 PM, Dave Page wrote:

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 29 June 2005 12:46

<snip />

I have a new idea --- pg_storage_size().

I'm not against that one, but I think Tom's point is vaild. I cannot
think of anything better at the moment though (maybe
pg_component_size,
but that's equally random) :-(

Anyone else? Please? Someone? Anyone? :-)

I'm still unclear as to what exactly is trying to be captured by the
names, so I'll just throw some out and see if they're intuitive to
anyone.

pg_table_extensions_size()
pg_table_support_size()
pg_relation_extensions_size()
pg_relation_support_size()

pg_relation_extended_size()

My two yen... if that :)

Michael Glaesemann
grzm myrealbox com

-----Original Message-----
From: Michael Glaesemann [mailto:grzm@myrealbox.com]
Sent: 30 June 2005 10:01
To: Dave Page
Cc: PostgreSQL-patches; PostgreSQL-development
Subject: Re: [PATCHES] Dbsize backend integration

I'm still unclear as to what exactly is trying to be captured by the
names, so I'll just throw some out and see if they're intuitive to
anyone.

Thanks Michael. We have 2 functions - 1 returns the on disk size of a
table or index without any additional parts such as indexes or toast
tables. The other function returns the total on disk size of a table and
all associated indexes and toast tables (and any indexes they might
have). The current names are pg_relation_size() for the first function,
and pg_table_size() for the second.

pg_table_extensions_size()
pg_table_support_size()
pg_relation_extensions_size()
pg_relation_support_size()

pg_relation_extended_size()

Hmm, none of those really stand out - but thanks anyway. More are
welcome :-)

Regards, Dave

-----Original Message-----
From: viy@mits.lv [mailto:viy@mits.lv]
Sent: 30 June 2005 10:29
To: Bruce Momjian; Dave Page
Cc: PostgreSQL-patches; PostgreSQL-development
Subject: Re: [PATCHES] Dbsize backend integration

Maybe pg_trait_size() or pg_property_size() will do?

I don't think property is right. What's your thinking for trait though?

Regards, Dave

I have a new idea --- pg_storage_size().

I'm not against that one, but I think Tom's point is vaild. I cannot
think of anything better at the moment though (maybe pg_component_size,
but that's equally random) :-(

Anyone else? Please? Someone? Anyone? :-)

Maybe pg_trait_size() or pg_property_size() will do?

--

Victor

---- Msg sent via @Mail ISP MiTS - http://www.mits.lv/

"Dave Page" <dpage@vale-housing.co.uk> writes:

Thanks Michael. We have 2 functions - 1 returns the on disk size of a
table or index without any additional parts such as indexes or toast
tables. The other function returns the total on disk size of a table and
all associated indexes and toast tables (and any indexes they might
have). The current names are pg_relation_size() for the first function,
and pg_table_size() for the second.

That seems to me to work perfectly fine. "Relation" is being used here
in its PG-jargon sense, that is an object described by one row of
pg_class, and "table" is being used from the user's point of view.

Or at least sort of --- I think most users know enough to distinguish
tables and indexes. We can figure that the toast table and its index
ought to be considered part of the "base" table, though, since the
user doesn't have a choice about those.

I've not been following the thread closely, so maybe this was already
proposed and rejected, but what about:

pg_relation_size: size of exactly the relation you point it at
(table, index, toast table, whatever)

pg_table_size: point it at heap, get size of heap+toast+toast_index

pg_index_size: point it at heap, get size of all indexes for heap
(excludes toast index)

pg_total_size: point it at heap, get table_size + index_size

regards, tom lane

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 30 June 2005 14:41
To: Dave Page
Cc: Michael Glaesemann; PostgreSQL-patches; PostgreSQL-development
Subject: Re: [PATCHES] Dbsize backend integration

"Dave Page" <dpage@vale-housing.co.uk> writes:

Thanks Michael. We have 2 functions - 1 returns the on disk

size of a

table or index without any additional parts such as indexes or toast
tables. The other function returns the total on disk size

of a table and

all associated indexes and toast tables (and any indexes they might
have). The current names are pg_relation_size() for the

first function,

and pg_table_size() for the second.

That seems to me to work perfectly fine. "Relation" is being
used here
in its PG-jargon sense, that is an object described by one row of
pg_class, and "table" is being used from the user's point of view.

I'm beginning to think that this is the best we'll get. Still, it is
documented, so it's not like the name has to be a perfect description of
the function's purpose.

Or at least sort of --- I think most users know enough to distinguish
tables and indexes. We can figure that the toast table and its index
ought to be considered part of the "base" table, though, since the
user doesn't have a choice about those.

I've not been following the thread closely, so maybe this was already
proposed and rejected, but what about:

pg_relation_size: size of exactly the relation you point it at
(table, index, toast table, whatever)

pg_table_size: point it at heap, get size of
heap+toast+toast_index

pg_index_size: point it at heap, get size of all
indexes for heap
(excludes toast index)

pg_total_size: point it at heap, get table_size + index_size

That moves the goal posts somewhat. We had settled on just the 2
functions - other combinations could easily be returned using
pg_relation_size and a little SQL for those people with more unusual
requirements. Unless you're particularly set on including these extra
two, I'm inclined to leave it as it is.

Regards, Dave.

"Dave Page" <dpage@vale-housing.co.uk> writes:

I've not been following the thread closely, so maybe this was already
proposed and rejected, but what about:
[4 functions]

That moves the goal posts somewhat.

Fair enough. The two you described are OK by me.

regards, tom lane

On 6/30/05, Dave Page <dpage@vale-housing.co.uk> wrote:

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 29 June 2005 12:46
To: Dave Page
Cc: PostgreSQL-patches; PostgreSQL-development
Subject: Re: [PATCHES] Dbsize backend integration

I have a new idea --- pg_storage_size().

I'm not against that one, but I think Tom's point is vaild. I cannot
think of anything better at the moment though (maybe pg_component_size,
but that's equally random) :-(

Anyone else? Please? Someone? Anyone? :-)

pg_diskspace_size()
pg_diskusage_size()
pg_media_used_size()
pg_allocated_size()
pg_diskspace_used()

Regards,
Dawid

PS: Yep, they aren't good...

Dawid Kuroczko wrote:

On 6/30/05, Dave Page <dpage@vale-housing.co.uk> wrote:

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 29 June 2005 12:46
To: Dave Page
Cc: PostgreSQL-patches; PostgreSQL-development
Subject: Re: [PATCHES] Dbsize backend integration

I have a new idea --- pg_storage_size().

I'm not against that one, but I think Tom's point is vaild. I cannot
think of anything better at the moment though (maybe pg_component_size,
but that's equally random) :-(

Anyone else? Please? Someone? Anyone? :-)

pg_df(text,text)

where the $1 would be the relation name and $2 would
be the type of output (human readable, bytes etc...)

J

pg_diskspace_size()
pg_diskusage_size()
pg_media_used_size()
pg_allocated_size()
pg_diskspace_used()

Regards,
Dawid

PS: Yep, they aren't good...

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

Dave Page wrote:

That would do just the
toast/index/heap, and pg_relation_size() gets a total of them all, and
only works on heap, no index or toast.

The totalling version (whatever it ends up being called) should
definitely work on toast tables, as it is a legitimate use case to want
to see the size of such a table and it's indexes, independent of the
owner table. There is no need for it to work on an index though,
however, it will return the right answer if it is used that way, so I
think that trying to prevent it will be unecessary code that simply
slows down the majority of invocations of the function for no benefit.

Agreed.

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

viy@mits.lv wrote:

I have a new idea --- pg_storage_size().

I'm not against that one, but I think Tom's point is vaild. I cannot
think of anything better at the moment though (maybe pg_component_size,
but that's equally random) :-(

Anyone else? Please? Someone? Anyone? :-)

Maybe pg_trait_size() or pg_property_size() will do?

I don't think so. I think trait and property suggests an aspect of the
object, so saying trait/property size is saying I am talking about an
aspect of the object, while for a heap, its size is really its size, it
isn't an aspect of its size.

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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I don't think so. I think trait and property suggests an aspect of the
object, so saying trait/property size is saying I am talking about an
aspect of the object, while for a heap, its size is really its size, it
isn't an aspect of its size.

I haven't been following this discussion but, uh, does the fact that I have
absolutely no clue what pg_trait_size() or pg_property_size() would be
measuring count for anything? My best guess here is that it's for measuring
the space taken up by a column which doesn't make a lot of sense.

I think you need to think about unambiguous words that help the user
understand what the function does; words that the user might guess if they
were looking for a function to do that, whatever that is.

Not words that are sufficiently vague as to include whatever it's actually
doing but offer no clue what that is. There are an infinite number of such
words to pick and no way for the user to figure out what he or she is looking
for.

--
greg

-----Original Message-----
From: Dawid Kuroczko [mailto:qnex42@gmail.com]
Sent: 30 June 2005 22:21
To: Dave Page
Cc: PostgreSQL-development
Subject: Re: [HACKERS] [PATCHES] Dbsize backend integration

On 6/30/05, Dave Page <dpage@vale-housing.co.uk> wrote:

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 29 June 2005 12:46
To: Dave Page
Cc: PostgreSQL-patches; PostgreSQL-development
Subject: Re: [PATCHES] Dbsize backend integration

I have a new idea --- pg_storage_size().

I'm not against that one, but I think Tom's point is vaild. I cannot
think of anything better at the moment though (maybe

pg_component_size,

but that's equally random) :-(

Anyone else? Please? Someone? Anyone? :-)

pg_diskspace_size()
pg_diskusage_size()
pg_media_used_size()
pg_allocated_size()
pg_diskspace_used()

Unfortunately I think those are too generic - we also have
pg_database_size() and pg_tablespace_size() for which these would work.

Regards, Dave

Is a new version of this patch coming?

---------------------------------------------------------------------------

Bruce Momjian wrote:

Dave Page wrote:

-----Original Message----- From: Bruce Momjian
[mailto:pgman@candle.pha.pa.us] Sent: Wed 6/29/2005 2:16 AM To: Dave
Page Cc: PostgreSQL-patches; PostgreSQL-development Subject: Re:
[PATCHES] Dbsize backend integration

OK, so you went with relation as heap/index/toast only, and table as the
total of them. I am not sure that makes sense because we usually equate
relation with table, and an index isn't a relation, really.

Err, yes - posted that before I got your reply!

Do we have to use pg_object_size? Is there a better name? Are
indexes/toasts even objects?

Yeah, I think perhaps pg_object_size is better in some ways than
pg_relation_size, however I stuck with relation because (certainly in
pgAdmin world) we tend to think of pretty much anything as an object.
I could go either way on that though, however Michael doesn't seem so
keen.

So, one for pg_object_size, one on the fench and one against :-). Anyone
else got a preference?

I have a new idea --- pg_storage_size(). That would do just the
toast/index/heap, and pg_relation_size() gets a total of them all, and
only works on heap, no index or toast.

How is that?

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

-- 
  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: 02 July 2005 21:30
To: Bruce Momjian
Cc: Dave Page; PostgreSQL-patches; PostgreSQL-development
Subject: Re: [PATCHES] Dbsize backend integration

Is a new version of this patch coming?

Yup, attached. Per our earlier conversation, pg_dbfile_size() now
returns the size of a table or index, and pg_relation_size() returns the
total size of a relation and all associated indexes and toast tables
etc.

Regards, Dave.

Dave Page wrote:

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 02 July 2005 21:30
To: Bruce Momjian
Cc: Dave Page; PostgreSQL-patches; PostgreSQL-development
Subject: Re: [PATCHES] Dbsize backend integration

Is a new version of this patch coming?

Yup, attached. Per our earlier conversation, pg_dbfile_size() now
returns the size of a table or index, and pg_relation_size() returns the
total size of a relation and all associated indexes and toast tables
etc.

pg_relation_size's name is quite unfortunate, since the 8.0 contrib
function does something different. And pg_dbfile_size sounds misleading,
suggesting it takes a filename or relfilenode as parameter.

Regards,
Andreas

Andreas Pflug wrote:

Dave Page wrote:

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 02 July 2005 21:30
To: Bruce Momjian
Cc: Dave Page; PostgreSQL-patches; PostgreSQL-development
Subject: Re: [PATCHES] Dbsize backend integration

Is a new version of this patch coming?

Yup, attached. Per our earlier conversation, pg_dbfile_size() now
returns the size of a table or index, and pg_relation_size() returns the
total size of a relation and all associated indexes and toast tables
etc.

pg_relation_size's name is quite unfortunate, since the 8.0 contrib
function does something different. And pg_dbfile_size sounds misleading,
suggesting it takes a filename or relfilenode as parameter.

Hmm. I don't see how we can call it pg_table_size because people think
of tables and indexes, while relation has a more inclusive suggestion.

As far as pg_dbfile_size, do you have any other idea for a name? To me,
it returns the size of the 'db file' associated with the
heap/index/toast.

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

On Jul 3, 2005, at 8:35 AM, Bruce Momjian wrote:

Andreas Pflug wrote:

Dave Page wrote:

Yup, attached. Per our earlier conversation, pg_dbfile_size() now
returns the size of a table or index, and pg_relation_size()
returns the
total size of a relation and all associated indexes and toast tables
etc.

pg_relation_size's name is quite unfortunate, since the 8.0 contrib
function does something different. And pg_dbfile_size sounds
misleading,
suggesting it takes a filename or relfilenode as parameter.

Hmm. I don't see how we can call it pg_table_size because people
think
of tables and indexes, while relation has a more inclusive suggestion.

I'm not familiar enough with the backend code to know if there's a
semantic difference between how relation and table are treated, so my
line of reasoning may be flawed. However, I try to use the term
relation when I'm discussing things at a logical level--the
predicates the data represents. Indexes and toast tables are
implementation details, separate from the predicates the relation
represents.

The distinction between table and relation is very small, and using
both pg_table_size and pg_relation_size but with different meanings
is going to have people dependent on the documentation to remember
the difference; pg_table_size and pg_relation_size both have the same
meaning to me: the size of the table or index. I'd lean towards
pg_table_size because this has a looser meaning that more easily
includes indexes. An index doesn't really contain predicates and one
doesn't store things in them directly.

I think what's needed is a term that expresses the more inclusive or
implementation-specific nature of the function that returns table +
indexes + toast tables + kitchen sink.

pg_tableall_size? pg_tablefull_size? pg_tableplus_size?
pg_tableandmore_size? pg_tableimplementation_size?
pg_tablekitchensink_size? ;)

I recognize the desire to have a relatively short name for the
functions, but perhaps a longer one is needed to capture the
distinction between the two. (Though it's kind of frustrating that
none of us have been able to hit on a term that accurately and
succinctly describes it.)

Michael Glaesemann
grzm myrealbox com

Bruce Momjian wrote:

Andreas Pflug wrote:

Dave Page wrote:

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 02 July 2005 21:30
To: Bruce Momjian
Cc: Dave Page; PostgreSQL-patches; PostgreSQL-development
Subject: Re: [PATCHES] Dbsize backend integration

Is a new version of this patch coming?

Yup, attached. Per our earlier conversation, pg_dbfile_size() now
returns the size of a table or index, and pg_relation_size() returns the
total size of a relation and all associated indexes and toast tables
etc.

pg_relation_size's name is quite unfortunate, since the 8.0 contrib
function does something different. And pg_dbfile_size sounds misleading,
suggesting it takes a filename or relfilenode as parameter.

Hmm. I don't see how we can call it pg_table_size because people think
of tables and indexes, while relation has a more inclusive suggestion.

We could, taking the same logic as GRANT which uses the keyword TABLE
for sequences and Indexes too, but it's certainly not favourable.

As far as pg_dbfile_size, do you have any other idea for a name? To me,
it returns the size of the 'db file' associated with the
heap/index/toast.

How about pg_relation_size(oid, bool) with the second optional parameter
to count all additional objects too (the 'total' flag).

Regards,
Andreas

On 7/3/05, Andreas Pflug <pgadmin@pse-consulting.de> wrote:

Yup, attached. Per our earlier conversation, pg_dbfile_size() now
returns the size of a table or index, and pg_relation_size() returns the
total size of a relation and all associated indexes and toast tables
etc.

pg_relation_size's name is quite unfortunate, since the 8.0 contrib
function does something different. And pg_dbfile_size sounds misleading,
suggesting it takes a filename or relfilenode as parameter.

Oh, I think pg_dbfile_size is best so far. Assuming someone gives it a
filename, she'll get an error message. So practically it cannot be used
wrong by mistake. It is not so with other names proposed for that
function. Their names suggest they'll happily accept table/index/whatever
and return some size... But what size, that is the question. At least
pg_dbfile_size states that clearly. :)

As for pg_relation_size. I think its good enough, or at least I don't know
any better. I think it is better than pg_table_size, since people tend to
have personalized ideas what a table size is (a table with TOAST and
TOAST's indexes; a table with PRIMARY KEY,UNIQUE constraint indexes,
a table with all indexes involved,. etc/). pg_relation_size seems. at least
to me, to imply that its greedy and will take not only the table, and also
things the table is closely related to, like all the indexes.

The fun will begin when we'll have full working table partitioning and
multitable
indexes. ;))))

Regards,
Dawid

Dawid Kuroczko <qnex42@gmail.com> writes:

Oh, I think pg_dbfile_size is best so far.

I think it's by far the ugliest suggestion yet :-(

Andreas's suggestion of having just one function with a bool parameter
might be a workable compromise.

regards, tom lane

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 03 July 2005 17:10
To: Dawid Kuroczko
Cc: Andreas Pflug; Dave Page; Bruce Momjian;
PostgreSQL-patches; PostgreSQL-development
Subject: Re: [HACKERS] [PATCHES] Dbsize backend integration

Dawid Kuroczko <qnex42@gmail.com> writes:

Oh, I think pg_dbfile_size is best so far.

I think it's by far the ugliest suggestion yet :-(

Why? It does exactly what it says on the tin! It might not be that nice,
but it does describe what it does - and noone yet has come up with
anything less ambiguous or misleading imho.

Andreas's suggestion of having just one function with a bool parameter
might be a workable compromise.

Aside from the fact that's a change to the API that we had settled on,
it doesn't solve the actual problem of needing a suitable name for a
function that returns the size of a table /or/ index. pg_relation_size()
or pg_table_size() can't be used for precisely the reason they were
rejected for that purpose in the first place.

Regards, Dave.

"Dave Page" <dpage@vale-housing.co.uk> writes:

Aside from the fact that's a change to the API that we had settled on,
it doesn't solve the actual problem of needing a suitable name for a
function that returns the size of a table /or/ index. pg_relation_size()
or pg_table_size() can't be used for precisely the reason they were
rejected for that purpose in the first place.

Rejected by whom? pg_relation_size is an excellent choice for that.

regards, tom lane

Tom Lane wrote:

"Dave Page" <dpage@vale-housing.co.uk> writes:

Aside from the fact that's a change to the API that we had settled on,
it doesn't solve the actual problem of needing a suitable name for a
function that returns the size of a table /or/ index. pg_relation_size()
or pg_table_size() can't be used for precisely the reason they were
rejected for that purpose in the first place.

Rejected by whom? pg_relation_size is an excellent choice for that.

We mostly tell people that table and relation are synonmous. Though
there is a distinction, it seems error-prone to rely on that distinction
in the API.

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

Bruce Momjian wrote:

Tom Lane wrote:

"Dave Page" <dpage@vale-housing.co.uk> writes:

Aside from the fact that's a change to the API that we had settled on,
it doesn't solve the actual problem of needing a suitable name for a
function that returns the size of a table /or/ index. pg_relation_size()
or pg_table_size() can't be used for precisely the reason they were
rejected for that purpose in the first place.

Rejected by whom? pg_relation_size is an excellent choice for that.

We mostly tell people that table and relation are synonmous. Though
there is a distinction, it seems error-prone to rely on that distinction
in the API.

I am starting to warm up to the idea of using "relation" as the combined
total. Was that the proposal? Are we prepared to make that distinction
in other places?

-- 
  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: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 04 July 2005 14:54
To: Dave Page
Cc: Dawid Kuroczko; Andreas Pflug; Bruce Momjian;
PostgreSQL-patches; PostgreSQL-development
Subject: Re: [HACKERS] [PATCHES] Dbsize backend integration

"Dave Page" <dpage@vale-housing.co.uk> writes:

Aside from the fact that's a change to the API that we had

settled on,

it doesn't solve the actual problem of needing a suitable name for a
function that returns the size of a table /or/ index.

pg_relation_size()

or pg_table_size() can't be used for precisely the reason they were
rejected for that purpose in the first place.

Rejected by whom? pg_relation_size is an excellent choice for that.

Bruce didn't like it
(http://archives.postgresql.org/pgsql-hackers/2005-06/msg01410.php), and
you seemed to object as well
(http://archives.postgresql.org/pgsql-hackers/2005-06/msg01247.php)

Personally I'm beyond caring much now as the amount of time spent trying
to name these simple functions is wildly disproportionate the the effort
take to actually code them. I think we just need to agree there is no
perfect name and rely on the comments and docs to guide people. I think
the current names work OK, and Bruce and Dawid at least agree!

Regards, Dave.

On Monday 04 July 2005 10:11, Dave Page wrote:

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 04 July 2005 14:54
To: Dave Page
Cc: Dawid Kuroczko; Andreas Pflug; Bruce Momjian;
PostgreSQL-patches; PostgreSQL-development
Subject: Re: [HACKERS] [PATCHES] Dbsize backend integration

"Dave Page" <dpage@vale-housing.co.uk> writes:

Aside from the fact that's a change to the API that we had

settled on,

it doesn't solve the actual problem of needing a suitable name for a
function that returns the size of a table /or/ index.

pg_relation_size()

or pg_table_size() can't be used for precisely the reason they were
rejected for that purpose in the first place.

Rejected by whom? pg_relation_size is an excellent choice for that.

Bruce didn't like it
(http://archives.postgresql.org/pgsql-hackers/2005-06/msg01410.php), and
you seemed to object as well
(http://archives.postgresql.org/pgsql-hackers/2005-06/msg01247.php)

Personally I'm beyond caring much now as the amount of time spent trying
to name these simple functions is wildly disproportionate the the effort
take to actually code them. I think we just need to agree there is no
perfect name and rely on the comments and docs to guide people. I think
the current names work OK, and Bruce and Dawid at least agree!

Actually I'd agree with Tom, pg_dbfile_size is ugly, and suggest to me I could
use a filename as an argument. ISTM that if we think that functions like
pg_database_size and pg_tablespace_size all make sense, the natural extension
would be functions called pg_index_size to tell us the size of an index,
pg_table_size to tell us the size of a table (table+toast) without it's
indexes, and some form of pg_table_plus_indexes_size for a table and its
indexes for those that feel we need both. I'm not sold we need a function
that can return either an index or table size, but if so something like
pg_object_size seems ambigious enough to work, and is future proof enough to
handle things like materialized views when and if they arise.

Just my .02 :-)

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Robert Treat wrote:

Actually I'd agree with Tom, pg_dbfile_size is ugly, and suggest to me I could
use a filename as an argument. ISTM that if we think that functions like
pg_database_size and pg_tablespace_size all make sense, the natural extension
would be functions called pg_index_size to tell us the size of an index,
pg_table_size to tell us the size of a table (table+toast) without it's
indexes, and some form of pg_table_plus_indexes_size for a table and its
indexes for those that feel we need both. I'm not sold we need a function
that can return either an index or table size, but if so something like
pg_object_size seems ambigious enough to work, and is future proof enough to
handle things like materialized views when and if they arise.

You are into the cycle we were in. We discussed pg_object size (too
vague) and pg_index_size (needs pg_toast_size too, and maybe toast
indexes; too many functions).

-- 
  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: Robert Treat [mailto:xzilla@users.sourceforge.net]
Sent: 04 July 2005 18:21
To: Dave Page
Cc: Tom Lane; Dawid Kuroczko; Andreas Pflug; Bruce Momjian;
PostgreSQL-patches; PostgreSQL-development
Subject: Re: [HACKERS] [PATCHES] Dbsize backend integration

Actually I'd agree with Tom, pg_dbfile_size is ugly, and
suggest to me I could
use a filename as an argument.

I won't repeat Bruce's reply to you, but as an FYI, the reason Bruce
suggested pg_dbfile_size over the nicer pg_file_size is that our
instrumentation patch includes that particular function, which does
accept a filename (provided it's under $PGDATA or the log directory).
Dbfile was meant to indicate it's a database related file, rather than
some arbitrary filename.

/D

On Monday 04 July 2005 13:25, Bruce Momjian wrote:

Robert Treat wrote:

Actually I'd agree with Tom, pg_dbfile_size is ugly, and suggest to me I
could use a filename as an argument. ISTM that if we think that
functions like pg_database_size and pg_tablespace_size all make sense,
the natural extension would be functions called pg_index_size to tell us
the size of an index, pg_table_size to tell us the size of a table
(table+toast) without it's indexes, and some form of
pg_table_plus_indexes_size for a table and its indexes for those that
feel we need both. I'm not sold we need a function that can return
either an index or table size, but if so something like pg_object_size
seems ambigious enough to work, and is future proof enough to handle
things like materialized views when and if they arise.

You are into the cycle we were in. We discussed pg_object size (too
vague) and pg_index_size (needs pg_toast_size too, and maybe toast
indexes; too many functions).

Yeah, I read those discussions, and think you were better off then than you
are now, which is why I went back to it somewhat.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

You are into the cycle we were in. We discussed pg_object size (too
vague) and pg_index_size (needs pg_toast_size too, and maybe toast
indexes; too many functions).

Yeah, I read those discussions, and think you were better off then than you
are now, which is why I went back to it somewhat.

To be honest, the amount of effort being expended on this naming
discussion far outweighs the benefits. Maybe it's time for a core
member to step in and just resolve it - one way or the other?

Chris

-----Original Message-----
From: Christopher Kings-Lynne [mailto:chriskl@familyhealth.com.au]
Sent: 05 July 2005 02:39
To: Robert Treat
Cc: Bruce Momjian; Dave Page; Tom Lane; Dawid Kuroczko;
Andreas Pflug; PostgreSQL-patches; PostgreSQL-development
Subject: Re: [HACKERS] [PATCHES] Dbsize backend integration

You are into the cycle we were in. We discussed pg_object size (too
vague) and pg_index_size (needs pg_toast_size too, and maybe toast
indexes; too many functions).

Yeah, I read those discussions, and think you were better

off then than you

are now, which is why I went back to it somewhat.

To be honest, the amount of effort being expended on this naming
discussion far outweighs the benefits. Maybe it's time for a core
member to step in and just resolve it - one way or the other?

Agreed. The current names were discussed (at some length!) by Bruce & I
before I reworked the latest version of the patch. Can we just settle on
that?

Regards, Dave.

Dave Page wrote:

You are into the cycle we were in. We discussed pg_object size (too
vague) and pg_index_size (needs pg_toast_size too, and maybe toast
indexes; too many functions).

Yeah, I read those discussions, and think you were better

off then than you

are now, which is why I went back to it somewhat.

To be honest, the amount of effort being expended on this naming
discussion far outweighs the benefits. Maybe it's time for a core
member to step in and just resolve it - one way or the other?

Agreed. The current names were discussed (at some length!) by Bruce & I
before I reworked the latest version of the patch. Can we just settle on
that?

If we go pg_table_size() and pg_relation_size(), which is object-only
and which is heap + index + toast? I think ideally we want
pg_relation_size to be the combined one, but then we have pg_table_size
that works on indexes and toast too, and that is confusing, and we don't
want to add index and toast versions. Or is an index a relation? And
TOAST?

OK, how about pg_relation_size for heap/index/toast, and
pg_complete_relation_size for the combined total.

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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

If we go pg_table_size() and pg_relation_size(), which is object-only
and which is heap + index + toast? I think ideally we want
pg_relation_size to be the combined one, but then we have pg_table_size
that works on indexes and toast too, and that is confusing, and we don't
want to add index and toast versions. Or is an index a relation? And
TOAST?

All the backend code thinks so --- anything that has an entry in
pg_class is a relation. So personally I don't find "table" and
"relation" confusing in this context. But I can see it might be
confusing to people not familiar with PG jargon.

OK, how about pg_relation_size for heap/index/toast, and
pg_complete_relation_size for the combined total.

I could live with that. Or "pg_total_relation_size".

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

If we go pg_table_size() and pg_relation_size(), which is object-only
and which is heap + index + toast? I think ideally we want
pg_relation_size to be the combined one, but then we have pg_table_size
that works on indexes and toast too, and that is confusing, and we don't
want to add index and toast versions. Or is an index a relation? And
TOAST?

All the backend code thinks so --- anything that has an entry in
pg_class is a relation. So personally I don't find "table" and
"relation" confusing in this context. But I can see it might be
confusing to people not familiar with PG jargon.

OK, how about pg_relation_size for heap/index/toast, and
pg_complete_relation_size for the combined total.

I could live with that. Or "pg_total_relation_size".

The problem with "total", to me, is that it already is the total size of
the heap/index/toast. Complete has the idea of adding additional
pieces, which I think fits best.

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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

I could live with that. Or "pg_total_relation_size".

The problem with "total", to me, is that it already is the total size of
the heap/index/toast. Complete has the idea of adding additional
pieces, which I think fits best.

[ shrug ] I don't care --- if you do, then do that.

I finally realized exactly what was bugging me about "dbfile_size": it
seems to imply that we are measuring the size of one *file*, which is
under no circumstance the definition of any of these functions (see
file splitting behavior for relations exceeding 1GB).

pg_relation_size plus pg_complete_relation_size is fine. Ship it...

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

I could live with that. Or "pg_total_relation_size".

The problem with "total", to me, is that it already is the total size of
the heap/index/toast. Complete has the idea of adding additional
pieces, which I think fits best.

[ shrug ] I don't care --- if you do, then do that.

I finally realized exactly what was bugging me about "dbfile_size": it
seems to imply that we are measuring the size of one *file*, which is
under no circumstance the definition of any of these functions (see
file splitting behavior for relations exceeding 1GB).

Yes, that is an issue I considered. I was more relying on the _idea_
that people thought it was a single file, but that is an implementation
detail that shouldn't be promoted.

pg_relation_size plus pg_complete_relation_size is fine. Ship it...

OK.

-- 
  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: 06 July 2005 04:11
To: Tom Lane
Cc: Dave Page; Christopher Kings-Lynne; Robert Treat; Dawid
Kuroczko; Andreas Pflug; PostgreSQL-patches; PostgreSQL-development
Subject: Re: [HACKERS] [PATCHES] Dbsize backend integration

Tom Lane wrote:

pg_relation_size plus pg_complete_relation_size is fine. Ship it...

OK.

Updated version attached.

Regards, Dave.

Patch applied. Thanks. /contrib/dbsize removed. New functions:

pg_tablespace_size
pg_database_size
pg_relation_size
pg_complete_relation_size
pg_size_pretty

---------------------------------------------------------------------------

Dave Page wrote:

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 06 July 2005 04:11
To: Tom Lane
Cc: Dave Page; Christopher Kings-Lynne; Robert Treat; Dawid
Kuroczko; Andreas Pflug; PostgreSQL-patches; PostgreSQL-development
Subject: Re: [HACKERS] [PATCHES] Dbsize backend integration

Tom Lane wrote:

pg_relation_size plus pg_complete_relation_size is fine. Ship it...

OK.

Updated version attached.

Regards, Dave.

Content-Description: dbsize.c

[ Attachment, skipping... ]

Content-Description: dbsize.patch

[ Attachment, skipping... ]

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