Re: Fast AT ADD COLUMN with DEFAULTs - Mailing list pgsql-hackers

<img class="cloudmagic-smart-beacon" height="0"
src="https://tr.cloudmagic.com/h/v6/emailtag/tag/1475704591/647b813270dffd4c4b2631f3c9eb3f46/9cf3801d03770ada01bb39dc8f52321d/eb0a7f22160b433d9b4d35634754e347/9efab2399c7c560b34de477b9aa0a465/ufo.gif"
style="border:0;width:0; height:0; overflow:hidden;" width="0" /><div dir="auto"><div
id="cm_replymail_content_wrap"><divclass="">On Wed, Oct 5, 2016 at 2:45 PM, Vitaly Burovoy
<vitaly.burovoy@gmail.com>wrote:<br /><div id="cm_replymail_content_1475704139" style="overflow:
visible;"><blockquotestyle="margin:0;border-left: #D6D6D6 1px solid;padding-left: 10px;">On 10/5/16, Serge Rielau
<serge@rielau.com>wrote:<br />> Dear Hackers,<br />><br />> I’m working on a patch that expands PG’s
abilityto add columns to a table<br />> without a table rewrite (i.e. at O(1) cost) from the<br />>
nullable-without-defaultto a more general case. E.g.<br />...<br />> Is there an interest in principle in the
communityfor this functionality?<br /><br />Wow! I think it would be great! It also solves huge vacuuming after<br
/>rewritingthe table(s).<br />Just pay attention to corner cases like indexes, statistics and speed.</blockquote><div
id="ID_1475704192987">Yes, Yes, and still analyzing speed</div><blockquote class="" style="margin: 0px;
border-left-color:rgb(214, 214, 214); border-left-width: 1px; border-left-style: solid; padding-left: 10px;"><br />But
I'dlike to see solution for more important cases like:<br />CREATE TABLE t (pk INT NOT NULL PRIMARY KEY);<br />INSERT
INTOt VALUES (1), (2), (3);<br />ALTER TABLE t ADD COLUMN c1 timestamptz NOT NULL DEFAULT 'now';<br />SELECT * FROM t
ORDERBY pk;<br />ALTER TABLE t ADD COLUMN c2 serial;<br />SELECT * FROM t ORDER BY pk;<br />INSERT INTO t(pk) VALUES
(4);<br/>SELECT * FROM t ORDER BY pk;</blockquote><div id="ID_1475704277131">By solution I think you mean a semantic
changefrom what it is doing today which is:</div><div id="ID_1475704277131">* “Now” is fixed to ALTER TABLE time for
allpre-existing rows</div><div id="ID_1475704277131">* serial will fill in the same value for all pre-existing
rows</div><divid="ID_1475704277131">Having different semantics for those would require a rewrite and probably different
syntaxin some form.</div><div id="ID_1475704277131"><br /></div><div id="ID_1475704277131">This is what my patch does
onour PG derivative today: </div><blockquote class="" style="margin: 0px; border-left-color: rgb(214, 214, 214);
border-left-width:1px; border-left-style: solid; padding-left: 10px;"></blockquote><div id="ID_1475704251970">CREATE
TABLEt (pk INT NOT NULL PRIMARY KEY);</div><div id="ID_1475704251970">CREATE TABLE</div><div
id="ID_1475704251970">postgres=#INSERT INTO t VALUES (1), (2), (3);</div><div id="ID_1475704251970">INSERT 0
3</div><divid="ID_1475704251970">postgres=# ALTER TABLE t ADD COLUMN c1 timestamptz NOT NULL DEFAULT 'now';</div><div
id="ID_1475704251970">ALTERTABLE</div><div id="ID_1475704251970">postgres=# SELECT * FROM t ORDER BY pk;</div><div
id="ID_1475704251970"> pk|              c1               </div><div
id="ID_1475704251970">----+-------------------------------</div><divid="ID_1475704251970">  1 | 2016-10-05
21:47:58.919194+00</div><divid="ID_1475704251970">  2 | 2016-10-05 21:47:58.919194+00</div><div id="ID_1475704251970"> 
3| 2016-10-05 21:47:58.919194+00</div><div id="ID_1475704251970">(3 rows)</div><div id="ID_1475704251970"><br
/></div><divid="ID_1475704251970">postgres=# </div><div id="ID_1475704251970">postgres=# ALTER TABLE t ADD COLUMN c2
serial;</div><divid="ID_1475704251970">SELECT * FROM t ORDER BY pk;</div><div id="ID_1475704251970">INSERT INTO t(pk)
VALUES(4);</div><div id="ID_1475704251970">SELECT * FROM t ORDER BY pk;</div><div id="ID_1475704251970"><br
/></div><divid="ID_1475704251970">ALTER TABLE t ADD COLUMN c2 serial;</div><div id="ID_1475704251970">ALTER
TABLE</div><divid="ID_1475704251970">postgres=# SELECT * FROM t ORDER BY pk;</div><div id="ID_1475704251970"> pk |    
        c1               | c2 </div><div id="ID_1475704251970">----+-------------------------------+----</div><div
id="ID_1475704251970"> 1 | 2016-10-05 21:47:58.919194+00 |  1</div><div id="ID_1475704251970">  2 | 2016-10-05
21:47:58.919194+00|  1</div><div id="ID_1475704251970">  3 | 2016-10-05 21:47:58.919194+00 |  1</div><div
id="ID_1475704251970">(3rows)</div><div id="ID_1475704251970"><br /></div><div id="ID_1475704251970">postgres=# INSERT
INTOt(pk) VALUES (4);</div><div id="ID_1475704251970">INSERT 0 1</div><div id="ID_1475704251970">postgres=# SELECT *
FROMt ORDER BY pk;</div><div id="ID_1475704251970"> pk |              c1               | c2 </div><div
id="ID_1475704251970">----+-------------------------------+----</div><divid="ID_1475704251970">  1 | 2016-10-05
21:47:58.919194+00|  1</div><div id="ID_1475704251970">  2 | 2016-10-05 21:47:58.919194+00 |  1</div><div
id="ID_1475704251970"> 3 | 2016-10-05 21:47:58.919194+00 |  1</div><div id="ID_1475704251970">  4 | 2016-10-05
21:47:58.919194+00|  2</div><div id="ID_1475704251970">(4 rows)</div><div id="ID_1475704251970"> </div><blockquote
class=""style="margin: 0px; border-left-color: rgb(214, 214, 214); border-left-width: 1px; border-left-style: solid;
padding-left:10px;">P.S.: I really think it is a good idea, just some research is<br />necessary and covering corner
cases...</blockquote><divid="ID_1475704460956">Thanks.</div><div id="ID_1475704460956">This would be my first
contribution. </div><divid="ID_1475704460956">I take it I would post a patch based on a recent PG 9.6 master for
review?</div><divid="ID_1475704460956">Or should I compose some sort of a design document? </div><div
id="ID_1475704460956"><br/></div><div id="ID_1475704460956">Cheers</div><div id="ID_1475704460956">Serge
Rielau</div><divid="ID_1475704460956">Salesforce.com</div></div></div></div></div>