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      
PostgreSQL Development, 24x7 Support, Training & Services

Sent via pgsql-hackers mailing list (
To make changes to your subscription:

Reply via email to