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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> It's certainly not obvious from the archives from around 2004-06-06
> that this was discussed. Perhaps you could be a bit more specific.
> As for the spec, if it requires composite types to have defaults (or
> constraints), then we're in violation of that all over the place
> anyway.

Here's the point: the spec requires that ADD COLUMN with a default cause
the column to spring into existence with the default value inserted in
all existing rows, like this:

regression=# create table foo (f1 int);
CREATE TABLE
regression=# insert into foo values (1),(2);
INSERT 0 2
regression=# alter table foo add column f2 text default 'hello';
ALTER TABLE
regression=# select * from foo;
f1 | f2
----+-------
1 | hello
2 | hello
(2 rows)

This is entirely different from what happens when you set the default
afterwards:

regression=# create table foo (f1 int);
CREATE TABLE
regression=# insert into foo values (1),(2);
INSERT 0 2
regression=# alter table foo add column f2 text ;
ALTER TABLE
regression=# select * from foo;
f1 | f2
----+----
1 |
2 |
(2 rows)

regression=# alter table foo alter column f2 set default 'hello';
ALTER TABLE
regression=# select * from foo;
f1 | f2
----+----
1 |
2 |
(2 rows)

In this case the column springs into existence as nulls, and the
subsequent change of default doesn't change that.

We are currently only capable of supporting the second behavior so far
as instances of the rowtype outside the table itself are concerned.
Eventually we should try to fix that --- and by "fix", I mean support
the spec-required behavior, not implement whatever happens to be easy.
The reason for rejecting the syntax with default is to avoid
establishing a non-spec-compliant precedent that we'd then have to
worry about being backward compatible with.

What your patch does is accept the syntax with ensuing
non-spec-compliant behavior. That's not a step forward. If it added
any really useful functionality, then maybe there would be an excuse for
violating the spec here --- but it doesn't. You can just add the column
without default and change the default afterwards, and get to the same
place without using any non-spec-compliant operations.

And yes, I know that we're not doing all that well with honoring
defaults (or constraints) for rowtypes. But that's something to be
fixed. Enlarging our non-compliance with the spec to gain no useful
functionality isn't an improvement.

regards, tom lane