Re: plpgsql constraint checked data fails to restore

On Mon, Jun 20, 2005 at 08:18:00AM -0400, John Burger wrote:
>
> I don't have personal experience with this, but I thought it was fairly
> common to DEFER constraint checking until after a restore completed,
> for exactly this reason.

Regarding DEFERRABLE, according to the CREATE TABLE documentation,
"Only foreign key constraints currently accept this clause. All
other constraint types are not deferrable."

http://www.postgresql.org/docs/8.0/static/sql-createtable.html

pg_dump could defer adding CHECK constraints until after data is
restored, but currently it doesn't behave that way -- it usually
emits CHECK constraints with the CREATE TABLE statements, and I
don't see an option in the documentation to change that behavior.
Search the list archives for past discussion; for example:

http://archives.postgresql.org/pgsql-hackers/2004-03/msg00035.php
http://archives.postgresql.org/pgsql-hackers/2004-03/msg00755.php

It should be possible to use a trigger instead of a CHECK constraint
because pg_dump adds triggers after data is restored. But without
careful design you still have the same integrity problem as with a
CHECK constraint: data is checked when it's inserted or updated,
but not when the referred-to data is modified. For that you'd need
another constraint or trigger that knows how to check all the
dependent data.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/