Re: Physical Database Configuration - Mailing list pgsql-general

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@celery.tssi.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.l.johnson@cox.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)               |
+-----------------------------------------------------------