Re: Physical Database Configuration

On Wed, 2003-06-25 at 10:51, Jonathan Bartlett wrote:
> My solution did not involve tablespaces, but was more of a quick solution
> to make it easier for admins to do _some_ sort of physical configuration.
>
> The idea is that the developer could do something like
>
> 'create alternate location ALTERNATE_LOCATION_NAME for
> DATABASE_OBJECT_NAME at "/PATH/TO/PHYSICAL/FILE";'
>
> We would have a system table holding theses values. Then, all database
> commands which create a file for an object, call open_object(oid,
> object_name) or something to create the file object. This will first look
> in the new system table to see if there is a mapping for an object of this
> name. If so, it will create a symlink to "/PATH/TO/PHYSICAL/FILE" for the
> oid before opening the file.
>
> Anyway, if people are working on tablespaces, I'll defer to them. This
> small fix is something that I might actually have time to do, but
> tablespaces definitely not.

IMHO, this is all that's *really* necessary. That, and an ALTER
that (takes an exclusive lock on the relevant objects and) lets the
DBA move ALTERNATE_LOCATION_NAME from "/PATH/TO/PHYSICAL/FILE" to
"/NEW/PATH/TO/PHYSICAL/FILE".

On question, though. I've noticed that once a file grows beyond
1GB, PostgreSQL creates a new file and starts appending to a new
file. Presumably, that same mode of operation would still occur.

> On Wed, 25 Jun 2003 nolan(at)celery(dot)tssi(dot)com wrote:
>
> > > Well, correct solution is to implement tablespaces on which objects like
> > > databases, tables and indexes can be put.
> >
> > I've not looked at the SQL standard, but it seems to me like the order
> > should be:
> >
> > Databases
> > Tablespaces
> > Schemas
> > Objects (tables, indexes, functions, etc.)
> >
> > And it really isn't hierarchical. As I understand them (based on my
> > Oracle background), tablespaces, unlike schemas, do NOT create a layer
> > of data abstraction. That is to say, while the same table name
> > can exist in multiple schemas, only one instance of a given table name
> > within a given schema can exist, regardless of what tablespace it is in.
> >
> > That makes the tablespace a property of an object.
> >
> > Whether or not two databases can share tablespaces isn't clear to me,
> > though as a DBA I can think of good reasons why they probably shouldn't
> > do so, I'm not sure if that is an absolute.
> >
> > > I have no idea what is the status of that effort right now. You can search the
> > > archives or I hope this kicks a fresh discussion..:-)
> >
> > I'm game, though I'm also not ready to lead such a project, probably not
> > even the discussion on it.
> > --
> > Mike Nolan

--
+-----------------------------------------------------------+
| Ron Johnson, Jr. Home: ron(dot)l(dot)johnson(at)cox(dot)net |
| Jefferson, LA USA http://members.cox.net/ron.l.johnson |
| |
| "Oh, great altar of passive entertainment, bestow upon me |
| thy discordant images at such speed as to render linear |
| thought impossible" (Calvin, regarding TV) |
+-----------------------------------------------------------