Josh Berkus wrote:
> On 03/19/2014 02:01 PM, Alvaro Herrera wrote:
> > Josh Berkus wrote:
> >> All,
> >>
> >> So, I'll ask again (because I didn't see a reply): is there any way
> >> users can *check* if they've been corrupted? Short of waiting for PK/FK
> >> violations?
Some notes:
1. if there's been no crash with 9.3 installed in a single system, or in
a master system, corruption cannot have occured.
2. replicas are very likely to have gotten corrupted if referenced
tables are updated at all. Many workloads do not update referenced
tables; those are not at risk.
3. Master that are failed-over at-risk replicas are thus very likely to
have been corrupted.
> > Obviously there are queries you can run to check each FK -- the same
> > queries that ri_triggers.c would run when you create an FK. It's
> > cumbersome to write, but not impossible. In fact, it can be done
> > mechanically.
>
> Would users which this corruption necessarily have broken FKs which
> would show up as such on a simple query? That is, if I did:
>
> SELECT ref_id FROM referenced WHERE ref_id NOT IN ( SELECT ref_id FROM
> referencing )
>
> ... or something similar, would that show the issue?
Yes, AFAICT that would show the issue, as long as the query uses an
index. I assume, without checking, that setting enable_seqscan to OFF
would have that effect on most but the largest tables. I think it'd be
better to write that as an EXISTS query, though. You also need to
consider details such as the MATCH mode of the FK, for multicolumn ones.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services