From: | Chris Browne <cbbrowne(at)acm(dot)org> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Check constraints on non-immutable keys |
Date: | 2010-06-30 22:16:55 |
Message-ID: | 874ogk17zc.fsf@cbbrowne.afilias-int.info |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Farina | 2010-06-30 22:22:09 | Re: Error with GIT Repository |
Previous Message | Bruce Momjian | 2010-06-30 21:57:04 | Re: Keepalive for max_standby_delay |