On 23 May 2013 15:33, Thom Brown <thom@xxxxxxxxx> wrote: > On 23 May 2013 10:15, Keith Fiske <keith@xxxxxxxxxx> wrote: >> Client reported an issue where it appears a foreign key has been violated >> >> prod=#\d rma_items >> [snip] >> rma_items_rma_id_status_fk" FOREIGN KEY (rma_id, rma_status) REFERENCES >> rmas(id, status) ON UPDATE CASCADE ON DELETE CASCADE >> >> prod=# select i.rma_id, i.rma_status, r.id, r.status from rmas r join >> rma_items i on i.rma_id = r.id and i.rma_status != r.status; >> rma_id | rma_status | id | status >> ------------+------------+------------+-------- >> 1008122437 | r | 1008122437 | c >> (1 row) >> >> >> Attempting to reinsert this data again causes a violation error, so it >> doesn't appear to be broken >> >> prod=# begin; >> BEGIN >> prod=# insert into rma_items (rma_id, order_item_id, return_reason_id, >> rma_status) values (1008122437, 1007674099, 9797623, 'r'); >> ERROR: insert or update on table "rma_items" violates foreign key >> constraint "rma_items_rma_id_status_fk" >> DETAIL: Key (rma_id, rma_status)=(1008122437, r) is not present in table >> "rmas". >> prod=# rollback; >> ROLLBACK >> >> This is running 9.2.4 on CentOS. If anyone can suggest how I can look into >> this deeper and find what the problem may be, I'd appreciate it. I'm here at >> PGCon if anyone is available to help IRL as well > > What do you get with: > > SELECT conname > FROM pg_constraint > WHERE NOT convalidated; Did you resolve this? -- Thom -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
