Re: [COMMITTERS] pgsql: Remove arbitrary ALTER TABLE .. ADD COLUMN restriction.

On Wed, Jan 26, 2011 at 4:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> Considering the number of OTHER places we'd have to break backward
>> compatibility, one more wouldn't bother me any, but apparently that's
>> just me.
>
> Well, again, it'd be all right with me if we were going to get any
> meaningful increment in functionality out of it, but we aren't.  If you
> add the column and the default in separate steps, you get the same
> result and all the behavior is spec-compliant.
>
> There's some history here that you may not be familiar with --- we used
> to have exactly this bug in regards to the mainline ALTER TABLE case.
> Observe the results in PG 7.1:
>
> regression=# create table foo(f1 int);
> CREATE
> regression=# insert into foo values(1);
> INSERT 3151259 1
> regression=# insert into foo values(2);
> INSERT 3151260 1
> regression=# alter table foo add column f2 int default 2;
> ALTER
> regression=# select * from foo;
>  f1 | f2
> ----+----
>  1 |
>  2 |
> (2 rows)
>
> In 7.2 through 7.4 the ALTER failed instead:
> regression=# alter table foo add column f2 int default 2;
> ERROR:  Adding columns with defaults is not implemented.
>        Add the column, then use ALTER TABLE SET DEFAULT.
>
> and by 8.0 we'd finally made it work per spec.  But we got lots of flak
> meanwhile from people who'd gotten used to the old behavior.  So those
> of us who were around then aren't eager to repeat that.  The code you're
> complaining about now was put in only a month after we got that problem
> fixed, so the issue was plenty fresh in mind at the time.

Yeah, I wasn't aware of that.  I'll go revert, but I think I'll also
add a big fat comment, because this is entirely non-obvious,
especially because we don't disallow the cases SET NOT NULL and ADD
table_constraint, which have the same darn problem.  Aren't people who
are used to those cases going to be pretty surprised too?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company