Re: Question about databases in alternate locations...

Thomas Lockhart writes:

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

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.

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.

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

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

All in all this might be a relatively small job for great immediate and
future benefit.

--
Peter Eisentraut Sernanders väg 10:115
peter_e(at)gmx(dot)net 75262 Uppsala
http://yi.org/peter-e/ Sweden