<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=koi8-r">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
@font-face
{font-family:"MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:"\@MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{mso-style-priority:99;
mso-style-link:"Balloon Text Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Tahoma","sans-serif";}
span.EmailStyle17
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.BalloonTextChar
{mso-style-name:"Balloon Text Char";
mso-style-priority:99;
mso-style-link:"Balloon Text";
font-family:"Tahoma","sans-serif";}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-GB" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="color:#1F497D">We have seen some other issues with alter table, but by and large it’s been very useful to us.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">I’ve raised a bug on this issue.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> Paul White [mailto:paul.white@shift7solutions.com.au]
<br>
<b>Sent:</b> 17 October 2017 09:43<br>
<b>To:</b> info-ingres@lists.planetingres.org<br>
<b>Subject:</b> Re: [Info-ingres] Compression weighs heavy on my defaults.<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I avoid alter table .. add column.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">
<a href="mailto:info-ingres-bounces@lists.planetingres.org">info-ingres-bounces@lists.planetingres.org</a> [<a href="mailto:info-ingres-bounces@lists.planetingres.org">mailto:info-ingres-bounces@lists.planetingres.org</a>]
<b>On Behalf Of </b>Martin Bowes<br>
<b>Sent:</b> Tuesday, 17 October 2017 6:30 PM<br>
<b>To:</b> <a href="mailto:info-ingres@lists.planetingres.org">info-ingres@lists.planetingres.org</a><br>
<b>Subject:</b> [Info-ingres] Compression weighs heavy on my defaults.<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><span lang="EN-AU"><o:p> </o:p></span></p>
<p class="MsoNormal">Hi All,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">II 10.2.0 (a64.lnx/100) +p15162<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">If we alter table add column specifying the column default then we expect the existing rows in the table to all have the specified default as their entry. And that seems to be the case…unless the table has compression in which case the
data default seems to be used instead. Any new data added to the table will use the correct default.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">For example…<o:p></o:p></p>
<p class="MsoNormal">create table test_alter(a integer4 not null with default) with nojournaling, page_size=8192;<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">insert into test_alter values(1), (2), (3);<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">modify test_alter to btree unique on a with compression;<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">alter table test_alter add column b integer4 not null with default -1;<o:p></o:p></p>
<p class="MsoNormal">modify test_alter to reconstruct;<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">select * from test_alter;<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span style="font-family:"Courier New""><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">a b <o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> 1 0<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> 2 0<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> 3 0<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""><o:p></o:p></span></p>
<p class="MsoNormal">(3 rows)<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">insert into test_alter(a) values (4);<o:p></o:p></p>
<p class="MsoNormal">select * from test_alter;<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span style="font-family:"Courier New""><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">a b <o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> 1 0<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> 2 0<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> 3 0<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> 4 -1<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""><o:p></o:p></span></p>
<p class="MsoNormal">(4 rows)<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">The same problem occurs if I’d used compression = (nokey, data).<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">If you run the above but this time with no compression then the final select will show the expected data:<o:p></o:p></p>
<p class="MsoNormal"><span style="font-family:"Courier New""><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">a b <o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> 1 -1<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> 2 -1<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> 3 -1<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> 4 -1<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""><o:p></o:p></span></p>
<p class="MsoNormal">(4 rows)<o:p></o:p></p>
</div>
</body>
</html>