Re: Fast AT ADD COLUMN with DEFAULTs

On 2016-10-05 11:58:33 -0700, Serge Rielau wrote:
> Dear Hackers,
> I’m working on a patch that expands PG’s ability to add columns to a table without a table rewrite (i.e. at O(1)
cost)from the nullable-without-default to a more general case. E.g. CREATE TABLE T(pk INT NOT NULL PRIMARY KEY); INSERT
INTOT VALEUS (1), (2), (3); ALTER TABLE T ADD COLUMN c1 INTEGER NOT NULL DEFAULT 5; INSERT INTO T VALUES (4, DEFAULT);
ALTERTABLE T ALTER COLUMN SET DEFAULT 6; INSERT INTO T VALUS (5, DEFAULT); SELECT * FROM T ORDER BY pk; => (1, 5), (2,
5),(3, 5), (4, 5), (5, 6);
 
> Rows 1-3 have never been updated, yet they know that their values of c1 is 5.
> The requirement is driven by large tables for which add column takes too much time and/or produces too large a
transactionfor comfort.
 
> In simplified terms: * a second “exist default” is computed and stored in the catalogs at time of AT ADD COLUMN * The
existdefault is cached in the tuple descriptor (e.g in attrdef) * When one of the getAttr or copytuple related routines
isinvoked the exist default is filled in instead of simply NULL padding if the tuple is shorter the requested attribute
number.

If I understand this proposal correctly, altering a column default will
still have trigger a rewrite unless there's previous default?