Tom Lane <tgl@sss.pgh.pa.us> writes:
> Jerry Sievers <jerry@jerrysievers.com> writes:
>
> > I believe what's happening here is that the server doesn't realize
> > that the new column is going to have all nulls and that the check
> > constraint allows nulls. As such, the check evidently is being
> > evaluated for each row of the table.
>
> Yup, that's right. There are some corner cases that make that harder to
> optimize than it might look:
>
> * volatile functions in the constraint might possibly deliver different
> answers at different rows
Understood.
> * if table is in fact empty, we should not throw an error, nor indeed
> evaluate the constraint even once (again, volatile functions...)
The table is big, the check constraint is trivial and the col values
will be all null. This is a tempting hack-around case.
Think I'm going to hide the constraint by temporarily toggling to zero
the contypid field in pg_constraint, around the alter table add column
statement. I've tested this and it allows the alter to happen fast.
Thanks for the information.
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
--
-------------------------------------------------------------------------------
Jerry Sievers 732 365-2844 (work) Production Database Administrator
305 321-1144 (mobil WWW E-Commerce Consultant