Re: plpgsql constraint checked data fails to restore

On 6/20/2005 1:23 PM, Lee Harr wrote:

Some of the data required by the check function
is being restored after the data being checked
by the function and so it all fails the constraint.

Are you saying that the check function perform queries against other
data? That might not be a good idea -- consider what happens if
the data changes: would changes invalidate records that had previously
passed the check but that wouldn't pass now if they were checked
again?

You ask some great questions. Thanks.

But not the really important one :-)

I think maybe I just got a little constraint-happy. The way I have
it, there is definitely a possibility for the other data to change
out from under the constraint. That can't be good.

Right now, I don't really see another way to check what I wanted
to check, so I am just going to remove the constraint.

When I get a few minutes, I will post my simplified example and
maybe someone will have a good idea.

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. From that I conclude that you are taking a data-only dump and
restore the schema first either from a text file or a separate pg_dump
schema only.

If you do keep your schema in external ascii files and do data-only
dumps, you have to split the schema into table creation (without
constraints, indexes, etc.) and a second part that adds all the
constraints and indexes after the data is loaded.

Jan

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

On Thu, Jun 23, 2005 at 11:47:37AM -0400, Jan Wieck wrote:

On 6/20/2005 1:23 PM, Lee Harr wrote:

You ask some great questions. Thanks.

But not the really important one :-)

Maybe that's because it didn't need asking :-)

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:

CREATE TABLE foo (
id integer PRIMARY KEY
);

CREATE TABLE bar (
fooid integer NOT NULL REFERENCES foo,
x integer CHECK (x > 0)
);

INSERT INTO foo (id) VALUES (1);
INSERT INTO foo (id) VALUES (2);

INSERT INTO bar (fooid, x) VALUES (1, 2);
INSERT INTO bar (fooid, x) VALUES (2, 3);

pg_dump testdb

[...]

CREATE TABLE bar (
fooid integer NOT NULL,
x integer,
CONSTRAINT bar_x_check CHECK ((x > 0))
);

[...]

CREATE TABLE foo (
id integer NOT NULL
);

[...]

COPY bar (fooid, x) FROM stdin;
1 2
2 3
\.

[...]

COPY foo (id) FROM stdin;
1
2
\.

[...]

ALTER TABLE ONLY foo
ADD CONSTRAINT foo_pkey PRIMARY KEY (id);

[...]

ALTER TABLE ONLY bar
ADD CONSTRAINT bar_fooid_fkey FOREIGN KEY (fooid) REFERENCES foo(id);

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

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