Re: Check constraints on non-immutable keys

On Wed, Jun 30, 2010 at 19:16, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Wed, Jun 30, 2010 at 11:49 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> I can't recall many
>>> field complaints about it.  And the ones I do recall wouldn't have been
>>> prevented by a check as stupid as "are there immutable functions in
>>> here".
>
>> Hopefully there aren't too many ways to get data into a table that
>> doesn't satisfy its check constraint - what else are you thinking of?
>
> Nobody is talking about having bypassed a check constraint --- the
> problem here is what if the "same" constraint condition is true today
> and false tomorrow.  The cases that I can recall were not directly about
> time passing, but rather about check constraints that were designed to
> examine the contents of other tables or other rows in the same table.
> Functions that do that are properly declared STABLE not VOLATILE, but
> they'd still be rejected by Magnus' proposed restriction.  The problem
> is that people would be *very* likely to just mark them IMMUTABLE rather
> than understand that what they're trying is fundamentally unreliable.
> That would cause them other problems, and they'd still be at risk of
> their dumps not reloading.
>
> 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?)

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/