Thread: RFD: PostgreSQL Schema Support

I'm currently starting to implement support for some of the more desirable
features of PostgreSQL 7.3 which is now well in development. One of these
areas (which are all now on the ToDo list incidently) is Schema support.
There are a number of ways we could implement this, and I'd like to get some
feedback on what people think is right.

Note; Schemas will probably end up being called Namespaces in pgSchema
because of the obvious naming conflict (that's what Tom Lane's been calling
the related catalogues and columns in PostgreSQL itself).

1) The most basic design.
     - Add Namespaces & pgNamespace classes under pgDatabase.
     - Add a Namespace property to all objects that can live in a Namespace.
     - Allow selection of a Namespace when creating objects.

   Pros:
     - Relatively simple & easy to implement.

   Cons:
     - Very 'bolted on' design.

2) The middle of the road approach.
     - Add Namespaces & pgNamespace classes under pgDatabase.
     - *Move* classes such as Tables & Sequences etc. from pgDatabase to
pgNamespace.
     - Add a ctx.CurrentNamespace property to clsContext in pgAdmin.

   Pros:
     - The object hierarchy will be correct.

   Cons:
     - Lose backward compatibility with PostgreSQL < 7.3.

3) The whole shebang.
     - Add Namespaces & pgNamespace classes under pgDatabase.
     - *Copy* classes such as Tables & Sequences etc. from pgDatabase to
pgNamespace.
     - Add a ctx.CurrentNamespace property to clsContext in pgAdmin.
     - Recode pgAdmin to use the current object hierarchy with PostgreSQL <
7.3, otherwise the new.

   Pros:
     - Backwards compatibility is maintained.
     - The object hierarchy will be correct.

   Cons:
     - The code will be *very* complex, and probably incomprehensible to
most developers.
     -  By far the most amount of work.

Comments and other suggestions would definitely be well received with this
one :-)

Regards, Dave.

Option 2 is certainly the best long term.  A couple of releases from
now and very few people will be using 7.2 or prior -- and they should
expect newer tools to be broken .

Support namespaces in the cleanest way possible, and potentially add a
temporary hack (for the length of 7.3.x releases and maybe part of
7.4) to support 7.2 and prior with a 'pg_public' or default namespace.
ie.  Pretend a namespace exists for public stuff, as when they upgrade
to 7.3 that would be where it all ends up (I think), so it's somwhat
appropriate.

--
Rod Taylor

Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. You cannot imagine why you ever felt
otherwise.

----- Original Message -----
From: "Dave Page" <dpage@vale-housing.co.uk>
To: <pgadmin-hackers@postgresql.org>
Sent: Friday, April 05, 2002 7:40 AM
Subject: [pgadmin-hackers] RFD: PostgreSQL Schema Support


> I'm currently starting to implement support for some of the more
desirable
> features of PostgreSQL 7.3 which is now well in development. One of
these
> areas (which are all now on the ToDo list incidently) is Schema
support.
> There are a number of ways we could implement this, and I'd like to
get some
> feedback on what people think is right.
>
> Note; Schemas will probably end up being called Namespaces in
pgSchema
> because of the obvious naming conflict (that's what Tom Lane's been
calling
> the related catalogues and columns in PostgreSQL itself).
>
> 1) The most basic design.
>      - Add Namespaces & pgNamespace classes under pgDatabase.
>      - Add a Namespace property to all objects that can live in a
Namespace.
>      - Allow selection of a Namespace when creating objects.
>
>    Pros:
>      - Relatively simple & easy to implement.
>
>    Cons:
>      - Very 'bolted on' design.
>
> 2) The middle of the road approach.
>      - Add Namespaces & pgNamespace classes under pgDatabase.
>      - *Move* classes such as Tables & Sequences etc. from
pgDatabase to
> pgNamespace.
>      - Add a ctx.CurrentNamespace property to clsContext in pgAdmin.
>
>    Pros:
>      - The object hierarchy will be correct.
>
>    Cons:
>      - Lose backward compatibility with PostgreSQL < 7.3.
>
> 3) The whole shebang.
>      - Add Namespaces & pgNamespace classes under pgDatabase.
>      - *Copy* classes such as Tables & Sequences etc. from
pgDatabase to
> pgNamespace.
>      - Add a ctx.CurrentNamespace property to clsContext in pgAdmin.
>      - Recode pgAdmin to use the current object hierarchy with
PostgreSQL <
> 7.3, otherwise the new.
>
>    Pros:
>      - Backwards compatibility is maintained.
>      - The object hierarchy will be correct.
>
>    Cons:
>      - The code will be *very* complex, and probably
incomprehensible to
> most developers.
>      -  By far the most amount of work.
>
> Comments and other suggestions would definitely be well received
with this
> one :-)
>
> Regards, Dave.
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org
>

> -----Original Message-----
> From: Rod Taylor [mailto:rbt@zort.ca]
> Sent: 05 April 2002 15:34
> To: Dave Page; pgadmin-hackers@postgresql.org
> Subject: Re: [pgadmin-hackers] RFD: PostgreSQL Schema Support
>
>
> Option 2 is certainly the best long term.  A couple of
> releases from now and very few people will be using 7.2 or
> prior -- and they should expect newer tools to be broken .
>
> Support namespaces in the cleanest way possible, and
> potentially add a temporary hack (for the length of 7.3.x
> releases and maybe part of
> 7.4) to support 7.2 and prior with a 'pg_public' or default
> namespace. ie.  Pretend a namespace exists for public stuff,
> as when they upgrade to 7.3 that would be where it all ends
> up (I think), so it's somwhat appropriate.

It sounds so obvious - I'm gutted I didn't think of that myself :-).

Thanks Rod.

Dave.

BTW Rod, I forgot to mention, thanks for the Domain support in 7.3 (I assume
you're the only Rod Taylor around here). A nice feature - I'm just finishing
adding support to pgAdmin...

Note to Frank Lupo: Domains implement what the patch to pgAdmin that you
sent me did, only in PostgreSQL itself. Not particuarly obvious from the
name though :-(

Regards, Dave.

> -----Original Message-----
> From: Dave Page [mailto:dpage@vale-housing.co.uk]
> Sent: 05 April 2002 15:44
> To: 'Rod Taylor'; pgadmin-hackers@postgresql.org
> Subject: Re: [pgadmin-hackers] RFD: PostgreSQL Schema Support
>
>
>
>
> > -----Original Message-----
> > From: Rod Taylor [mailto:rbt@zort.ca]
> > Sent: 05 April 2002 15:34
> > To: Dave Page; pgadmin-hackers@postgresql.org
> > Subject: Re: [pgadmin-hackers] RFD: PostgreSQL Schema Support
> >
> >
> > Option 2 is certainly the best long term.  A couple of
> > releases from now and very few people will be using 7.2 or
> > prior -- and they should expect newer tools to be broken .
> >
> > Support namespaces in the cleanest way possible, and
> > potentially add a temporary hack (for the length of 7.3.x
> > releases and maybe part of
> > 7.4) to support 7.2 and prior with a 'pg_public' or default
> > namespace. ie.  Pretend a namespace exists for public stuff,
> > as when they upgrade to 7.3 that would be where it all ends
> > up (I think), so it's somwhat appropriate.
>
> It sounds so obvious - I'm gutted I didn't think of that myself :-).
>
> Thanks Rod.
>
> Dave.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
http://www.postgresql.org/users-lounge/docs/faq.html

At 13:40 05/04/2002 +0100, Dave Page wrote:
>I'm currently starting to implement support for some of the more desirable
>features of PostgreSQL 7.3 which is now well in development. One of these

I wish the desirable features for 7.3 included all of alter table
implemented. Do you know if it is BTW? A bit out of touch with the 'to do'
and 'Now done' lists.

>There are a number of ways we could implement this, and I'd like to get some
>feedback on what people think is right.

Tricky.
My gut feeling is get the classes / object model as close as possible to
what PostgreSQL exposes. As stated for 7.2 and before this is one thing,
for 7.3 onwards its another.

Perhaps you can freeze pgAdminII code base at some soon point, make it
known this is where support for 7.2 and earlier ends, and move the version
number up a notch and follow through with the 7.3 only route from here on
in? Perhaps for a season offer bug fixe releases only to the frozen 7.2
version, with a cut off date, roughly in keeping with the expected adoption
rate of 7.3. For us mere NT/2000 folk, perhaps also wait until 7.3 is out
on Cygwin, too before freezing all support for 7.2.

Ahhh, the wonderful world of open source.... Its like looking at the best
countryside in full clarity..... but whilst sitting on a train,
the view always keeps shifting..


Tim Finch,
FosterFinch Ltd
http://www.fosterfinch.co.uk

That would be me.

Lots more work to be done on domains yet though (foreign keys, check
constraints, etc.), but those will have to come in some other weekend.

I think you'll really enjoy my next patch (should be submitted soon).
If it goes through it'll make your life quite a bit easier -- then
again, maybe not.

--
Rod Taylor

----- Original Message -----
From: "Dave Page" <dpage@vale-housing.co.uk>
To: "'Rod Taylor'" <rbt@zort.ca>; <pgadmin-hackers@postgresql.org>
Cc: "'Frank_Lupo'" <frank_lupo@email.it>
Sent: Friday, April 05, 2002 11:27 AM
Subject: RE: [pgadmin-hackers] RFD: PostgreSQL Schema Support


> BTW Rod, I forgot to mention, thanks for the Domain support in 7.3
(I assume
> you're the only Rod Taylor around here). A nice feature - I'm just
finishing
> adding support to pgAdmin...
>
> Note to Frank Lupo: Domains implement what the patch to pgAdmin that
you
> sent me did, only in PostgreSQL itself. Not particuarly obvious from
the
> name though :-(
>
> Regards, Dave.
>
> > -----Original Message-----
> > From: Dave Page [mailto:dpage@vale-housing.co.uk]
> > Sent: 05 April 2002 15:44
> > To: 'Rod Taylor'; pgadmin-hackers@postgresql.org
> > Subject: Re: [pgadmin-hackers] RFD: PostgreSQL Schema Support
> >
> >
> >
> >
> > > -----Original Message-----
> > > From: Rod Taylor [mailto:rbt@zort.ca]
> > > Sent: 05 April 2002 15:34
> > > To: Dave Page; pgadmin-hackers@postgresql.org
> > > Subject: Re: [pgadmin-hackers] RFD: PostgreSQL Schema Support
> > >
> > >
> > > Option 2 is certainly the best long term.  A couple of
> > > releases from now and very few people will be using 7.2 or
> > > prior -- and they should expect newer tools to be broken .
> > >
> > > Support namespaces in the cleanest way possible, and
> > > potentially add a temporary hack (for the length of 7.3.x
> > > releases and maybe part of
> > > 7.4) to support 7.2 and prior with a 'pg_public' or default
> > > namespace. ie.  Pretend a namespace exists for public stuff,
> > > as when they upgrade to 7.3 that would be where it all ends
> > > up (I think), so it's somwhat appropriate.
> >
> > It sounds so obvious - I'm gutted I didn't think of that myself
:-).
> >
> > Thanks Rod.
> >
> > Dave.
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> http://www.postgresql.org/users-lounge/docs/faq.html
>

> -----Original Message-----
> From: Rod Taylor [mailto:rbt@zort.ca]
> Sent: 05 April 2002 19:06
> To: Dave Page; pgadmin-hackers@postgresql.org
> Subject: Re: [pgadmin-hackers] RFD: PostgreSQL Schema Support
>
>
> That would be me.
>
> Lots more work to be done on domains yet though (foreign
> keys, check constraints, etc.), but those will have to come
> in some other weekend.
>
> I think you'll really enjoy my next patch (should be
> submitted soon). If it goes through it'll make your life
> quite a bit easier -- then again, maybe not.

That sound ominous, what is it?

Dave.

I've been working away at pg_depend support so I can add RESTRICT and
CASCADE bits to all the objects and actually have it work.

You know.. Drop serials with drop of the table.  Restrict table
dropping if it's referenced via foreign key, won't let you drop the
int4 type because it's used in a ton of places...

Mind you, CASCADE on drop of type OID could be interesting...
--
Rod Taylor

----- Original Message -----
From: "Dave Page" <dpage@vale-housing.co.uk>
To: "'Rod Taylor'" <rbt@zort.ca>; <pgadmin-hackers@postgresql.org>
Sent: Friday, April 05, 2002 2:18 PM
Subject: RE: [pgadmin-hackers] RFD: PostgreSQL Schema Support


>
>
> > -----Original Message-----
> > From: Rod Taylor [mailto:rbt@zort.ca]
> > Sent: 05 April 2002 19:06
> > To: Dave Page; pgadmin-hackers@postgresql.org
> > Subject: Re: [pgadmin-hackers] RFD: PostgreSQL Schema Support
> >
> >
> > That would be me.
> >
> > Lots more work to be done on domains yet though (foreign
> > keys, check constraints, etc.), but those will have to come
> > in some other weekend.
> >
> > I think you'll really enjoy my next patch (should be
> > submitted soon). If it goes through it'll make your life
> > quite a bit easier -- then again, maybe not.
>
> That sound ominous, what is it?
>
> Dave.
>

> -----Original Message-----
> From: Tim Finch, FosterFinch Ltd [mailto:tim@fosterfinch.co.uk]
> Sent: 05 April 2002 17:23
> To: Dave Page
> Cc: pgadmin-hackers@postgresql.org
> Subject: Re: [pgadmin-hackers] RFD: PostgreSQL Schema Support
>
>
> At 13:40 05/04/2002 +0100, Dave Page wrote:
> >I'm currently starting to implement support for some of the more
> >desirable features of PostgreSQL 7.3 which is now well in
> development.
> >One of these
>
> I wish the desirable features for 7.3 included all of alter table
> implemented. Do you know if it is BTW? A bit out of touch
> with the 'to do'
> and 'Now done' lists.

The problem with the PostgreSQL todo list (as with most open source
projects), is that the developers pick what they want to work on, which is
not necessarily what the less experienced users want. Net result is that
releases are planned by time scale rather than when the todo list is clear.

On the plus side, I noticed that Hiroshi cleared out the DROP_COLUMN_HACK
stuff the other day, and Tom Lane (I think) did write up some ideas for
handling DROP COLUMN using historic versions of tuple types. Also (I think
it was Christopher Kings-Lynne & Tom Lane) were discussing implementing some
of the known pg_attribute hacks as real SQL the other day.

> >There are a number of ways we could implement this, and I'd
> like to get
> >some feedback on what people think is right.
>
> Tricky.
> My gut feeling is get the classes / object model as close as
> possible to
> what PostgreSQL exposes. As stated for 7.2 and before this is
> one thing,
> for 7.3 onwards its another.
>
> Perhaps you can freeze pgAdminII code base at some soon
> point, make it
> known this is where support for 7.2 and earlier ends, and
> move the version
> number up a notch and follow through with the 7.3 only route
> from here on
> in? Perhaps for a season offer bug fixe releases only to the
> frozen 7.2
> version, with a cut off date, roughly in keeping with the
> expected adoption
> rate of 7.3. For us mere NT/2000 folk, perhaps also wait
> until 7.3 is out
> on Cygwin, too before freezing all support for 7.2.

I'd rather not do this if I can help it. Periodically with pgAdmin I we had
to bump the minimum version number and I never liked doing it, not just
because of the extra support posts, but it just seemed restrictive.

Actually, I think Rod's idea is a good one. We go with option 2 & fudge a
PUBLIC schema for PostgreSQL < 7.3. Actually, this is how Tom Lane is
handling backwards compatibility in PostgreSQL - he's using a PUBLIC schema
which will be used by default. We'd just do it the other way round.

> Ahhh, the wonderful world of open source.... Its like looking
> at the best
> countryside in full clarity..... but whilst sitting on a
> train, the view always keeps shifting..

:-) That's the problem with tools like pgAdmin that are tied so tightly to a
separate project.

Regards, Dave.

> -----Original Message-----
> From: Rod Taylor [mailto:rbt@zort.ca]
> Sent: 05 April 2002 20:26
> To: Dave Page
> Cc: pgadmin-hackers@postgresql.org
> Subject: Re: [pgadmin-hackers] RFD: PostgreSQL Schema Support
>
>
> I've been working away at pg_depend support so I can add
> RESTRICT and CASCADE bits to all the objects and actually
> have it work.

Aha, you'll be Jean-Michel's best friend for a good long while when you
submit that patch I'm sure. I've knocked a number of his ideas that I wasn't
convinced we could handle the dependencies reliably enough to implement
well. Our discussions just kept coming back to 'we need to wait for a
pg_depend'.

> You know.. Drop serials with drop of the table.  Restrict
> table dropping if it's referenced via foreign key, won't let
> you drop the int4 type because it's used in a ton of places...
>
> Mind you, CASCADE on drop of type OID could be interesting...

It's almost tempting to try it!

Regards, Dave.