Обсуждение: 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