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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
