SEO

>>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.

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.

Thanks again.

__________________________________________________ _______________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/g...ave/direct/01/


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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 #

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

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/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend