Re: BUG #13195: ALTER TABLE with DEFAULT NULL is different than implicit default

On 30 April 2015 at 04:56, <autarch(at)urth(dot)org> wrote:

> If I have a very large table (or set of partitioned tables) and run "ALTER
> TABLE foo ADD COLUMN bar text DEFAULT NULL", Pg takes several hours to
> complete this statement. If I simply remove the "DEFAULT NULL" clause, I
> will get what seems to be the exact same outcome in a fraction of a second.
>
>
Hi Dave,

I'm not quite sure why you're seeing this, as from looking at the code it
appears to optimise this properly.

I've also tested with:

david=# create table tt (id int not null);
CREATE TABLE
Time: 5.026 ms
david=# insert into tt select x.x from generate_series(1,50000000)x(x);
INSERT 0 50000000
Time: 130046.906 ms
david=# alter table tt add column bar text default null;
ALTER TABLE
Time: 31.459 ms
david=# alter table tt add column bar1 text;
ALTER TABLE
Time: 1.078 ms
david=# alter table tt add column bar2 text default null;
ALTER TABLE
Time: 0.954 ms
david=# select version();
version
--------------------------------------------------------------
PostgreSQL 9.2.10, compiled by Visual C++ build 1700, 64-bit

If you're actually testing with a domain type rather than a built in type,
then I could see why you'd get the slow down.

Try:

david=# create domain mytexttype as text;
CREATE DOMAIN
Time: 1.408 ms
david=# alter table tt add column bar5 mytexttype default null;
ALTER TABLE
Time: 34141.624 ms

Notice it took 34 seconds that time.

This is explained in the following code comment:

* If there is no default, Phase 3 doesn't have to do anything, because
* that effectively means that the default is NULL. The heap tuple access
* routines always check for attnum > # of attributes in tuple, and return
* NULL if so, so without any modification of the tuple data we will get
* the effect of NULL values in the new column.
*
* An exception occurs when the new column is of a domain type: the domain
* might have a NOT NULL constraint, or a check constraint that indirectly
* rejects nulls. If there are any domain constraints then we construct
* an explicit NULL default value that will be passed through
* CoerceToDomain processing. (This is a tad inefficient, since it causes
* rewriting the table which we really don't have to do, but the present
* design of domain processing doesn't offer any simple way of checking
* the constraints more directly.)

Are you perhaps actually using a domain type and not text?
Do you see the problem with my test case?

Regards

David Rowley