Re: Fast ALTER TABLE ... ADD COLUMN ... DEFAULT xxx?

Greg Stark <stark(at)enterprisedb(dot)com> writes:
> On Thu, May 21, 2009 at 4:22 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Yeah ... I don't see exactly what it would buy to restrict it to just
>> the first such value.

> Well it wouldn't buy you steady-state space savings or performance improvements.

> What it would buy you is a much narrowed set of circumstances where
> ALTER TABLE ADD COLUMN goes from a fast O(1) catalog change to a
> complete table rewrite. The use cases covered such as "boolean DEFAULT
> false" or "integer DEFAULT 0" are extremely common.

No, you missed my point --- what's the value of having an implementation
of this that only works for one column? If we do it, I'd envision it
as an extra column in pg_attribute, and it would work for any column(s).
There's nothing to be gained by restricting it.

> I think Robert Haas is right that we could handle any stable
> expression by evaluating the expression once and storing only the
> final resulting value as a constant. That would avoid the problems
> with dependencies and later changes to functions.

Right, that's *necessary* to avoid changing semantics compared to
the non-optimized behavior.

> Another gotcha is that the default value might be very large.... It
> can't be very common but I suppose we would have to take some care
> around that.

Yeah, that occurred to me too. We'd probably not be able to toast
the pg_attribute column (depending on exactly how it's
declared/represented) so we'd have to put a limit on the width of
data value that we'd be willing to handle this way. Seems doable
though.

regards, tom lane