Hi all,

  In looking over the TODO doc, I saw

* Add table name mapping for numeric file names

  After reading the thread that bore that entry, I'd like to 
submit a bit of code for consideration, since it solves at least one 
of the problems raised in that thread.  You may fetch it from cvs;
 
 sh$ cvs -d :pserver:anoncvs@rcfile.org:/var/cvs login
    [no password, just press enter]
 sh$ cvs -d :pserver:anoncvs@rcfile.org:/var/cvs co pg_stat_du
 sh$ echo "read the README"

It's been tested with 7.1 and 7.2bN and is basically a few c functions
that allow (approximate) reports of disk usage (in Kb) per database, 
and  per relation (only on the current database, for now).

brent=# SELECT datname, pg_du_db(datname) AS diskusage FROM pg_database;
  datname  | diskusage 
-----------+-----------
 brent     |      1785
 template1 |      1713
 template0 |      1713
(3 rows)

brent=# select relname,pg_du_rel(relname) as diskusage from pg_class        
brent-# where (relkind='r' or relkind='i') order by diskusage desc;
             relname             | diskusage 
---------------------------------+-----------
 pg_proc                         |       252
 pg_proc_proname_narg_type_index |       236
 pg_description                  |       100
 pg_attribute                    |        92
 pg_operator                     |        84
...


  If you guys could suggest what needs to be added/modified to allow
Bruce to mark off that TODO item, I can probably get that done later
today.

cheers.
  brent

-- 
"Develop your talent, man, and leave the world something. Records are 
really gifts from people. To think that an artist would love you enough
to share his music with anyone is a beautiful thing."  -- Duane Allman

Sun, 18 Nov 2001 07:36:04 -0500
Brent Verner <brent@rcfile.org>
TODO item inquiry/claim
Brent Verner writes:

> * Add table name mapping for numeric file names

> It's been tested with 7.1 and 7.2bN and is basically a few c functions
> that allow (approximate) reports of disk usage (in Kb) per database,
> and  per relation (only on the current database, for now).

I've written on like that a while ago:

http://webmail.postgresql.org/~petere/dbsize.html

The tarball can be rolled into contrib -- now that I think of it I don't
know why I never did that.

Never imagined this would have anything to do with that TODO item, though.
I figured oid2name accomplished that.

-- 
Peter Eisentraut   peter_e@gmx.net


Sun, 18 Nov 2001 18:17:24 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: TODO item inquiry/claim
On 18 Nov 2001 at 18:17 (+0100), Peter Eisentraut wrote:
| Brent Verner writes:
| 
| > * Add table name mapping for numeric file names
| 
| > It's been tested with 7.1 and 7.2bN and is basically a few c functions
| > that allow (approximate) reports of disk usage (in Kb) per database,
| > and  per relation (only on the current database, for now).
| 
| I've written on like that a while ago:
| 
| http://webmail.postgresql.org/~petere/dbsize.html
| 
| The tarball can be rolled into contrib -- now that I think of it I don't
| know why I never did that.

  Can you put your code in contrib?  I've seen atleast a few other 
users wanting to measure disk use.

| Never imagined this would have anything to do with that TODO item, though.
| I figured oid2name accomplished that.

  Yeah, but it address one inconvenience caused by the oid-named files.
Even after reading through the thread[1], I'm not sure what the desired
solution to the problem is.  There seem to be two reasons to know
the filename->(db|rel) mapping.

  1) resource usage [solved]
  2) data recovery from db failure.

  Are there any other reasons an admin needs to know the actual
filenames the system is using?  Please reply if you have any.

  I really have no idea what all would be involved in recovering a
database (or a single relation, if possible), so I would appreciate
suggestions on what needs to be done to address this issue WRT the
filename->(db|rel) mappings.  There was not a concensus on what the
proper solution would look like; some suggested maintaining symlinks,
other suggested a separate file of mappings.  If someone would tell
me what a reliable solution would be, I'll work at implementing it
for inclusion in one of the 7.2.X maintenance releases.

cheers.
  brent

[1] http://fts.postgresql.org/db/mw/msg.html?mid=114680

-- 
"Develop your talent, man, and leave the world something. Records are 
really gifts from people. To think that an artist would love you enough
to share his music with anyone is a beautiful thing."  -- Duane Allman

Sun, 18 Nov 2001 18:25:34 -0500
Brent Verner <brent@rcfile.org>
Re: TODO item inquiry/claim
Brent Verner wrote:
> On 18 Nov 2001 at 18:17 (+0100), Peter Eisentraut wrote:
> | Brent Verner writes:
> | 
> | > * Add table name mapping for numeric file names
> | 
> | > It's been tested with 7.1 and 7.2bN and is basically a few c functions
> | > that allow (approximate) reports of disk usage (in Kb) per database,
> | > and  per relation (only on the current database, for now).
> | 
> | I've written on like that a while ago:
> | 
> | http://webmail.postgresql.org/~petere/dbsize.html
> | 
> | The tarball can be rolled into contrib -- now that I think of it I don't
> | know why I never did that.
> 
>   Can you put your code in contrib?  I've seen atleast a few other 
> users wanting to measure disk use.

Done.

> | Never imagined this would have anything to do with that TODO item, though.
> | I figured oid2name accomplished that.
> 
>   Yeah, but it address one inconvenience caused by the oid-named files.
> Even after reading through the thread[1], I'm not sure what the desired
> solution to the problem is.  There seem to be two reasons to know
> the filename->(db|rel) mapping.
> 
>   1) resource usage [solved]

Yes.

>   2) data recovery from db failure.
> 
>   Are there any other reasons an admin needs to know the actual
> filenames the system is using?  Please reply if you have any.
> 
>   I really have no idea what all would be involved in recovering a
> database (or a single relation, if possible), so I would appreciate
> suggestions on what needs to be done to address this issue WRT the
> filename->(db|rel) mappings.  There was not a concensus on what the
> proper solution would look like; some suggested maintaining symlinks,
> other suggested a separate file of mappings.  If someone would tell
> me what a reliable solution would be, I'll work at implementing it
> for inclusion in one of the 7.2.X maintenance releases.

I wish I knew the answer.  :-)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 22 Feb 2002 18:06:12 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: TODO item inquiry/claim