Re: Table/Column Constraints

"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