The following bug has been logged on the website:
Bug reference: 13603
Logged by: Gavin Wahl
Email address: gavin@fusionbox.com
PostgreSQL version: 9.4.4
Operating system: Linux
Description:
I have a database with a foreign key constraint, with data in in that
violates
the constraint. Shouldn't that be impossible? The DEFERRABLE isn't related
--
the transaction that added this data has already committed. The data came
from
pgloader so I'm not sure the exact query that was able to insert data
violating
the fk constraint.
# \d products_specifications
Table "public.products_specifications"
Column | Type |
Modifiers
------------+------------------------+----------------------------------------------------------------------
id | integer | not null default
nextval('products_specifications_id_seq'::regclass)
name | character varying(255) | not null
product_id | integer |
Indexes:
"products_specifications_pkey" PRIMARY KEY, btree (id)
"products_specifications_9bea82de" btree (product_id)
Foreign-key constraints:
"D5225bac506acc40e6f759b266a3b1c8" FOREIGN KEY (product_id) REFERENCES
products_product(variant_ptr_id) DEFERRABLE INITIALLY DEFERRED
# \d products_product
Table "public.products_product"
Column | Type | Modifiers
--------------------+------------------------+-----------
variant_ptr_id | integer | not null
Indexes:
"products_product_pkey" PRIMARY KEY, btree (variant_ptr_id)
Referenced by:
TABLE "products_specifications" CONSTRAINT
"D5225bac506acc40e6f759b266a3b1c8" FOREIGN KEY (product_id) REFERENCES
products_product(variant_ptr_id) DEFERRABLE INITIALLY DEFERRED
# SELECT * FROM products_specifications WHERE product_id = 15;
id | name | product_id
----+-------------+------------
5 | 200 threads | 15
(1 row)
# SELECT * FROM products_product WHERE variant_ptr_id = 15;
variant_ptr_id
---------------
(0 rows)
Inserting new rows fails:
# INSERT INTO products_specifications (name, product_id) VALUES ('foo',
15);
ERROR: insert or update on table "products_specifications" violates foreign
key constraint "D5225bac506acc40e6f759b266a3b1c8"
DETAIL: Key (product_id)=(15) is not present in table "products_product".
But updating the existing row succeeds:
# UPDATE products_specifications SET product_id = 15 WHERE id = 5;
UPDATE 1
