Now that we have numeric file names, I would like to have a command I
can run from psql that will dump a mapping of numeric file name to table
name, i.e.,

	121233	pg_proc
	143423	pg_index


With that feature, I can write scripts pgfile2name and pgname2file that
map file names to table names.  People can run standard Unix commands
and have meaningful display output:

	ls -l | pgfile2name

changes:

	-rwx------  4 postgres  postgres   512 Oct 27 10:52 198323

to:

	-rwx------  4 postgres  postgres   512 Oct 27 10:52 pg_class

The only missing piece would be to identify files on a backup tape.  Not
sure how to handle that unless a map file already exists on the tape.

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

Sat, 28 Oct 2000 16:52:31 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Numeric file names
Bruce Momjian writes:

> Now that we have numeric file names, I would like to have a command I
> can run from psql that will dump a mapping of numeric file name to table
> name, i.e.,
> 
> 	121233	pg_proc
> 	143423	pg_index

select oid, relname from pg_class;

> With that feature, I can write scripts pgfile2name and pgname2file that
> map file names to table names.  People can run standard Unix commands
> and have meaningful display output:
> 
> 	ls -l | pgfile2name

sed `psql -Aqt -d ${database} -c "select'-e s/' || oid || '/' || relname || '/g' from pg_class"`


What I'd find useful is a program that you can occasionally run on a
database directory that creates links from "name" to "oid".

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/


Sat, 28 Oct 2000 23:58:10 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Numeric file names
> Bruce Momjian writes:
> 
> > Now that we have numeric file names, I would like to have a command I
> > can run from psql that will dump a mapping of numeric file name to table
> > name, i.e.,
> > 
> > 	121233	pg_proc
> > 	143423	pg_index
> 
> select oid, relname from pg_class;

Oh, we went with oid-based file names.  OK.

> > With that feature, I can write scripts pgfile2name and pgname2file that
> > map file names to table names.  People can run standard Unix commands
> > and have meaningful display output:
> > 
> > 	ls -l | pgfile2name
> 
> sed `psql -Aqt -d ${database} -c "select'-e s/' || oid || '/' || relname || '/g' from pg_class"`
> 
> 
> What I'd find useful is a program that you can occasionally run on a
> database directory that creates links from "name" to "oid".

Yes, that too.  You can then do ls -L on the symlinks to see the
underlying sizes.

My utilities are more generic.  Also, they will allow programs like
fstat/lsof to show meaningful output, though it may be tough to guess
the database from the fstat output.  lsof prints the full path, so that
is OK.  The script will guess the database from the path name.

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

Sat, 28 Oct 2000 18:07:53 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Numeric file names
> > With that feature, I can write scripts pgfile2name and pgname2file that
> > map file names to table names.  People can run standard Unix commands
> > and have meaningful display output:
> > 
> > 	ls -l | pgfile2name
> 
> sed `psql -Aqt -d ${database} -c "select'-e s/' || oid || '/' || relname || '/g' from pg_class"`
> 
> 
> What I'd find useful is a program that you can occasionally run on a
> database directory that creates links from "name" to "oid".

Let me not grab this utility as my own.  If someone else wants to code
it, go ahead.  I will be in California for most of next week.

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

Sat, 28 Oct 2000 18:08:51 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Numeric file names
> > Now that we have numeric file names, I would like to have a command I
> > can run from psql that will dump a mapping of numeric file name to table
> > name, i.e.,
> >
> > 121233 pg_proc
> > 143423 pg_index
>
> select oid, relname from pg_class;

No. select relfilenode, relname from pg_class - in theory relfilenode may
differ
from relation oid.

Vadim



Sat, 28 Oct 2000 15:44:04 -0700
"Vadim Mikheev" <vmikheev@sectorbase.com>
Re: Numeric file names