On Mon, Mar 21, 2005 at 10:58:27AM -0500, Stephen Howie wrote: > I've notice a couple things. I ran a couple test and at first I > couldn't duplicate my error on some test tables. But I then added > inheritance to one of the tables and thats when I got the error again.
Are you aware that foreign keys and inheritance don't play well together? See the comments at the bottom of the following page: http://www.postgresql.org/docs/8.0/static/ddl-inherit.html > It looks like there is an error when obtaining the seq id (nextval) from > the original table and using it on a table that has a foreign key to the > original table by inserting it into the table that inherits the original > table, within a transaction. What I think is happening is since the > insert is on the inherited table the foreign key doesn't see the insert > into the original table until the transaction is committed. Have you tested that hypothesis by doing an insert with a foreign key value that *has* been committed? Reducing the problem to the simplest possible test case can reveal what's really happening -- the views, rules, and functions might not be relevant to the problem and they're just adding unnecessary complexity to the investigation. CREATE TABLE parent ( id integer PRIMARY KEY, data text NOT NULL ); CREATE TABLE child ( more_data text NOT NULL ) INHERITS (parent); CREATE TABLE other ( fkid integer NOT NULL REFERENCES parent ); BEGIN; INSERT INTO parent (id, data) VALUES (1, 'foo'); INSERT INTO child (id, data, more_data) VALUES (2, 'bar', 'baz'); COMMIT; SELECT * FROM parent; id | data ----+------ 1 | foo 2 | bar (2 rows) INSERT INTO other (fkid) VALUES (1); INSERT INTO other (fkid) VALUES (2); ERROR: insert or update on table "other" violates foreign key constraint "other_fkid_fkey" DETAIL: Key (fkid)=(2) is not present in table "parent". I suspect the problem is simply that inheritance has known and documented limitations with constraints like foreign keys. > Also, in my original schema I'm getting an increment of 2 every time I > run nextval. I can't duplicate this yet but I'm looking into it. > Possibly my error somewhere in the function. Could be one of the "gotchas" with using rules. Search the archives for past discussion. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
