RE: pg_dump / Unique constraints

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 |/