Re: [HACKERS] [PATCHES] Dbsize backend integration
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:[EMAIL PROTECTED] > > 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Dbsize backend integration
> -Original Message- > From: Bruce Momjian [mailto:[EMAIL PROTECTED] > 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. dbsize.c Description: dbsize.c dbsize.patch Description: dbsize.patch ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Dbsize backend integration
Tom Lane wrote: > Bruce Momjian 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] Dbsize backend integration
Bruce Momjian 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Dbsize backend integration
Tom Lane wrote: > Bruce Momjian 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] [PATCHES] Dbsize backend integration
Bruce Momjian 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Dbsize backend integration
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Dbsize backend integration
> -Original Message- > From: Christopher Kings-Lynne [mailto:[EMAIL PROTECTED] > 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. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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? Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] Dbsize backend integration
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Dbsize backend integration
> -Original Message- > From: Robert Treat [mailto:[EMAIL PROTECTED] > 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] Dbsize backend integration
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Dbsize backend integration
On Monday 04 July 2005 10:11, Dave Page wrote: > > -Original Message- > > From: Tom Lane [mailto:[EMAIL PROTECTED] > > 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" 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Dbsize backend integration
> -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > 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" 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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Dbsize backend integration
Bruce Momjian wrote: > Tom Lane wrote: > > "Dave Page" 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Dbsize backend integration
Tom Lane wrote: > "Dave Page" 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Dbsize backend integration
"Dave Page" 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Dbsize backend integration
> -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > 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 <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Dbsize backend integration
Dawid Kuroczko <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Dbsize backend integration
On 7/3/05, Andreas Pflug <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Dbsize backend integration
Bruce Momjian wrote: Andreas Pflug wrote: Dave Page wrote: -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] Dbsize backend integration
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] Dbsize backend integration
Andreas Pflug wrote: > Dave Page wrote: > > > > > > > >>-Original Message- > >>From: Bruce Momjian [mailto:[EMAIL PROTECTED] > >>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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Dbsize backend integration
Dave Page wrote: -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Dbsize backend integration
> -Original Message- > From: Bruce Momjian [mailto:[EMAIL PROTECTED] > 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. dbsize.c Description: dbsize.c dbsize.patch Description: dbsize.patch ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Dbsize backend integration
Is a new version of this patch coming? --- Bruce Momjian wrote: > Dave Page wrote: > > > > > > > > -Original Message- From: Bruce Momjian > > [mailto:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] [PATCHES] Dbsize backend integration
> -Original Message- > From: Dawid Kuroczko [mailto:[EMAIL PROTECTED] > 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 wrote: > > > -Original Message- > > > From: Bruce Momjian [mailto:[EMAIL PROTECTED] > > > 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Dbsize backend integration
Bruce Momjian 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Dbsize backend integration
[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Dbsize backend integration
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Dbsize backend integration
Dawid Kuroczko wrote: On 6/30/05, Dave Page wrote: -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] 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/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Dbsize backend integration
On 6/30/05, Dave Page wrote: > > -Original Message- > > From: Bruce Momjian [mailto:[EMAIL PROTECTED] > > 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... ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] Dbsize backend integration
"Dave Page" 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Dbsize backend integration
> -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: 30 June 2005 14:41 > To: Dave Page > Cc: Michael Glaesemann; PostgreSQL-patches; PostgreSQL-development > Subject: Re: [PATCHES] Dbsize backend integration > > "Dave Page" 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. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] Dbsize backend integration
"Dave Page" 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] Dbsize backend integration
> -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] [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? :-) Maybe pg_trait_size() or pg_property_size() will do? -- Victor Msg sent via @Mail ISP MiTS - http://www.mits.lv/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Dbsize backend integration
> -Original Message- > From: Michael Glaesemann [mailto:[EMAIL PROTECTED] > 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Dbsize backend integration
On Jun 30, 2005, at 5:48 PM, Dave Page wrote: -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: 29 June 2005 12:46 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Dbsize backend integration
> -Original Message- > From: Bruce Momjian [mailto:[EMAIL PROTECTED] > 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. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Dbsize backend integration
Andreas Pflug <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Dbsize backend integration
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Dbsize backend integration
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] Dbsize backend integration
Dave Page wrote: > > > > -Original Message- From: Bruce Momjian > [mailto:[EMAIL PROTECTED] 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 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Dbsize backend integration
-Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Dbsize backend integration
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match