This is a multi-part message in MIME format.
------_=_NextPart_001_01C8E7F1.5C48D4E4
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable
Hi Ian,
=20
Sounds nasty!
=20
Perhaps just a note in the do***entation of alter table alter column to
mention the view problem would be a nice side step.
=20
Marty
=20
From: info-ingres-bounces@[EMAIL PROTECTED]
On Behalf Of Ian
Kirkham
Sent: 17 July 2008 10:10
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] How much does a view remember?
=20
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_01C8E7F1.5C48D4E4
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:m=3D"http://schemas.microsoft.com/office/2004/12/omml"
=
xmlns=3D"http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 12 (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>
<!--
/* Font Definitions */
@[EMAIL PROTECTED]
15 5 2 2 2 4 3 2 4;}
@[EMAIL PROTECTED]
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;
font-family:"Arial","sans-serif";
color:navy;}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
..MsoChpDefault
{mso-style-type:ex****t-only;
font-size:10.0pt;}
@[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-GB link=3Dblue vlink=3Dpurple>
<div class=3DSection1>
<p class=3DMsoNormal><span style=3D'color:#1F497D'>Hi =
Ian,<o:p></o:p></span></p>
<p class=3DMsoNormal><span =
style=3D'color:#1F497D'><o:p> </o:p></span></p>
<p class=3DMsoNormal><span style=3D'color:#1F497D'>Sounds =
nasty!<o:p></o:p></span></p>
<p class=3DMsoNormal><span =
style=3D'color:#1F497D'><o:p> </o:p></span></p>
<p class=3DMsoNormal><span style=3D'color:#1F497D'>Perhaps just a note =
in the
do***entation of alter table alter column to mention the view problem =
would be
a nice side step.<o:p></o:p></span></p>
<p class=3DMsoNormal><span =
style=3D'color:#1F497D'><o:p> </o:p></span></p>
<p class=3DMsoNormal><span =
style=3D'color:#1F497D'>Marty<o:p></o:p></span></p>
<p class=3DMsoNormal><span =
style=3D'color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style=3D'border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt =
0cm 0cm 0cm'>
<p class=3DMsoNormal><b><span lang=3DEN-US =
style=3D'font-size:10.0pt;font-family:
"Tahoma","sans-serif"'>From:</span></b><span lang=3DEN-US =
style=3D'font-size:10.0pt;
font-family:"Tahoma","sans-serif"'>
info-ingres-bounces@[EMAIL PROTECTED]
<b>On Behalf Of =
</b>Ian
Kirkham<br>
<b>Sent:</b> 17 July 2008 10:10<br>
<b>To:</b> Ingres and related product discussion forum<br>
<b>Subject:</b> Re: [Info-Ingres] How much does a view =
remember?<o:p></o:p></span></p>
</div>
</div>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal><span lang=3DEN-US =
style=3D'font-size:10.0pt;font-family:"Arial","sans-serif";
color:navy'>Hi Martin,<o:p></o:p></span></p>
<p class=3DMsoNormal><span lang=3DEN-US =
style=3D'font-size:10.0pt;font-family:"Arial","sans-serif";
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></p>
<p class=3DMsoNormal><span lang=3DEN-US =
style=3D'font-size:10.0pt;font-family:"Arial","sans-serif";
color:navy'>Regards,<o:p></o:p></span></p>
<p class=3DMsoNormal><span lang=3DEN-US =
style=3D'font-size:10.0pt;font-family:"Arial","sans-serif";
color:navy'>Ian<o:p></o:p></span></p>
<p class=3DMsoNormal><span lang=3DEN-US =
style=3D'font-size:10.0pt;font-family:"Arial","sans-serif";
color:navy'><o:p> </o:p></span></p>
<div>
<div class=3DMsoNormal align=3Dcenter style=3D'text-align:center'><span =
lang=3DEN-US
style=3D'font-size:12.0pt;font-family:"Times New Roman","serif"'>
<hr size=3D2 width=3D"100%" align=3Dcenter>
</span></div>
<p class=3DMsoNormal><b><span lang=3DEN-US =
style=3D'font-size:10.0pt;font-family:
"Tahoma","sans-serif"'>From:</span></b><span lang=3DEN-US =
style=3D'font-size:10.0pt;
font-family:"Tahoma","sans-serif"'> =
info-ingres-bounces@[EMAIL PROTECTED]
<b>On Behalf Of =
</b>Martin
Bowes<br>
<b>Sent:</b> 17 July 2008 09:36<br>
<b>To:</b> Ingres and related product discussion forum<br>
<b>Subject:</b> [Info-Ingres] How much does a view remember?</span><span
lang=3DEN-US style=3D'font-size:12.0pt;font-family:"Times New =
Roman","serif"'><o:p></o:p></span></p>
</div>
<p class=3DMsoNormal><span lang=3DEN-US><o:p> </o:p></span></p>
<p class=3DMsoNormal>Hi Everyone,<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>How much does a view remember about the columns =
involved in
its definition?<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>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></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>Try this…<o:p></o:p></p>
<p class=3DMsoNormal>sql iidbdb << SQL_END<o:p></o:p></p>
<p class=3DMsoNormal>\r<o:p></o:p></p>
<p class=3DMsoNormal>set autocommit on;<o:p></o:p></p>
<p class=3DMsoNormal>\p\g<o:p></o:p></p>
<p class=3DMsoNormal>drop table xxx;<o:p></o:p></p>
<p class=3DMsoNormal>\p\g<o:p></o:p></p>
<p class=3DMsoNormal>create table xxx(a_string varchar(10) not null not =
default)<o:p></o:p></p>
<p class=3DMsoNormal>with nojournaling, page_size=3D4096;<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>create view xxx_view as select a_string from =
xxx;<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>insert into xxx values('a string');<o:p></o:p></p>
<p class=3DMsoNormal>insert into xxx values('a string =
2');<o:p></o:p></p>
<p class=3DMsoNormal>insert into xxx values('a string =
3');<o:p></o:p></p>
<p class=3DMsoNormal>\p\g<o:p></o:p></p>
<p class=3DMsoNormal>select * from xxx_view;<o:p></o:p></p>
<p class=3DMsoNormal>/* The strings will be displayed OK! =
*/<o:p></o:p></p>
<p class=3DMsoNormal>\p\g<o:p></o:p></p>
<p class=3DMsoNormal>alter table xxx alter column a_string varchar(25) =
not null
not default;<o:p></o:p></p>
<p class=3DMsoNormal>/* So we've made the column wider and not redefined =
the view
*/<o:p></o:p></p>
<p class=3DMsoNormal>\p\g<o:p></o:p></p>
<p class=3DMsoNormal>insert into xxx values('a much longer =
string');<o:p></o:p></p>
<p class=3DMsoNormal>/* Inserted a longer string */<o:p></o:p></p>
<p class=3DMsoNormal>\p\g<o:p></o:p></p>
<p class=3DMsoNormal>select * from xxx_view;<o:p></o:p></p>
<p class=3DMsoNormal>/* And the view retains the varchar(10) =
information, and the
longer string<o:p></o:p></p>
<p class=3DMsoNormal> * appears to be corrupted - or truncated - =
depending
on whether the<o:p></o:p></p>
<p class=3DMsoNormal> * extraction is done with simple SQL or with =
something
more complicated!<o:p></o:p></p>
<p class=3DMsoNormal> */<o:p></o:p></p>
<p class=3DMsoNormal>\p\g<o:p></o:p></p>
<p class=3DMsoNormal>\q<o:p></o:p></p>
<p class=3DMsoNormal>SQL_END<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>Is this a bug?<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>Martin Bowes<o:p></o:p></p>
</div>
</body>
</html>
------_=_NextPart_001_01C8E7F1.5C48D4E4--
|
