Re: Table/Column Constraints

"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. 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. Loading an old dump file into
a new version with a different constraint implementation would not
work as desired. (This may mean that we can't change it, which would
*really* be a problem...)

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

Exactly.

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

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

No, and they shouldn't be --- only per-table, I think.

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

Right, exactly my point above. We discard too much information that
needs to be retained somewhere...

regards, tom lane