Thread: Dbsize backend integration

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.

Regards, Dave.

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

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

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

"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

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

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