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 :pOn Wed, May 29, 2013 at 10:52 AM, Thom Brown <thom@xxxxxxxxx> wrote:
Did you resolve this?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;
--
Thom
