Re: 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 <thom(at)linux(dot)com> wrote:

> On 23 May 2013 15:33, Thom Brown <thom(at)linux(dot)com> wrote:
> > On 23 May 2013 10:15, Keith Fiske <keith(at)omniti(dot)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
>