Re: ALTER TABLE lock level

Marko Tiikkaja <marko(at)joh(dot)to> wrote:
> On 5/27/13 5:23 PM, I wrote:
>> Today I got (unfortunately) reminded that this statement, in
>> sql-altertable.html under VALIDATE CONSTRAINT, is not true anymore:
>>
>> The value of separating validation from initial creation of the
>> constraint is that validation requires a lesser lock on the table than
>> constraint creation does.
>
> Since nobody seems to want to pick this up, I'm going to suggest the
> following wording:
>
>     The value of separating validation from initial creation of the
>     constraint is that you can immediately start enforcing the
>     constraint on all future rows, but delay the validation of
>     pre-existing data until your database is not under heavy load.

That's good as far as it goes, but there's another major reason for
wanting the capability.  There are cases where, due to dirty
converted data or late recognition of the need for a constraint
there is existing data which violates the constraint.  Being able
to add a constraint to protect against creating additional bad data
before all the existing problems are cleaned up can be valuable.

I know of cases where the old rows which violate the constraint may
persist for decades.  In a court setting they may care a lot that
data on a new and active court case is forced to be correct when
entered, but may not feel that it is worth the cost (or in some
cases even possible) to determine correct values for cases which
are 10 or 20 (or 120) years old.  These cases can't be deleted due
to records retention laws or relevant court rules, and for a DBA to
update them with bogus values to make them comply with the
constraint would be not only potentially more misleading than
leaving them alone, but would be a felony.

> But I'm not going to object to simply removing the entire sentence.

I don't know -- it seems like the reasons for the feature are
non-obvious enough that such a note is useful.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company