Fast AT ADD COLUMN with DEFAULTs

From: Serge Rielau <serge(at)rielau(dot)com>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Fast AT ADD COLUMN with DEFAULTs
Date: 2016-10-05 18:58:33
Message-ID: 90c85206-f465-472f-b782-5a6412d217f3@rielau.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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 INTO T VALEUS (1), (2), (3); ALTER TABLE T ADD COLUMN c1 INTEGER NOT NULL DEFAULT 5; INSERT INTO T VALUES (4, DEFAULT); ALTER TABLE 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 transaction for comfort.
In simplified terms: * a second “exist default” is computed and stored in the catalogs at time of AT ADD COLUMN * The exist default is cached in the tuple descriptor (e.g in attrdef) * When one of the getAttr or copytuple related routines is invoked the exist default is filled in instead of simply NULL padding if the tuple is shorter the requested attribute number.

Is there an interest in principle in the community for this functionality?
Cheers Serge Rielau Salesforce.com
via Newton Mail [https://cloudmagic.com/k/d/mailapp?ct=dx&cv=9.0.74&pv=10.11.6&source=email_footer_2]

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2016-10-05 19:02:09 Re: Our "fallback" atomics implementation doesn't actually work
Previous Message Heikki Linnakangas 2016-10-05 18:57:26 Re: PostgreSQL - Weak DH group