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
