Skip to content

add NOT VALID when disabling contraints so they don't get checked on restore

Dan Notestein requested to merge disable-constraint-validity-checks into develop

If we drop the requirement that we recheck constraints on haf tables after dropping them, we can restore them in milliseconds. This also means there's no real penalty for going thru the P2P sync state before entering the LIVE sync state in sql serializer, so we can eliminate the live_threshhold variable and the associated code. In practice, this means we can resume from a 9 day old snapshot in a matter of a few minutes (as opposed to 2 hours on a very fast machine).

Just to clarify the meaning of 'NOT VALID': it avoids checking the constraint when the constraint is added to the table, but the constraint is checked when any data is added thereafter.

So there is one downside to this change: if p2p sync/massive sync code gets broken in a way that the constraints get violated, we won't auto-detect the break because we will not detect the constraint was invalidated during that time. These constraints will only detect violations during live sync when the constraint exists. But, it saves a huge amount of time for regular production usage and the constraint can always be checked later as described below.

If you later want to validate any given NOT VALID constraint, you can use syntax like: ALTER TABLE x VALIDATE constraint_on_x;

Note that this command can be executed at any time (it doesn't lock the table), so this already offers advantages over the old method, because you can start your application quicker, before waiting for the old constraint to be checked first. On a successful run, the 'NOT VALID' part of the constraint's definition will be removed.

@bwrona can you assign someone to fix the test failure associated with this change?

Edited by Dan Notestein

Merge request reports