Re: Table/Column Constraints

At 10:49 PM 11/20/00 -0500, Tom Lane wrote:
>"Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)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(at)pacifier(dot)com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.