<div dir="ltr"><div dir="ltr">The Ingres 11.2 documentation has been updated. The maximum row size has reduced further. <div><br></div><div><img src="cid:ii_kp7m02yb1" alt="image.png" width="337" height="268"><br></div><div><br></div><div>Check out Upgrade.pdf for recommendations about modifying tables to adjust page size.</div><div><br></div><div>Here is an SQL to search for tables with a row width near the boundary</div><div><br></div><div>select t.system_use, t.number_pages, t.num_rows, t.row_width,<br>t.table_pagesize, t.table_name, t.table_owner, t.table_type, f.file_name,<br>t.storage_structure, t.overflow_pages, t.create_date<br>from iitables t<br>join iifile_info f on t.table_name = f.table_name and t.table_owner = f.owner_name<br>where<br>(t.table_pagesize = 2048 and t.row_width >= 1928 ) or<br>(t.table_pagesize = 4096 and t.row_width >= 3976 ) or<br>(t.table_pagesize = 8192 and t.row_width >= 8072 ) or<br>(t.table_pagesize = 16384 and t.row_width >= 16264 ) or<br>(t.table_pagesize = 32768 and t.row_width >= 32648 ) or<br>(t.table_pagesize = 65536 and t.row_width >= 65416 )<br>order by system_use, t.table_owner, t.table_name<br></div><div><br></div><div>Paul</div><div><br></div><div><br></div><div><br></div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Mar 22, 2021 at 3:13 PM <<a href="mailto:shift7solutions@gmail.com">shift7solutions@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div>
<pre>Hi all,</pre>
<pre>Can anyone say when the maximum row size per page has changed?
</pre>
<pre>I have several btree tables with warning messages in verifydb like this:
</pre>
<pre><font size="-1" face="Courier New, Courier, monospace">verifydb -mreport -sdbname ctrust -otable newtable -n
W_DM5066_ROW_SPANS_PAGE Table row size 2004 spans page size 2048.</font></pre>
<pre>My normal approach is to increase the page size to 4K or 8K to suit MVCC requirements,
so I am seeking to understand why this particular table is being reported as a warning when the row
width is within the specified limits here:</pre>
<pre>V10.2
<a href="https://docs.actian.com/ingres/10.2/index.html#page/DatabaseAdmin/Maximum_Row_Size_Per_Page_Size.htm" target="_blank">https://docs.actian.com/ingres/10.2/index.html#page/DatabaseAdmin/Maximum_Row_Size_Per_Page_Size.htm</a>
V11.x
<a href="https://docs.actian.com/ingres/11.0/index.html#page/DatabaseAdmin%2FCalculate_Space_Requirements_When_Rows_Span_Page.htm%23ww1027421" target="_blank">https://docs.actian.com/ingres/11.0/index.html#page/DatabaseAdmin%2FCalculate_Space_Requirements_When_Rows_Span_Page.htm%23ww1027421</a>
<img src="cid:179b06fc74f99ac2da71" alt="" width="755" height="496">
On further investigation, I found these to be the maximum row sizes per page before page spanning occurs.
</pre>
<table cellspacing="0" border="0">
<colgroup width="60" span="3"></colgroup> <tbody>
<tr>
<td height="17" align="left"><font face="Courier New">Page</font></td>
<td align="right"><font face="Courier New">Old</font></td>
<td align="right"><font face="Courier New">New</font></td>
</tr>
<tr>
<td height="17" align="left"><font face="Courier New">2K</font></td>
<td align="right"><font face="Courier New">2008</font></td>
<td align="right"><font face="Courier New">1932</font></td>
</tr>
<tr>
<td height="17" align="left"><font face="Courier New">4K</font></td>
<td align="right"><font face="Courier New">3988</font></td>
<td align="right"><font face="Courier New">3980</font></td>
</tr>
<tr>
<td height="17" align="left"><font face="Courier New">8K</font></td>
<td align="right"><font face="Courier New">8084</font></td>
<td align="right"><font face="Courier New">8076</font></td>
</tr>
<tr>
<td height="17" align="left"><font face="Courier New">16K</font></td>
<td align="right"><font face="Courier New">16276</font></td>
<td align="right"><font face="Courier New">16268</font></td>
</tr>
<tr>
<td height="17" align="left"><font face="Courier New">32K</font></td>
<td align="right"><font face="Courier New">32660</font></td>
<td align="right"><font face="Courier New">32652</font></td>
</tr>
<tr>
<td height="17" align="left"><font face="Courier New">64K</font></td>
<td align="right"><font face="Courier New">65428</font></td>
<td align="right"><font face="Courier New">65420</font></td>
</tr>
</tbody>
</table>
<pre>Incidently, I found two Ingres catalog tables with 2K storage and row size > 1932: ii_encoded_forms and ii_encodings. I am pretty sure they are not used in any of the customer databases I'm investigating. It seems that upgradedb (V10/V11) has missed these. In newly created databases, these tables are 8K.
Paul
&
</pre>
</div>
</blockquote></div><br clear="all"><div><br></div>-- <br><div dir="ltr" class="gmail_signature"><div dir="ltr"><div><div dir="ltr"><span style="font-family:arial">Paul White</span><br style="font-family:arial">& Shift Seven Solutions<br>Mob <a value="+61414681799" style="color:rgb(17,85,204)">0414 681 799</a><br>Ph <a value="+61754482137" style="color:rgb(17,85,204)">07 5448 2137</a><div>Em <a href="mailto:paul.white@shift7solutions.com.au" target="_blank">paul.white@shift7solutions.com.au</a></div><div><div style="font-family:arial">Web <a href="http://www.shift7solutions.com.au/" style="color:rgb(17,85,204)" target="_blank">www.shift7solutions.com.au</a></div><div style="font-family:arial"><span style="font-family:Arial,Helvetica,sans-serif">Mail PO Box 418 Pomona 4568</span></div></div></div></div></div></div></div>