Re: Check constraints on non-immutable keys

magnus(at)hagander(dot)net (Magnus Hagander) writes:
>> I concur with the thought that the most useful solution might be a way
>> to tell pg_restore to remove or disable check constraints.
>
> Uh, say what? Are you saying pg_restore should actually remove
> something from the database schema? And thus no longer be valid for
> taking database backups?
>
> Or are you just saying that it should have the constraints off, load
> the data, and then somehow create the constraint without having it
> validate the exinsting data (like the NOCHECK option in MSSQL?)

Well, consider the way that foreign keys are handled by pg_dump.

- Initially, it dumps out the table schema, NOT replete with foreign
key constraints.

- Data is loaded, *without* checking foreign keys.

- Foreign keys are added in, afterwards.

That's not a scenario where constraints are ignored - their evaluation
is merely deferred.

For constraints that involve dates, I can certainly see a potential for foot guns.

It points me towards making sure that our apps don't do
over-aggressive things like having constraints to prevent data from
being inserted back-dated, as that would cause restores of backups to
break. That's a dangerous kind of constraint.

It's *possible* that it would be an idea to apply the check constraint
late in the pg_dump, so that the ill effects might be imagined to be
alleviated. That seems rather wishful.
--
output = ("cbbrowne" "@" "gmail.com")
http://linuxdatabases.info/info/languages.html
HEADLINE: Suicidal twin kills sister by mistake!