Обсуждение: Table/Column Constraints

Hi,

I was looking at the ALTER TABLE DROP CONSTRAINT bit of PostgreSQL, and I
started thinking about trying to implement it (as a bit of mental exercise).
(And because it's highly annoying not being able to remove the damn things!

Please comment on all of this, and tell me if it's going to be over my head!

I'm just trying to understand some stuff:

* I assume that the command is supposed to allow the dropping of unique,
primary, foreign key and check constraints?  Should 'not null' constraints
also be included here?

* Unique constraints are implemented as indicies, so dropping a unique
constraint maps to dropping the relevant index.

* Primary keys are implemented...how??  I can't for the life of me find
where 'create table' occurs in the source code!

* Foreign keys are implemented as two triggers?  It seems that all that is
required is the removal of these two triggers.  I haven't checked carefully
to see _exactly_ what the triggers are doing.  I see there is one associated
with the 'one' table and one with the 'many' table.  It seems that dropping
a foreign key constraint should be a case of removing the two triggers?

* Check constraints.  I seem to recall seeing code that implements check
constraints as triggers, but I wrote a query that retrieves all triggers
associated with a particular class and no check triggers were returned.  How
are check constraints implemented?  How would you drop a check constraint?

* Not null constraints.  This seems to be a 'for completeness' constraint -
I presume it's implemented as part of the attribute definition?  I guess it
would be relatively straightforward to drop a 'not null' constraint,
assuming they are actually named in there somewhere.

Would anyone be able to correct my understanding of these issues?

Also - is there some good reason why this hasn't been implemented yet?  Is
there some subtle reason, or is it just that no-one's bothered?

Thanks,

Chris

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> * I assume that the command is supposed to allow the dropping of unique,
> primary, foreign key and check constraints?  Should 'not null' constraints
> also be included here?

Sure.

> * Unique constraints are implemented as indicies, so dropping a unique
> constraint maps to dropping the relevant index.

Or just marking the index non-unique.  Dropping it altogether might be
bad for query performance.

> * Primary keys are implemented...how??  I can't for the life of me find
> where 'create table' occurs in the source code!

Primary key == UNIQUE NOT NULL, as far as I know, and there's also a
flag somewhere in the index associated with the UNIQUE constraint.

> * Check constraints.  I seem to recall seeing code that implements check
> constraints as triggers, but I wrote a query that retrieves all triggers
> associated with a particular class and no check triggers were returned.  How
> are check constraints implemented?  How would you drop a check constraint?

No, check constraints are stored in pg_relcheck.  Don't forget to update
the count in pg_class.relchecks.

> * Not null constraints.  This seems to be a 'for completeness' constraint -
> I presume it's implemented as part of the attribute definition?

AFAIR it's just a bool in the pg_attribute row for the column.

> Also - is there some good reason why this hasn't been implemented yet?  Is
> there some subtle reason, or is it just that no-one's bothered?

I think no one's got round to it; attention has focused on DROP COLUMN,
which is a great deal harder.  If you feel like working on DROP
CONSTRAINT, go for it...
        regards, tom lane

Tom Lane wrote:
> 
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > * I assume that the command is supposed to allow the dropping of unique,
> > primary, foreign key and check constraints?  Should 'not null' constraints
> > also be included here?
> 
> Sure.
> 
> > * Unique constraints are implemented as indicies, so dropping a unique
> > constraint maps to dropping the relevant index.
> 
> Or just marking the index non-unique.  Dropping it altogether might be
> bad for query performance.

It also may break the db (make it impossible to update) if some FK
constraints are using it

> > Also - is there some good reason why this hasn't been implemented yet?  Is
> > there some subtle reason, or is it just that no-one's bothered?
> 
> I think no one's got round to it; attention has focused on DROP COLUMN,
> which is a great deal harder.  If you feel like working on DROP
> CONSTRAINT, go for it...

Dumping constraints in human-readable form (instead of CREATE CONSTRAIN
TRIGGER) would also be great.

---------
Hannu

On Mon, Nov 20, 2000 at 06:52:20PM +0200, Hannu Krosing wrote:
> 
> Dumping constraints in human-readable form (instead of CREATE CONSTRAIN
> TRIGGER) would also be great.

In fact, IMHO, this would be a great place to start: we'd all love the
fuctionality, it'd have you examining almost all the same code, and it'd
be a feature we could all test, in diverse situations. DROP CONSTRAINT
is unlikely to be as widely tested. If you can build the introspection
correctly, so that it dumps/reloads correctly for _everyone_, then I'd
trust your DROP CONSTRAINT work a lot more.

Ross
-- 
Open source code is like a natural resource, it's the result of providing
food and sunshine to programmers, and then staying out of their way.
[...] [It] is not going away because it has utility for both the developers 
and users independent of economic motivations.  Jim Flynn, Sunnyvale, Calif.

"Ross J. Reedstrom" <reedstrm@rice.edu> writes:
> On Mon, Nov 20, 2000 at 06:52:20PM +0200, Hannu Krosing wrote:
>> 
>> Dumping constraints in human-readable form (instead of CREATE CONSTRAIN
>> TRIGGER) would also be great.

> In fact, IMHO, this would be a great place to start: we'd all love the
> fuctionality, it'd have you examining almost all the same code, and it'd
> be a feature we could all test, in diverse situations. DROP CONSTRAINT
> is unlikely to be as widely tested. If you can build the introspection
> correctly, so that it dumps/reloads correctly for _everyone_, then I'd
> trust your DROP CONSTRAINT work a lot more.

Yes.  My take on this is that a lot of the constraint-related stuff,
especially foreign keys, is misdesigned: the reason it's so hard to
extract the info is that we are only storing an execution-oriented
representation.  There should be a purely declarative representation
of each constraint someplace, too, for ease of introspection.

So, my idea is that this ought to be a three-part process:

1. Redesign the representation of constraints into something more
reasonable --- at least add a declarative representation, maybe alter
or drop existing representation if it seems appropriate.

2. Adjust pg_dump to use the declarative representation rather than
trying to reconstruct things from the execution-oriented representation.
(Note this will imply that, for example, triggers generated to implement
foreign keys should NOT be dumped.  Thus, it needs to be reasonably easy
to identify such triggers --- maybe an additional flag column is needed
in pg_trigger to mark system-generated triggers.)

3. Work on ALTER ... DROP CONSTRAINT.

Christopher may now be wondering what he's got himself in for ;-).
However, steps 2 and 3 should be pretty easy if step 1 accounts for
their needs.  Don't do this in a waterfall process --- when you hit a
roadblock in 2 or 3, figure out what information you don't have, and
return to step 1 to fix it.
        regards, tom lane

> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > * I assume that the command is supposed to allow the dropping of unique,
> > primary, foreign key and check constraints?  Should 'not null'
> constraints
> > also be included here?

OK, I have just checked the SQL standard thingy for DROP CONSTRAINT, and it
seems that this is the syntax:

ALTER TABLE <table name> DROP CONSTRAINT <constraint name> <CASCADE |
RESTRICT>

I can't find out what CASCADE and RESTRICE mean?

I presume that CASCADE means that if you're trying to remove a primary key
that is referenced by some other foreign keys, all those foreign keys should
also be dropped.  However, if neither is specified, should it fail?  Or
should it produce an error?  And what on Earth does RESTRICT mean?

Also - given that the correct definition of a foreign key is that is is a
non-key attribute that refers to a primary key in another relation - would
it be really bad behaviour to _not_ drop any referring foreign keys?

> > * Unique constraints are implemented as indicies, so dropping a unique
> > constraint maps to dropping the relevant index.
> > * Not null constraints.  This seems to be a 'for completeness'
> constraint -
> > I presume it's implemented as part of the attribute definition?
>
> AFAIR it's just a bool in the pg_attribute row for the column.

My question then is - if someone adds it as a named attribute, where is its
name stored?

> > Also - is there some good reason why this hasn't been
> implemented yet?  Is
> > there some subtle reason, or is it just that no-one's bothered?
>
> I think no one's got round to it; attention has focused on DROP COLUMN,
> which is a great deal harder.  If you feel like working on DROP
> CONSTRAINT, go for it...

I have a couple of reasons for wanting to work on it and that's that I've
come from a MySQL (*gasp*) background and I've fallen in love with
Postgres's coolness.  However, I also love the admin tool 'phpMyAdmin'.
'phpPgAdmin' is the Postgres equivalent - however it lacks convenience and
many features because various sql commands aren't implemented by Postgress.
I believe that wider use of postgres would be greatly enhanced if phpPgAdmin
had all the features of phpMyAdmin - it would make it a lot easier for me to
convert people!  See, if people can't easily drop constraints (and add
constraints) then it discourages people from playing around with them, and
really learning the advanced features of postgres.

Chris

> > In fact, IMHO, this would be a great place to start: we'd all love the
> > fuctionality, it'd have you examining almost all the same code, and it'd
> > be a feature we could all test, in diverse situations. DROP CONSTRAINT
> > is unlikely to be as widely tested. If you can build the introspection
> > correctly, so that it dumps/reloads correctly for _everyone_, then I'd
> > trust your DROP CONSTRAINT work a lot more.

Just to catch up here - does this mean that pg_dump has issues with
correctly recreating the contraints?  If you tell me exactly what the
problem is - I'll give it a burl.  However, a reimplementation of
constraints would probably be beyond my  knowledge atm.

> Yes.  My take on this is that a lot of the constraint-related stuff,
> especially foreign keys, is misdesigned: the reason it's so hard to
> extract the info is that we are only storing an execution-oriented
> representation.  There should be a purely declarative representation
> of each constraint someplace, too, for ease of introspection.

By this, do you mean that the existence of a foreign key is implied rather
than explicit by the existence of various triggers, etc.?

> So, my idea is that this ought to be a three-part process:
>
> 1. Redesign the representation of constraints into something more
> reasonable --- at least add a declarative representation, maybe alter
> or drop existing representation if it seems appropriate.

Problem is that there are 5 difference types of constraints, implemented in
5 different ways.  Do you want a unifed, central catalog of constraints, or
just for some of them, or what?

Maybe it could be done like this (given my limited knowledge...)

a. Create a system catalog that names all contraints associated with tables.
I assume that column contraints implicitly become table constraints.  This
will also make it easy to have global unique contraint names.  Actually -
are the constraint  names currently unique for an entire database?

b. In all the places where the constraints are implemented.  (ie.
pg_relcheck, indicies and pg_trigger add a column that flags the entry as
being a 'system constraint'.

That way finding and dropping constraints should be ok, so long as
everything is kept consistent!

> 2. Adjust pg_dump to use the declarative representation rather than
> trying to reconstruct things from the execution-oriented representation.
> (Note this will imply that, for example, triggers generated to implement
> foreign keys should NOT be dumped.  Thus, it needs to be reasonably easy
> to identify such triggers --- maybe an additional flag column is needed
> in pg_trigger to mark system-generated triggers.)

This would be straightforward, given the implementation of (1).

It would be nice, however, if pg_dump produced the exact same sql as used to
create a table.  For instance, if you specify a column constraint, it comes
back as a column constraint, rather than a trigger, or a table constraint.
This would especially aid portability of the dumped SQL.

> 3. Work on ALTER ... DROP CONSTRAINT.

Again, this should be straightforward given (1).

> Christopher may now be wondering what he's got himself in for ;-).

There's no better way to learn databases than to code for one I think!

Any comments?

Chris

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> Just to catch up here - does this mean that pg_dump has issues with
> correctly recreating the contraints?

Well, if you examine the pg_dump output, it doesn't really try ---
you'll see no sign of any foreign-key constraint declarations in
a pg_dump script, for example, only trigger declarations.  This is
correct as far as reproducing the working database goes, but it's
bad news for making a readable/modifiable dump script.  What's worse,
this representation ties us down over version updates: we cannot easily
change the internal representation of constraints, because the internal
representation is what's getting dumped.  Loading an old dump file into
a new version with a different constraint implementation would not
work as desired.  (This may mean that we can't change it, which would
*really* be a problem...)

>> There should be a purely declarative representation
>> of each constraint someplace, too, for ease of introspection.

> By this, do you mean that the existence of a foreign key is implied rather
> than explicit by the existence of various triggers, etc.?

Exactly.

>> 1. Redesign the representation of constraints into something more
>> reasonable --- at least add a declarative representation, maybe alter
>> or drop existing representation if it seems appropriate.

> Problem is that there are 5 difference types of constraints, implemented in
> 5 different ways.  Do you want a unifed, central catalog of constraints, or
> just for some of them, or what?

Dunno.  Maybe a unified representation would make more sense, or maybe
it's OK to treat them separately.  The existing implementations of the
different types of constraints were done at different times, and perhaps
are different "just because" rather than for any good reason.  We need
investigation before we can come up with a reasonable proposal.

> I assume that column contraints implicitly become table constraints.  This
> will also make it easy to have global unique contraint names.  Actually -
> are the constraint  names currently unique for an entire database?

No, and they shouldn't be --- only per-table, I think.

> It would be nice, however, if pg_dump produced the exact same sql as used to
> create a table.  For instance, if you specify a column constraint, it comes
> back as a column constraint, rather than a trigger, or a table constraint.
> This would especially aid portability of the dumped SQL.

Right, exactly my point above.  We discard too much information that
needs to be retained somewhere...
        regards, tom lane

Tom Lane wrote:

> > It would be nice, however, if pg_dump produced the exact same sql as used to
> > create a table.  For instance, if you specify a column constraint, it comes
> > back as a column constraint, rather than a trigger, or a table constraint.
> > This would especially aid portability of the dumped SQL.
> 
> Right, exactly my point above.  We discard too much information that
> needs to be retained somewhere...

I like this conversation as not a day goes by where I don't wish I could
edit the dump of a database rather than keeping structure entirely
seperate -- and actually do so in a useful manner.  That said, whats the
possibility of maintaining comments if the SQL dumps actually became
humanly editable?

> I like this conversation as not a day goes by where I don't wish I could
> edit the dump of a database rather than keeping structure entirely
> seperate -- and actually do so in a useful manner.  That said, whats the
> possibility of maintaining comments if the SQL dumps actually became
> humanly editable?

From reading the pg_dump source code, pg_dump creates a set of 'COMMENT ON
...' statements that should recreate all the comments associated with an
oid.  So - there shouldn't be a problem, should there?

Chris

> > Problem is that there are 5 difference types of constraints,
> implemented in
> > 5 different ways.  Do you want a unifed, central catalog of
> constraints, or
> > just for some of them, or what?
>
> Dunno.  Maybe a unified representation would make more sense, or maybe
> it's OK to treat them separately.  The existing implementations of the
> different types of constraints were done at different times, and perhaps
> are different "just because" rather than for any good reason.  We need
> investigation before we can come up with a reasonable proposal.

It strikes me that having a catalog (so to speak) of all contraints, with
flags in the tables where the contraints are implemented would allow a
separation of presentation and implementation.

For example, say, if a catalog existed that clients could query to discover
all constraint information, then it would be possible to change how foreign
keys are implemented, and not affect how this info is presented.

However, if users still had to perform joins between some centralised table,
and the tables where the constraints are actually kept (relcheck, trigger,
etc) then that defeats the purpose.  Say - isn't that what 'views' are for?

> > I assume that column contraints implicitly become table
> constraints.  This
> > will also make it easy to have global unique contraint names.
> Actually -
> > are the constraint  names currently unique for an entire database?
>
> No, and they shouldn't be --- only per-table, I think.

Oops - correct.  Wasn't paying attention.  I forgot that the table name is
specified as part of the ALTER statement.

Chris

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> For example, say, if a catalog existed that clients could query to discover
> all constraint information, then it would be possible to change how foreign
> keys are implemented, and not affect how this info is presented.

> However, if users still had to perform joins between some centralised table,
> and the tables where the constraints are actually kept (relcheck, trigger,
> etc) then that defeats the purpose.  Say - isn't that what 'views' are for?

A join as such doesn't bother me.  For example, it'd be proper for this
hypothetical constraint catalog to have a column of table OIDs, which
you'd have to join against pg_class to get the table name from.  The
real issue is to make sure that we store enough info so that the
original table/constraint declarations can be reconstructed in a
straightforward fashion.

Peter has remarked that the SQL spec offers a set of system views
intended to provide exactly this info.  That should be looked at;
if there's a workable standard for this stuff, we oughta follow it.
        regards, tom lane

> "Ross J. Reedstrom" <reedstrm@rice.edu> writes:
> > On Mon, Nov 20, 2000 at 06:52:20PM +0200, Hannu Krosing wrote:
> >> 
> >> Dumping constraints in human-readable form (instead of CREATE CONSTRAIN
> >> TRIGGER) would also be great.
> 
> > In fact, IMHO, this would be a great place to start: we'd all love the
> > fuctionality, it'd have you examining almost all the same code, and it'd
> > be a feature we could all test, in diverse situations. DROP CONSTRAINT
> > is unlikely to be as widely tested. If you can build the introspection
> > correctly, so that it dumps/reloads correctly for _everyone_, then I'd
> > trust your DROP CONSTRAINT work a lot more.
> 
> Yes.  My take on this is that a lot of the constraint-related stuff,
> especially foreign keys, is misdesigned: the reason it's so hard to
> extract the info is that we are only storing an execution-oriented
> representation.  There should be a purely declarative representation
> of each constraint someplace, too, for ease of introspection.

Yes, and psql should be able to show constraint info too.

--  Bruce Momjian                        |  http://candle.pha.pa.us 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,
Pennsylvania19026
 

> A join as such doesn't bother me.  For example, it'd be proper for this
> hypothetical constraint catalog to have a column of table OIDs, which
> you'd have to join against pg_class to get the table name from.  The
> real issue is to make sure that we store enough info so that the
> original table/constraint declarations can be reconstructed in a
> straightforward fashion.

That would then require that an optional oid be stored that relates the
constraint to a particular attribute in a table, not just the table itself.
That way, column restraints can be reconstructed.

> Peter has remarked that the SQL spec offers a set of system views
> intended to provide exactly this info.  That should be looked at;
> if there's a workable standard for this stuff, we oughta follow it.

Speaking of - I simply cannot find a standard SQL specification anywhere on
the net, without buying one from ANSI.  I'm forced to rely on
vendor-specific docs - which are not standard in any way.  Is anyone able to
mail me such a thing?

Chris

* Christopher Kings-Lynne <chriskl@familyhealth.com.au> [001120 23:10]:
> Speaking of - I simply cannot find a standard SQL specification anywhere on
> the net, without buying one from ANSI.  I'm forced to rely on
> vendor-specific docs - which are not standard in any way.  Is anyone able to
> mail me such a thing?
I found a SQL99, Complete, Really book recently...  Seems very
complete.  I'll get an ISBN if ya want...

LER

> 
> Chris

-- 
Larry Rosenman                      http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

At 10:49 PM 11/20/00 -0500, Tom Lane wrote:
>"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>> Just to catch up here - does this mean that pg_dump has issues with
>> correctly recreating the contraints?
>
>Well, if you examine the pg_dump output, it doesn't really try ---
>you'll see no sign of any foreign-key constraint declarations in
>a pg_dump script, for example, only trigger declarations.  This is
>correct as far as reproducing the working database goes, but it's
>bad news for making a readable/modifiable dump script.

Short story, you are both right.

Chris - the dumps reload and recreate the constraints (in other words,
the answer to your question is "no")

Tom's correct in that decyphering the dump output is an ... interesting
problem.

(Tom, I just want to make sure that Chris undertands that dump/restore
DOES restore the constraints.  The "it doesn't really try" statement
you made, if hastily read without the qualifier, would lead one to believe
that a dump/restore would lose constraints).

What Tom's saying is the internal implementation of the SQL constraints
are exposed during the dump, where it would be much better if the SQL
that constructed the constraint were output instead.  The implementation
isn't hidden from the dump, rather the declaration is hidden.

>What's worse,
>this representation ties us down over version updates: we cannot easily
>change the internal representation of constraints, because the internal
>representation is what's getting dumped.

Which follows up my statement above perfectly.  If the implementation
were hidden, and the SQL equivalent dumped, we could change the implementation
without breaking dump/restore ACROSS VERSIONS.  (I capped because WITHIN
A VERSION dump/restore works fine).

>> Problem is that there are 5 difference types of constraints, implemented in
>> 5 different ways.  Do you want a unifed, central catalog of constraints, or
>> just for some of them, or what?
>
>Dunno.  Maybe a unified representation would make more sense, or maybe
>it's OK to treat them separately.  The existing implementations of the
>different types of constraints were done at different times, and perhaps
>are different "just because" rather than for any good reason.  We need
>investigation before we can come up with a reasonable proposal.

I think you hit the nail on the head when earlier you said that representation
was driven by the implementation.

Of course, one could say this is something of a PG tradition - check out
views,
which in PG 7.0 still are dumped as rules to the rule system, which no other
DB will understand.

So I can't say it's fair to pick on newer contraints like RI - they build
on a tradition of exposing the internal implementation to pg_dump and its
output, they didn't invent it.

If this problem is attacked, should one stop at constraints or make certain
that other elements like views are dumped properly, too?  (or were views
fixed for 7.1, I admit to a certain amount of "ignoring pgsql-hackers over
the last few months")



- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.
 

Don Baccus <dhogaza@pacifier.com> writes:
> If this problem is attacked, should one stop at constraints or make certain
> that other elements like views are dumped properly, too?  (or were views
> fixed for 7.1, I admit to a certain amount of "ignoring pgsql-hackers over
> the last few months")

Over the long run, there's a number of areas that need to be attacked
before pg_dump output will fully correspond to what was entered.
"SERIAL" columns are another favorite complaint, for example.
But I suggest that we try to deal with manageable pieces of the
problem ;-)

Views do seem to be dumped as views by current sources.
        regards, tom lane

At 12:03 AM 11/21/00 -0500, Tom Lane wrote:

>Peter has remarked that the SQL spec offers a set of system views
>intended to provide exactly this info.  That should be looked at;
>if there's a workable standard for this stuff, we oughta follow it.

This and a BUNCH else.



- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.
 

> Speaking of - I simply cannot find a standard SQL specification anywhere on
> the net, without buying one from ANSI.  I'm forced to rely on
> vendor-specific docs - which are not standard in any way.  Is anyone able to
> mail me such a thing?

Check the mailing list archives for the reference to a web site which
has what appears to be something close to the SQL99 standards document.
Let me know if you don't find it and I'll tarball something up for you.
                      - Thomas

Christopher Kings-Lynne wrote:
> 
> > I like this conversation as not a day goes by where I don't wish I could
> > edit the dump of a database rather than keeping structure entirely
> > seperate -- and actually do so in a useful manner.  That said, whats the
> > possibility of maintaining comments if the SQL dumps actually became
> > humanly editable?
> 
> >From reading the pg_dump source code, pg_dump creates a set of 'COMMENT ON
> ...' statements that should recreate all the comments associated with an
> oid.  So - there shouldn't be a problem, should there?

I was thinking of SQL that looks something like:

/******************************** TABLE:  example** Used to accomplish stuff*/
CREATE TABLE example ( example_id  serial
/* Must be a ZIP or Postal Code */, region        varchar(6) UNIQUE                         NOT NULL
/* Descriptive text */, description varchar(60) NOT NULL);


I've always made the assumption that anything in the /* */ was dropped.

At 00:22 21/11/00 -0500, Rod Taylor wrote:
>Christopher Kings-Lynne wrote:
>
>/*******************************
> * TABLE:  example
> *
> * Used to accomplish stuff
> */
>CREATE TABLE example 
> ( example_id  serial
>
> /* Must be a ZIP or Postal Code */
> , region        varchar(6) UNIQUE
>                          NOT NULL
>
> /* Descriptive text */
> , description varchar(60) NOT NULL
> );

From the point of view of efficient dump & load, I think you actually need
to dump:

CREATE TABLE example ( example_id  serial
-- Must be a ZIP or Postal Code, region        varchar(6) 
-- Descriptive text, description varchar(60));

Followed by:

ALTER TABLE example Alter Column region UNIQUE NOT NULL;
...etc. (Whatever the correct syntax is).

The reason for this is that UNIQUE constraints in particular are probably
very nasty things to check when loading a table. I would expect it to be
more efficient to create tables, load them, and define constraints. Also,
for FK constraints this is essential. Unless of course someone implements a
'SET ALL CONSTRAINTS OFF/ON'.

It is also nice to be able to dump constraints only.

So it's definitely a good idea to separate them, IMO.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

> CREATE TABLE example
>  ( example_id  serial
>
>  -- Must be a ZIP or Postal Code
>  , region        varchar(6)
>
>  -- Descriptive text
>  , description varchar(60)
>  );

Actually - this is something I _could_ do.

As the pg_dump is running, it shouldn't be too hard to select the comment
associated with each entity as it is being dumped.  ie.  In the example
above, the comments for each attribute would be retrieved from
pg_description (or whatever) and output as '-- ...' comments.

Then, if the COMMENT ON statements are also still dumped at the bottom, you
get the ability to see comments conveniently in your dump, but with the
ability to still hand-edit them before restoring the dump...

Chris

At 15:10 21/11/00 +0800, Christopher Kings-Lynne wrote:
>> CREATE TABLE example
>>  ( example_id  serial
>>
>>  -- Must be a ZIP or Postal Code
>>  , region        varchar(6)
>>
>>  -- Descriptive text
>>  , description varchar(60)
>>  );
>
>Actually - this is something I _could_ do.
>
>As the pg_dump is running, it shouldn't be too hard to select the comment
>associated with each entity as it is being dumped.  ie.  In the example
>above, the comments for each attribute would be retrieved from
>pg_description (or whatever) and output as '-- ...' comments.

I was actually more worried about making sure the constraints were dumped
separately from the table, but maybe I missed the point of the original post. 

>Then, if the COMMENT ON statements are also still dumped at the bottom, you
>get the ability to see comments conveniently in your dump, but with the
>ability to still hand-edit them before restoring the dump...

If I recall correctly, the comments are actually grabbed when each table is
retrieved, so it is easy to do. But is it really a good idea?


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

At 12:18 AM 11/21/00 -0500, Tom Lane wrote:
>Don Baccus <dhogaza@pacifier.com> writes:
>> If this problem is attacked, should one stop at constraints or make certain
>> that other elements like views are dumped properly, too?  (or were views
>> fixed for 7.1, I admit to a certain amount of "ignoring pgsql-hackers over
>> the last few months")

...

>Views do seem to be dumped as views by current sources.

Good...definitely a step in the right direction!



- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.
 

On Tue, 21 Nov 2000, Christopher Kings-Lynne wrote:

> > > Problem is that there are 5 difference types of constraints,
> > implemented in
> > > 5 different ways.  Do you want a unifed, central catalog of
> > constraints, or
> > > just for some of them, or what?
> >
> > Dunno.  Maybe a unified representation would make more sense, or maybe
> > it's OK to treat them separately.  The existing implementations of the
> > different types of constraints were done at different times, and perhaps
> > are different "just because" rather than for any good reason.  We need
> > investigation before we can come up with a reasonable proposal.
> 
> It strikes me that having a catalog (so to speak) of all contraints, with
> flags in the tables where the contraints are implemented would allow a
> separation of presentation and implementation.

Yeah, the hard part is storing enough information to recover the
constraint in an easy way without going to the implementation details,
strings aren't sufficient by themselves because that gets really difficult
to maintain as table/columns change or are dropped. Maybe a central
catalog like the above and a backend function that takes care of
formatting to text would work.  Or keeping track of the dependent objects
and re-figuring the text form (or drop constraint, or whatever) when those
objects are changed/dropped.

I think that combining different constraints is good to some extent
because there are alot of problems with many constraints (the RI ones have
problems, check constraints are currently not deferrable AFAIK,
the unique constraint doesn't actually have the correct semantics) and
maybe thinking about the whole set of them at the same time would be a
good idea.

> > > I assume that column contraints implicitly become table
> > constraints.  This
> > > will also make it easy to have global unique contraint names.
> > Actually -
> > > are the constraint  names currently unique for an entire database?
> >
> > No, and they shouldn't be --- only per-table, I think.
> 
> Oops - correct.  Wasn't paying attention.  I forgot that the table name is
> specified as part of the ALTER statement.

I'm not sure actually, it seems to say in the syntax rules for the
constraint name definition that the qualified identifier of a constraint
needs to be different from any other qualified identifier for any other
constraint in the same schema, so Christopher may have been right the
first time (given we don't have schema).

> 
> On Tue, 21 Nov 2000, Christopher Kings-Lynne wrote:
> 
> > > > Problem is that there are 5 difference types of constraints,
> > > implemented in
> > > > 5 different ways.  Do you want a unifed, central catalog of
> > > constraints, or
> > > > just for some of them, or what?
> > >
> > > Dunno.  Maybe a unified representation would make more sense, or maybe
> > > it's OK to treat them separately.  The existing implementations of the
> > > different types of constraints were done at different times, and perhaps
> > > are different "just because" rather than for any good reason.  We need
> > > investigation before we can come up with a reasonable proposal.
> > 
> > It strikes me that having a catalog (so to speak) of all contraints, with
> > flags in the tables where the contraints are implemented would allow a
> > separation of presentation and implementation.
> 
> Yeah, the hard part is storing enough information to recover the
> constraint in an easy way without going to the implementation details,
> strings aren't sufficient by themselves because that gets really difficult
> to maintain as table/columns change or are dropped. Maybe a central
> catalog like the above and a backend function that takes care of
> formatting to text would work.  Or keeping track of the dependent objects
> and re-figuring the text form (or drop constraint, or whatever) when those
> objects are changed/dropped.
> 
> I think that combining different constraints is good to some extent
> because there are alot of problems with many constraints (the RI ones have
> problems, check constraints are currently not deferrable AFAIK,
> the unique constraint doesn't actually have the correct semantics) and
> maybe thinking about the whole set of them at the same time would be a
> good idea.
> 
> > > > I assume that column contraints implicitly become table
> > > constraints.  This
> > > > will also make it easy to have global unique contraint names.
> > > Actually -
> > > > are the constraint  names currently unique for an entire database?
> > >
> > > No, and they shouldn't be --- only per-table, I think.
> > 
> > Oops - correct.  Wasn't paying attention.  I forgot that the table name is
> > specified as part of the ALTER statement.
> 
> I'm not sure actually, it seems to say in the syntax rules for the
> constraint name definition that the qualified identifier of a constraint
> needs to be different from any other qualified identifier for any other
> constraint in the same schema, so Christopher may have been right the
> first time (given we don't have schema).

tom and i spoke of this problem at the Open Source Database
Summit awhile back. 

in a nutshell, postgres doesn't maintain explicit 
relationships between tables.  my experience says
that foreign/primary keys fall under the
category of extended domains, not rules, and, hence,
postgres is a bit out of the loop.

my vote is for storing the relationships in
the system tables, as most commercial DBs do.
otherwise, an entire class of DDL applications
won't be possible under postgres.

john

-

John Scott
Senior Partner
August Associates
web: http://www.august.com/~john

....................................
Get your own free email account from
http://www.popmail.com

Christopher Kings-Lynne wrote:

> Speaking of - I simply cannot find a standard SQL specification anywhere on
> the net, without buying one from ANSI.  I'm forced to rely on
> vendor-specific docs - which are not standard in any way.  Is anyone able to
> mail me such a thing?

You may want to take a look through http://www.techstreet.com -- I
searched standards for the keyword 'database', and found that many 
of the SQL documents were available as PDFs for $18.00 each.

-- 
Karl DeBisschop                        kdebisschop@alert.infoplease.com
Learning Network Reference             http://www.infoplease.com
Netsaint Plugin Developer              kdebisschop@users.sourceforge.net