pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Thomas Lockhart <lockhart@alumni.caltech.edu>
To: Peter Eisentraut <peter_e@gmx.net>
Cc: Richard J Kuhns <rjk@grauel.com>
Cc: pgsql-general@postgresql.org, PostgreSQL Development <pgsql-hackers@postgresql.org>
Subject: Re: Question about databases in alternate locations...
Date: Fri, 19 May 2000 13:43:45 +0000
Message-ID: <39254511.5447BBA9@alumni.caltech.edu> (raw)
References: <Pine.LNX.4.21.0005181422410.349-100000@localhost.localdomain>

> > Peter E (if I recall right) was proposing some changes to remove the
> > environment variable capabilities in Postgres. He also proposed making
> > a *list* of allowed locations as an environment variable as a way of
> > managing or controlling the allowed locations.
> That was an interesting line of thought until the system catalog idea came
> up. I believe everyone would agree that keeping things system catalog
> controlled is the generally preferred choice. If you create a system
> catalog pg_location(locname name, locpath text) then you still have in
> fact a list of allowed locations, but one that can be changed while the
> server is up, that can be queried, that can easily be joined against
> pg_database, etc. Heck, finely grained permissions are the next logical
> step.

So pg_location would hold the full path (absolute or logical) to every
file resource in every database? Or would it hold only a list of
allowed paths? Or only a list of resources for each database (~1 row
per database) and then table-specific info would be stored somewhere
local to the database itself?

> Table spaces are another point of consideration. Surely you would
> eventually want table space administration to be via query language
> commands. In essence, the alternative locations are a table space kind of
> thingy. The only difference is that the granularity of control stops at
> the database level, but that's only a difference of degree, not kind. In
> fact, if someone comes around to reworking the logical->physical relation
> name mapping then you could add a field pg_class.rellocation and voilĂ ,
> there's your table spaces.

Yes, this capability will be great.
  ALTER TABLE SET LOCATION=...
and/or
  ALTER DATABASE SET LOCATION=...
should help administration and scalability.

> So all in all I do like the system catalog driven model much better in
> terms of ease of use, functionality, extensibility, everything. And no,
> there's no chicken-and-egg problem because the relation name mapping for
> shared system relations would presumably not be changed. (How would that
> work anyway?)
> > Putting all of this stuff in a table is a possibility, but
> > 1) Ingres did this, but they had way too many tables involved in
> > defining and using tables imho. We should do better.
> Well, so far we'd have one table. Is there any reason why we would need
> more? Why did they have so many? I don't mind many tables if they give
> more functionality.

I have no idea why they had so many. Probably because it grew
incrementally, or possibly because they normalized their tables to the
theoretically correct point. It was ugly either way (right Bruce?).

> > 2) If a dbadmin wants to *carefully* move database locations around,
> > the environment variables allow this to happen by just shutting down
> > the backend, tarring/untarring a disk area, redefining the environment
> > variable, and restarting the backend.
> 1. shut down database
> 2. move data area
> 3. connect to template1
> 4. update pg_location
> 5. connect to the moved database
> That's not very different.

But hard to do? If pg_location has 5000 entries, and you've scattered
tables all over the place (perhaps a bad decision, but we *should*
have the flexibility to do that) then it might be very error prone
when working with absolute paths imho.

> > 3) We don't (yet) have a way to move tables from within Postgres. So
> > hardcoding or "hard storing" absolute paths would make it pretty
> > difficult to accomplish (2).
> I don't know what you mean with "hard storing".

Putting absolute path names as pointers to tables or data areas. I'm
getting the sense I'm in a minority (in a group of 3? ;) in this
discussion, but imho having some decoupling between logical paths in
the database and actual paths outside is A Good Thing. Always has been
a mark of good design in my experience.

                          - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California



view thread (10+ messages)  latest in thread

Message-ID: <39254511.5447BBA9@alumni.caltech.edu>
Permalink:  ../39254511.5447BBA9@alumni.caltech.edu/
Also on:    postgresql.org/message-id/39254511.5447BBA9@alumni.caltech.edu

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: lockhart@alumni.caltech.edu, peter_e@gmx.net, rjk@grauel.com, pgsql-hackers@postgresql.org
  Subject: Re: Question about databases in alternate locations...
  In-Reply-To: <39254511.5447BBA9@alumni.caltech.edu>
  References: <Pine.LNX.4.21.0005181422410.349-100000@localhost.localdomain> <39254511.5447BBA9@alumni.caltech.edu>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox