Re: Fast AT ADD COLUMN with DEFAULTs

<img class="cloudmagic-smart-beacon" height="0"
src="https://tr.cloudmagic.com/h/v6/emailtag/tag/1475708722/647b813270dffd4c4b2631f3c9eb3f46/9cf3801d03770ada01bb39dc8f52321d/c143e7051e30d22af27905a24303de11/9efab2399c7c560b34de477b9aa0a465/ufo.gif"
style="border:0;width:0; height:0; overflow:hidden;" width="0" /><div dir="auto"><br /><div class="cm_footer"
id="cm_footer"><divid="cm_sent_from">via <a
href="https://cloudmagic.com/k/d/mailapp?ct=dx&cv=9.0.74&pv=10.11.6&source=email_footer_2">Newton
Mail</a></div></div><span><br/></span><div id="cm_replymail_content_wrap"><div class="">On Wed, Oct 5, 2016 at 3:58 PM,
TomLane <tgl@sss.pgh.pa.us> wrote:<br /><div id="cm_replymail_content_1475708366" style="overflow:
visible;"><blockquotestyle="margin:0;border-left: #D6D6D6 1px solid;padding-left: 10px;">Andres Freund
<andres@anarazel.de>writes:<br />> On 2016-10-05 15:44:56 -0700, Jeff Janes wrote:<br />>>> No, "a
second“exist default"" was mentioned, i.e. it is an additional<br />>>> column in a system table
(pg_attribute)as default column values of<br />>>> the "pre-alter" era. It solves changing of the default
expressionof<br />>>> the same column later.<br /><br />> Don't think that actually solves the issue. The
defaultmight be unset<br />> for a while, for example. Essentially you'd need to be able to associate<br />>
arbitrarynumber of default values with an arbitrary set of rows.<br /><br />I think it does work, as long as the
"existsdefault" is immutable.<br />(For safety, personally, I'd restrict it to be a verbatim constant.)<br />The point
isthat you apply that when you are reading a row that has<br />so few columns that it must predate the original ALTER
TABLEADD COLUMN.<br />Subsequent ADD/DROP/SET DEFAULT affect the "active default" and hence<br />insertions that happen
afterthem, but they don't affect the<br />interpretation of old rows. And of course all rows inserted after the<br
/>ADDCOLUMN contain explicit values of the column, so their meaning is<br />unaffected in any
case.</blockquote><blockquoteclass="" style="margin: 0px; border-left-color: rgb(214, 214, 214); border-left-width:
1px;border-left-style: solid; padding-left: 10px;"><br />You do need two defaults associated with a column to make this
work.<br/>The "exists default" never changes after the column is added. But<br />in principle, the "exists default"
justreplaces the NULL value that<br />we implicitly insert now in such cases.</blockquote><div
id="ID_1475708435309">Explainedso much better than I could do it :-)</div><div id="ID_1475708435309"><br /></div><div
id="ID_1475708435309">Iwant to point out as a minor “extension” that there is no need for the default to be immutable.
Itis merely required that the default is evaluate at time of ADD COLUMN</div><div id="ID_1475708435309">and then we
rememberthe actual value for the exist default, rather than the parsed expression as we do for the “current”
default.</div><divid="ID_1475708435309"> </div><blockquote class="" style="margin: 0px; border-left-color: rgb(214,
214,214); border-left-width: 1px; border-left-style: solid; padding-left: 10px;">Need a better name for the concept,
sinceevidently this name isn't<br />conveying the idea.</blockquote><div id="ID_1475708551097">By all means. Got
anythingin mind?</div><div id="ID_1475708551097"><br /></div><div id="ID_1475708551097">Cheers</div><div
id="ID_1475708551097">SergeRielau</div><div id="ID_1475708551097"><br /></div></div></div></div></div>