A foreign key constraint violation in postgres that shouldn't be happening, as far as I can tell. (w/ hibernate)

So I have two tables:

table A
-id
-other stuff

table B
-id
-stuff
-a_id, a fk column to id in A

in hibernate, I've mapped B.a_id as a simple property (I don't want a many-to-one and get an entire A instance out, I just want the Id). So let's say I have a row in A with id=100.

if I attempt to insert a new row into B, with a_id=100, I get a postgres foreign key constraint violation saying no A exists with id=100 !

I do not understand this. I turned on the show_sql in hibernate, and it generates this for the B insert:

insert into B (stuff, a_id) values (?, ?)

so that looks legit.

The hibernate mapping I have for B.a_id looks like:

   <property name="aId" type="java.lang.Long" unique="true" not-null="true">
        <column name="a_id" />
    </property>

the constraint added in postgres looks like:

alter table B 
add constraint myfk
foreign key (a_id) 
references A;

Any ideas?

Thank you

edit: I do not think hibernate has anything to do with this. if I try the insert by hand using sql, I get the same error.

edit2: There is a subtle twist - the id fields are int8's and have sequences on them:

    create table A (
id int8 not null unique,
stuff varchar(10),
primary key(id)
);
create table B (
id int8 not null unique,
a_id int8 not null references A,
primary key(id)
);

create sequence a_seq;
ALTER SEQUENCE a_seq OWNED BY a.id;
ALTER TABLE a ALTER COLUMN id SET DEFAULT nextval('a_seq');

create sequence b_seq;
ALTER SEQUENCE b_seq OWNED BY b.id;
ALTER TABLE b ALTER COLUMN id SET DEFAULT nextval('b_seq');

CREATE TABLE A (id INT, other_stuff VARCHAR(20) NULL);
CREATE TABLE b (id INT, stuff VARCHAR(20) NULL, a_id INT);
ALTER TABLE A ADD CONSTRAINT PK_A PRIMARY KEY (id);
ALTER TABLE B ADD CONSTRAINT myfk FOREIGN KEY (a_id) REFERENCES A;

INSERT INTO A (id) VALUES (100);
INSERT INTO B (id, a_id) VALUES (1,100);

SELECT * FROM a JOIN b ON a.id = b.a_id;
 id  | other_stuff | id | stuff | a_id 
-----+-------------+----+-------+------
 100 |             |  1 |       |  100

I attempted to duplicate your problem using the above SQL in PostgreSQL 9.0.3 and I couldn't duplicate your error. Any DDL/DML that you can capture as well as the exact error message would be helpful.

(I'll keep adding info as you provide additional info. Hopefully this can serve as a starting point for someone else, too.)

If the parent row in table A is there, then PostgreSQL will not throw an error.

The generation of the ID columns should not matter either (once the parent row is inserted).

The only thing, that I can think of: did you maybe insert the row in table A in a differen hibernate session and forgot to commit that?

Another session/transaction that inserts the row in table B will not see the the uncommitted row in table A.

I have no problem running the statements directly:

postgres=> create table A (
postgres(>   id int8 not null unique,
postgres(>   stuff varchar(10),
postgres(>   primary key(id)
postgres(> );
CREATE TABLE
postgres=>
postgres=> create table B (
postgres(>   id int8 not null unique,
postgres(>   a_id int8 not null references A,
postgres(>   stuff varchar(10),
postgres(>   primary key(id)
postgres(> );
CREATE TABLE
postgres=>
postgres=> create sequence a_seq;
CREATE SEQUENCE
postgres=> ALTER SEQUENCE a_seq OWNED BY a.id;
ALTER SEQUENCE
postgres=> ALTER TABLE a ALTER COLUMN id SET DEFAULT nextval('a_seq');
ALTER TABLE
postgres=>
postgres=> create sequence b_seq;
CREATE SEQUENCE
postgres=> ALTER SEQUENCE b_seq OWNED BY b.id;
ALTER SEQUENCE
postgres=> ALTER TABLE b ALTER COLUMN id SET DEFAULT nextval('b_seq');
ALTER TABLE
postgres=>
postgres=> COMMIT;
COMMIT
postgres=> INSERT INTO a (stuff) VALUES ('a_stuff');
INSERT 0 1
postgres=> commit;
COMMIT
postgres=> select * from a;
 id |  stuff
----+---------
  1 | a_stuff
(1 row)


postgres=> INSERT INTO b (a_id, stuff) VALUES (1, 'b_stuff');
INSERT 0 1
postgres=> commit;
COMMIT;
postgres=>