RE: Table/Column Constraints

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