Re: [GENERAL] Foreign Key violated
Apologies for not replying sooner. After a few days, we actually found out the cause was a user turning off all triggers on the table, forcing some data into it to try and solve an RMA issue manually, then turning the triggers back on. This hadn't showed up on any logs, and after finding zero signs of corruption or other FKs being violated, we asked the for more information about what had been done recently and they fessed up. So, relief on one hand that there was no data corruption. But a bit troubling that the user did that :p -- Keith Fiske Database Administrator OmniTI Computer Consulting, Inc. http://www.keithf4.com On Wed, May 29, 2013 at 10:52 AM, Thom Brown t...@linux.com wrote: On 23 May 2013 15:33, Thom Brown t...@linux.com wrote: On 23 May 2013 10:15, Keith Fiske ke...@omniti.com 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
Re: [GENERAL] Foreign Key violated
On 23 May 2013 15:33, Thom Brown t...@linux.com wrote: On 23 May 2013 10:15, Keith Fiske ke...@omniti.com 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Foreign Key violated
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 -- Keith Fiske Database Administrator OmniTI Computer Consulting, Inc. http://www.keithf4.com
Re: [GENERAL] Foreign Key violated
On 23 May 2013 10:15, Keith Fiske ke...@omniti.com 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; -- Thom -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Foreign Key violated
Hi, I'm not sure if I understand your issue, but could you output SELECT COUNT(*) FROM rmas WHERE id = 1008122437 AND status = 'r'; HTH Kind regards/met vriendelijke groet, Serge Fonville http://www.sergefonville.nl Convince Microsoft! They need to add TRUNCATE PARTITION in SQL Server https://connect.microsoft.com/SQLServer/feedback/details/417926/truncate-partition-of-partitioned-table 2013/5/23 Keith Fiske ke...@omniti.com 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 -- Keith Fiske Database Administrator OmniTI Computer Consulting, Inc. http://www.keithf4.com
