Postgres Pro
Downloads
Home   >   mailing lists

Re: Foreign Key violated - Mailing list pgsql-general

From Keith Fiske
Subject Re: Foreign Key violated
Date
Msg-id CAG1_KcCE=0qVMQh4tist+_a9EnGZsHmvscy9W2ju9=cBmJp9jQ@mail.gmail.com
Whole thread Raw
In response to Re: Foreign Key violated  (Thom Brown <thom@linux.com>)
List pgsql-general
Tree view
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 <thom@linux.com> wrote:
On 23 May 2013 15:33, Thom Brown <thom@linux.com> wrote:
> On 23 May 2013 10:15, Keith Fiske <keith@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

pgsql-general by date:

Previous
From: Raymond O'Donnell
Date:
Subject: Re: How to modify dump files created by pg_dump
Next
From: Jeff Janes
Date:
Subject: Re: Streaming replication with sync slave, but disconnects due to missing WAL segments