Re: Help with transactions

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/