Re: BUG #1930: Cannot create foreign key constraint

"Warren Little" <warren(dot)little(at)reardeninc(dot)com> writes:
> pg_restore: [archiver (db)] could not execute query: ERROR: insert or
> update on table "asientry" violates foreign key constraint "asientry_r0"
> DETAIL: Key (pid)=(AA0003403544) is not present in table "pobject".
> Command was: ALTER TABLE ONLY asientry
> ADD CONSTRAINT asientry_r0 FOREIGN KEY (pid) REFERENCES pobject(pid);

> Also running the alter table after restore generates same constraint
> violation error.

> The following query returns one row:

> select * from asientry, pobject
> where pobject.pid = 'AA0003403544'
> and pobject.pid = asientry.pid;

That seems a bit odd. Are the two pid columns exactly the same datatype
(and what is it anyway)? What plan does EXPLAIN show for that SELECT,
and what plan do you see for

SELECT fk.pid FROM ONLY asientry fk
LEFT OUTER JOIN ONLY pobject pk
ON pk.pid=fk.pid
WHERE pk.pid IS NULL AND fk.pid IS NOT NULL;

(which is what is probably being used to test foreign key validity)?

regards, tom lane