This is a multi-part message in MIME format.
------_=_NextPart_001_01C8E7EC.8903A999
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Hi Martin,
The text ****tion is just there for information and the real information
used is the CX data. There are a number of actions that if done can
invalidate a view. There is no link retained with the underlying
metadata so as you have seen, the view is vulnerable to certain types of
structure change. It would be feasible to write a script or code to
refresh a views CX data based on the stored text, the CX data is stored
in a column too which can be updated. The more nutty problem to crack
though would be the detecting of the dependency in the first place and
then either blocking the action or, if valid, refre****ng.
Regards,
Ian
=20
________________________________
From: info-ingres-bounces@[EMAIL PROTECTED]
On Behalf Of
Martin Bowes
Sent: 17 July 2008 09:36
To: Ingres and related product discussion forum
Subject: [Info-Ingres] How much does a view remember?
=20
Hi Everyone,
=20
How much does a view remember about the columns involved in its
definition?
=20
I'd always thought next to nothing as a the view definition was stored
as a text unit and not as a compiled expression....
=20
Try this...
sql iidbdb << SQL_END
\r
set autocommit on;
\p\g
drop table xxx;
\p\g
create table xxx(a_string varchar(10) not null not default)
with nojournaling, page_size=3D4096;
=20
create view xxx_view as select a_string from xxx;
=20
insert into xxx values('a string');
insert into xxx values('a string 2');
insert into xxx values('a string 3');
\p\g
select * from xxx_view;
/* The strings will be displayed OK! */
\p\g
alter table xxx alter column a_string varchar(25) not null not default;
/* So we've made the column wider and not redefined the view */
\p\g
insert into xxx values('a much longer string');
/* Inserted a longer string */
\p\g
select * from xxx_view;
/* And the view retains the varchar(10) information, and the longer
string
* appears to be corrupted - or truncated - depending on whether the
* extraction is done with simple SQL or with something more
complicated!
*/
\p\g
\q
SQL_END
=20
Is this a bug?
=20
Martin Bowes
------_=_NextPart_001_01C8E7EC.8903A999
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns=3D"http://www.w3.org/TR/REC-html40"
xmlns:ns1=3D"http://schemas.microsoft.com/office/2004/12/omml">
<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
..shape {behavior:url(#default#VML);}
</style>
<![endif]-->
<style>
<!--a:link
{mso-style-priority:99;}
span.MSOHYPERLINK
{mso-style-priority:99;}
a:visited
{mso-style-priority:99;}
span.MSOHYPERLINKFOLLOWED
{mso-style-priority:99;}
/* Font Definitions */
@[EMAIL PROTECTED]
11 6 4 3 5 4 4 2 4;}
@[EMAIL PROTECTED]
0 0 0 0 0 0 0 0 0;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:Calibri;}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal;
font-family:Calibri;
color:windowtext;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:Arial;
color:navy;}
@[EMAIL PROTECTED]
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=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3DEN-US link=3Dblue vlink=3Dpurple>
<div class=3DSection1>
<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>Hi =
Martin,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>The text ****tion is just there for
information and the real information used is the CX data. There are a =
number of
actions that if done can invalidate a view. There is no link retained =
with the
underlying metadata so as you have seen, the view is vulnerable to =
certain
types of structure change. It would be feasible to write a script or =
code to
refresh a views CX data based on the stored text, the CX data is stored =
in a
column too which can be updated. The more nutty problem to crack though =
would
be the detecting of the dependency in the first place and then either =
blocking
the action or, if valid, refre****ng.<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>Regards,<o:p></o:p></span></font></p=
>
<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>Ian<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<div>
<div class=3DMsoNormal align=3Dcenter style=3D'text-align:center'><font =
size=3D3
face=3D"Times New Roman"><span =
style=3D'font-size:12.0pt;font-family:"Times New Roman"'>
<hr size=3D2 width=3D"100%" align=3Dcenter tabindex=3D-1>
</span></font></div>
<p class=3DMsoNormal><b><font size=3D2 face=3DTahoma><span =
style=3D'font-size:10.0pt;
font-family:Tahoma;font-weight:bold'>From:</span></font></b><font =
size=3D2
face=3DTahoma><span style=3D'font-size:10.0pt;font-family:Tahoma'> =
info-ingres-bounces@[EMAIL PROTECTED]
<b><span
style=3D'font-weight:bold'>On Behalf Of </span></b>Martin Bowes<br>
<b><span style=3D'font-weight:bold'>Sent:</span></b> 17 July 2008 =
09:36<br>
<b><span style=3D'font-weight:bold'>To:</span></b> Ingres and related =
product
discussion forum<br>
<b><span style=3D'font-weight:bold'>Subject:</span></b> [Info-Ingres] =
How much
does a view remember?</span></font><font size=3D3 face=3D"Times New =
Roman"><span
style=3D'font-size:12.0pt;font-family:"Times New =
Roman"'><o:p></o:p></span></font></p>
</div>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span =
style=3D'font-size:11.0pt'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>Hi Everyone,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>How much does a view remember about the columns involved in its
definition?<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>I'd always thought next to nothing as a the view definition was =
stored
as a text unit and not as a compiled =
expression….<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>Try this…<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>sql iidbdb << SQL_END<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>\r<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>set autocommit on;<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>\p\g<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>drop table xxx;<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>\p\g<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>create table xxx(a_string varchar(10) not null not =
default)<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>with nojournaling, =
page_size=3D4096;<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>create view xxx_view as select a_string from =
xxx;<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>insert into xxx values('a string');<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>insert into xxx values('a string =
2');<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>insert into xxx values('a string =
3');<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>\p\g<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>select * from xxx_view;<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>/* The strings will be displayed OK! =
*/<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>\p\g<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>alter table xxx alter column a_string varchar(25) not null not =
default;<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>/* So we've made the column wider and not redefined the view =
*/<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>\p\g<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>insert into xxx values('a much longer =
string');<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>/* Inserted a longer string */<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>\p\g<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>select * from xxx_view;<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>/* And the view retains the varchar(10) information, and the =
longer
string<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'> * appears to be corrupted - or truncated - depending on =
whether
the<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'> * extraction is done with simple SQL or with something =
more
complicated!<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'> */<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>\p\g<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>\q<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>SQL_END<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>Is this a bug?<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D2 face=3DCalibri><span lang=3DEN-GB =
style=3D'font-size:
11.0pt'>Martin Bowes<o:p></o:p></span></font></p>
</div>
</body>
</html>
------_=_NextPart_001_01C8E7EC.8903A999--
|
