Re: Thanks, naming conventions, and count() - Mailing list pgsql-hackers

doesn't this defeat the reasons for going to numerics?  is there a reason
why its such a difficult thing to do a SELECT oid on pg_database and
pg_class to get this information?  that's what I've been doing when I need
to know *shrug*

On Sun, 29 Apr 2001, Bruce Momjian wrote:

> > First off I just wanted to give a big 'thank you' to all the
> > developers and contributors who have made PostgreSQL what it is
> > today. I haven't come across a single thing since my first
> > experience with it a few years ago that hasn't been corrected,
> > sped up, or otherwise postively enhanced!
> >
> > In working with 7.1 over the past couple weeks, I've noted the
> > following mods may add to usability and speed:
> >
> > o v7.1 changed the database naming convention to be all numeric;
> > I suggest having
> >    the DB engine create symbolic links when creating a new DB
> >    and subsequent tables.  For instance, in creating a database
> >    'foo' with table 'bar' the /path/to/pgsql/data/base folder
> >    will have a new folder named something like '18720'; this
> >    folder could also have a symbolic link to 'foo'. Then in the
> >    '18720' folder rather than just having numeric files for each
> >    table, pk, index, etc.   there could be symbolic links
> >    following the naming convention 'bar', 'pk_foo_pkey',
> >    'field1_foo_ukey', 'field2_foo_key'.
> >
> >    Maybe this would work best as configurable flag that could
> >    be set during compilation or in the conf file.
>
> I think this is an excellent idea, and will add it to the TODO list.  We
> agonized over moving to numeric names, and we couldn't think of a good
> way to allow administrators to know that table matched what files.  The
> big problem is that there is no good way to make the symlinks reliable
> because in a crash, the symlink could point to a table creation that got
> rolled back or the renaming of a table that got rolled back.  I think
> symlinks with some postmaster cleanup script that fixed bad symlinks
> would be great for 7,2.
>
> I have added this to the TODO list.  If someone objects, I will remove
> it:
>
>     * Add tablename symlinks for numeric file names
>
> >
> > o count() should use index scans for tables with a PK; scans
> > would be on the PK index;
> >    even after running 'vacuum analyze' such a query still uses
> >    a sequential scan. For instance, "select count(*) from bar"
> >    and even "select(pk_name) from bar" both use sequential scans.
> >    Likewise, scans on fields with indexes should use the index.
>
> The problem here is that now we don't have commit status in the index
> rows, so they have to check the heap for every row.  One idea is to
> update the index status on an index scan, and if we can do that, we can
> easily use the index.  However, the table scan is pretty quick.
>
> --
>   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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org