Horton

Thread

  1. Re: Big 7.1 open items

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-06-28T16:11:40Z

    Bruce Momjian <pgman@candle.pha.pa.us> writes:
> If we put multiple database tables in the same directory, have we
> considered how to drop databases?  Right now we do rm -rf:

rm -rf will no longer work in a tablespaces environment anyway.
(Even if you kept symlinks underneath the DB directory, rm -rf
wouldn't follow them.)

DROP DATABASE will have to be implemented honestly: run through
pg_class and do a regular DROP on each user table.

Once you've got rid of the user tables, rm -rf should suffice to
get rid of the "home tablespace" as I've been calling it, with
all the system tables therein.

Now that you mention it, this is another reason why system tables for
each database have to live in a separate tablespace directory: there's
no other good way to do that final stage of DROP DATABASE.  The
DROP-each-table approach doesn't work for system tables (somewhere along
about the point where you drop pg_attribute, DROP TABLE itself would
stop working ;-)).

However I do see a bit of a problem here: since DROP DATABASE is
ordinarily executed by a backend that's running in a different database,
how's it going to read pg_class of the target database?  Perhaps it will
be necessary to fire up a sub-backend that runs in the target DB for
long enough to kill all the user tables.  Looking messy...

			regards, tom lane

  2. Re: Big 7.1 open items

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-06-28T16:40:53Z

    > However I do see a bit of a problem here: since DROP DATABASE is
> ordinarily executed by a backend that's running in a different database,
> how's it going to read pg_class of the target database?  Perhaps it will
> be necessary to fire up a sub-backend that runs in the target DB for
> long enough to kill all the user tables.  Looking messy...

That was my feeling.  Imagine another issue.  If you see a file, how do
you figure out what database it belong to?  You would have to cycle
through the pg_class relations for every database.  Seems such reverse
lookups would not be impossible.  Not sure if it will ever be required.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

  3. RE: Big 7.1 open items

    Hiroshi Inoue <inoue@tpf.co.jp> — 2000-06-28T17:28:45Z

    > -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> 
> However I do see a bit of a problem here: since DROP DATABASE is
> ordinarily executed by a backend that's running in a different database,
> how's it going to read pg_class of the target database?  Perhaps it will
> be necessary to fire up a sub-backend that runs in the target DB for
> long enough to kill all the user tables.  Looking messy...
>

Why do we have to have system tables per *database* ?
Is there anything wrong with global system tables ?
And how about adding dbid to pg_class,pg_proc etc ?

Regards.

Hiroshi Inoue

  4. Re: Big 7.1 open items

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-06-28T17:39:32Z

    "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> Why do we have to have system tables per *database* ?
> Is there anything wrong with global system tables ?
> And how about adding dbid to pg_class,pg_proc etc ?

We could, but I think I'd vote against it on two grounds:

1. Reliability.  If something corrupts pg_class, do you want to
lose your whole installation, or just one database?

2. Increased locking overhead/loss of concurrency.  Currently, there
is very little lock contention between backends running in different
databases.  A shared pg_class will be a single point of locking (as
well as a single point of failure) for the whole installation.

It would solve the DROP DATABASE problem kind of nicely, but really
it'd just be downgrading DROP DATABASE to a DROP SCHEMA operation...

			regards, tom lane

  5. Re: Big 7.1 open items

    Hiroshi Inoue <inoue@tpf.co.jp> — 2000-06-28T23:53:03Z

    Tom Lane wrote:

> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> > Why do we have to have system tables per *database* ?
> > Is there anything wrong with global system tables ?
> > And how about adding dbid to pg_class,pg_proc etc ?
>
> We could, but I think I'd vote against it on two grounds:
>
> 1. Reliability.  If something corrupts pg_class, do you want to
> lose your whole installation, or just one database?
>
> 2. Increased locking overhead/loss of concurrency.  Currently, there
> is very little lock contention between backends running in different
> databases.  A shared pg_class will be a single point of locking (as
> well as a single point of failure) for the whole installation.

Isn't current design of PG's *database* for dropdb using "rm -rf"
rather than for above 1.2. ?
If we couldn't rely on our db itself and our locking mechanism is
poor,we could start different postmasters for different *database*s.


> It would solve the DROP DATABASE problem kind of nicely, but really
> it'd just be downgrading DROP DATABASE to a DROP SCHEMA operation...
>

What is our *DATABASE* ?
Is it clear to all people ?
At least it's a vague concept for me.
Could you please tell me what kind of objects are our *DATABASE*
objects but could not be schema objects ?

Regards.

Hiroshi Inoue

  6. Re: Big 7.1 open items

    Chris Bitmead <chris@bitmead.com> — 2000-07-01T03:59:26Z

    Tom Lane wrote:

> Now that you mention it, this is another reason why system tables for
> each database have to live in a separate tablespace directory: there's
> no other good way to do that final stage of DROP DATABASE.  The
> DROP-each-table approach doesn't work for system tables (somewhere along
> about the point where you drop pg_attribute, DROP TABLE itself would
> stop working ;-)).

If drop table is extended to drop multiple tables at once, then you read
and cache everything you need before doing all the destruction in the
second stage.