Обсуждение: Re: [GENERAL] plpgsql constraint checked data fails to restore

Added pgsql-hackers
Added Bruce Momjian


On 6/23/2005 12:19 PM, Michael Fuhr wrote:
>> The question I have is how exactly you manage to get the trigger fired
>> when restoring the dump. By default, the dump created by pg_dump will
>> create the table, fill in the data and create the trigger(s) only after
>> that.
>
> Not true for CHECK constraints -- pg_dump creates them with the
> CREATE TABLE statement:

This is still true in 8.1's pg_dump, even though check constraints can
be added later. Even though it is bad practice to have functions that
rely on or even manipulate other objects in a CHECK constraint, I think
pg_dump should add the check constraints in the same manner as it does
triggers.

Bruce, do we have a TODO item for this?


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Jan Wieck <JanWieck@Yahoo.com> writes:
> I think pg_dump should add the check constraints in the same manner as
> it does triggers.

> Bruce, do we have a TODO item for this?

No, because that idea has been proposed and rejected before --- it adds
overhead (extra table scans) and reduces readability of the SQL dump,
in order to "support" a programming technique that will never really
work correctly anyway.  A CHECK constraint that depends on anything more
than the content of the row being checked is simply wrong.

Essentially what we implement is what SQL92 calls the "intermediate"
level of CHECK support:

         1) The following restrictions apply for Intermediate SQL:

            a) The <search condition> contained in a <check constraint defi-
              nition> shall not contain a <subquery>.

regression=# create table bbb(f2 int check (f2 in (select f1 from aaa)));
ERROR:  cannot use subquery in check constraint

Of course, a function call that executes a query internally is simply
a cheat to try to bypass this restriction; the fact that we don't catch
you cheating doesn't mean we promise it will work.  The function call
is a lot worse, in fact, because there is no way pg_dump can even detect
the data dependency, and thus no way to know when it is safe to add the
check constraint.

There is no point in changing the behavior of pg_dump until and unless
we improve the handling of CHECK constraints to support subqueries ---
which is more or less the same thing as supporting database-wide
ASSERTIONs, and I don't know of anybody even thinking of working on that.

            regards, tom lane