RE: pg_dump / Unique constraints - Mailing list pgsql-hackers
From | Philip Warner |
---|---|
Subject | RE: pg_dump / Unique constraints |
Date | |
Msg-id | 3.0.5.32.20001122195811.026a09d0@mail.rhyme.com.au Whole thread Raw |
In response to | RE: pg_dump / Unique constraints ("Christopher Kings-Lynne" <chriskl@familyhealth.com.au>) |
Responses |
Re: pg_dump / Unique constraints
|
List | pgsql-hackers |
At 16:33 22/11/00 +0800, Christopher Kings-Lynne wrote: >At 15:50 22/11/00 +0800, Christopher Kings-Lynne wrote: >> >I've been examining the pg_dump source and output, and I've come to the >> >conclusion that I can modify it so that UNIQUE constraints >> appear as part of >> >the CREATE TABLE statement, rather than as a separate CREATE INDEX. >> ... >> >Is there any problem with me working on this? >> >> I actually don't think it's a good idea to force things to work that way. > >Why, exactly? Having now looked at the code and seen that PK constraints are already dumped in the table definition, I guess doing unique constraints in the same way is no worse. My main concern is that I'd like pg_dump to be able to separate out the various parts of the schema, and this includes constraints. The ability to add/drop constraints at any part of the restoration process would be very nice. The latest incarnations of pg_dump/pg_restore allow people (and pg_dump/restore) to choose what to restore, and even to define an ordering for them - and having the constraimts as separate items would be a great benefit. One example of the problems that I'd like to avoid is in loading data via INSERT statements - doing: Create Table... Insert many rows... Add Uniqueness Constraint is *substantially* faster than INSERTs on a table with constraints already defined. At the current time we don't even have a working 'ALTER TABLE...' that works with all constraint types, so my hopes are probably in vain. I don't suppose you feel like working on 'ALTER TABLE...ADD/DROP CONSTRAINT...' do you???? >What's the difference between this: > >-- >create table test ( > a int4, > constraint "name" unique (a) >) >-- > >and this: > >-- >create table test ( > a int4 >) >create unique index "name" on "test" using btree ( "a" "int4_ops" ); The fact that pg_dump/restore will be able to create the index at the end of the data load. > >As far as I can tell, Postgres 7.0.3 only supports adding fk constraints. >The CVS version seems to support adding CHECK constraints, but other than >that, it has to be added as an index. Sounds like a good thing to work on ;-} >If you're a database user, it's >conceptually better to see right in your table that you've added a named (or >not) unique constraint, rather than noticing at the bottom of the file that >there's some unique index on one of your columns (IMHO). This is a good argument for modifying the output of '\d' in psql. It is also probably a valid argument for a new option on pg_dump to specify if constraints should be kept separate from table definitions. Then we could also move FK constraints to the end. ---------------------------------------------------------------- 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 |/
pgsql-hackers by date: