Re: Big 7.1 open items - Mailing list pgsql-hackers

> Tom Lane wrote:
> > "Oliver Elphick" <olly@lfix.co.uk> writes:
> > > I suggest that DROP TABLE in a transaction should not be allowed.
> >
> > I had actually made it do that for a short time early this year,
> > and was shouted down.  On reflection I have to agree; it's too useful
> > to be able to do
> >
> >    begin;
> >    drop table foo;
> >    create table foo(new schema);
> >    ...
> >    end;
> >
> > You do indeed lose big if you suffer an error partway through, but
> > the answer to that is to fix our file naming conventions so that we
> > can support rollback of drop table.
> 
>     Belongs  IMHO  to  the  discussion  to  keep separate what is
>     separate  (having  indices/toast-relations/etc.  in  separate
>     directories and whatnot).
> 
>     I've   never   been   really   happy  with  the  file  naming
>     conventions. The need of a filesystem entry to have the  same
>     name of the DB object that is associated with it isn't right.
>     I know, some people love to be able to  easily  identify  the
>     files with ls(1). OTOH what is that good for?

Well, I have no problem just appending some serial number to the end of
our existing names.  That solves both purposes, no?  Seems Vadim is
going to have a new storage manager in 7.2 anyway.

If/when we lose file name/object mapping, we will have to write
command-line utilities to report the mappings so people can do
administration properly.  It certainly makes it hard for administrators.

> 
>     Well,  someone  can  easily see how big the disk footprint of
>     his data is.  Whow - what an info. Anything else?
> 
>     Why not changing the naming to be something like this:
> 
>         <dbroot>/catalog_tables/pg_...
>         <dbroot>/catalog_index/pg_...
>         <dbroot>/user_tables/oid_...
>         <dbroot>/user_index/oid_...
>         <dbroot>/temp_tables/oid_...
>         <dbroot>/temp_index/oid_...
>         <dbroot>/toast_tables/oid_...
>         <dbroot>/toast_index/oid_...
>         <dbroot>/whatnot_???/...
> 
>     This way, it  would  be  much  easier  to  separate  all  the
>     different  object types to different physical media. We would
>     loose some  transparency,  but  I've  allways  wondered  what
>     people  USE  that  for  (except  for  just  wanna  know). For
>     convinience we could implement another  little  utility  that
>     tells the object size like

Yes, we could do that.

> 
>         DESCRIBE TABLE/VIEW/whatnot <object-name>
> 
>     that returns the physical location and storage details of the
>     object. And psql could use it to print this  info  additional
>     on  the  \d commands. Would give unprivileged users access to
>     this info, so be it, it's not a security issue IMHO.

You need something that works from the command line, and something that
works if PostgreSQL is not running.  How would you restore one file from
a tape.  I guess you could bring back the whole thing, then do the
query, and move the proper table file back in, but that is a pain.



--  Bruce Momjian                        |  http://www.op.net/~candle 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,
Pennsylvania19026