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

The following bug has been logged on the website:

Bug reference:      13195
Logged by:          Dave Rolsky
Email address:      autarch@urth.org
PostgreSQL version: 9.2.10
Operating system:   Linux
Description:

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.

Ideally, the "DEFAULT NULL" would be optimized away. But maybe the two
statements really are different in some way I can't see, in which case it'd
be great if Pg could detect this case and warn that this will take forever
with a suggestion that you can drop the "DEFAULT NULL (see X
documentation)."

Cheers,

-dave

On 30 April 2015 at 04:56, <autarch@urth.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