From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | John Burger <john(at)mitre(dot)org> |
Cc: | Lee Harr <missive(at)hotmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: plpgsql constraint checked data fails to restore |
Date: | 2005-06-20 13:34:13 |
Message-ID: | 20050620133413.GA25813@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | O.B. | 2005-06-20 13:43:01 | Re: Access is denied during initdb |
Previous Message | Rodrigo Katsumoto Sakai | 2005-06-20 13:32:37 | Help! What has changed in the internal structure of PostgreSQL? |