<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:x="urn:schemas-microsoft-com:office:excel" 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 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 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;}
/* 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;}
span.EmailStyle17
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page Section1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.Section1
{page:Section1;}
-->
</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-AU link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><span lang=EN-GB>I avoid alter table .. add column.<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><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"'> info-ingres-bounces@lists.planetingres.org
[mailto:info-ingres-bounces@lists.planetingres.org] <b>On Behalf Of </b>Martin
Bowes<br>
<b>Sent:</b> Tuesday, 17 October 2017 6:30 PM<br>
<b>To:</b> info-ingres@lists.planetingres.org<br>
<b>Subject:</b> [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><span lang=EN-GB>Hi All,<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>II 10.2.0 (a64.lnx/100) +p15162<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>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></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>For example…<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>create table test_alter(a integer4 not null
with default) with nojournaling, page_size=8192;<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>insert into test_alter values(1), (2), (3);<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>modify test_alter to btree unique on a with
compression;<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>alter table test_alter add column b
integer4 not null with default -1;<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>modify test_alter to reconstruct;<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>select * from test_alter;<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB style='font-family:"Courier New"'><o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB style='font-family:"Courier New"'>a
b <o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB style='font-family:"Courier New"'><o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB style='font-family:"Courier New"'>
1 0<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB style='font-family:"Courier New"'>
2 0<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB style='font-family:"Courier New"'>
3 0<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB style='font-family:"Courier New"'><o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>(3 rows)<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>insert into test_alter(a) values (4);<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>select * from test_alter;<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB style='font-family:"Courier New"'><o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB style='font-family:"Courier New"'>a
b <o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB style='font-family:"Courier New"'><o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB style='font-family:"Courier New"'>
1 0<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB style='font-family:"Courier New"'>
2 0<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB style='font-family:"Courier New"'>
3 0<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB style='font-family:"Courier New"'>
4 -1<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB style='font-family:"Courier New"'><o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>(4 rows)<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>The same problem occurs if I’d used
compression = (nokey, data).<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>If you run the above but this time with no
compression then the final select will show the expected data:<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB style='font-family:"Courier New"'><o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB style='font-family:"Courier New"'>a
b <o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB style='font-family:"Courier New"'><o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB style='font-family:"Courier New"'>
1
-1<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB style='font-family:"Courier New"'>
2 -1<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB style='font-family:"Courier New"'>
3 -1<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB style='font-family:"Courier New"'>
4 -1<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB style='font-family:"Courier New"'><o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>(4 rows)<o:p></o:p></span></p>
</div>
</body>
</html>