This is a multi-part message in MIME format.
------=_NextPart_000_002D_01C8E871.75B8E860
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
I see similar problems with other DBMSs.
I think it is good practise is to redefine all rules, procedures and views
after modifying a table.
Problem is keeping track of them.
In Mssql it is possible to lock a table definition to a view so the table
cannot be modified until the view is first dropped.
Paul
-----Original Message-----
From: info-ingres-bounces@[EMAIL PROTECTED]
Behalf Of Martin
Bowes
Sent: Thursday, 17 July 2008 7:42 PM
To: Ingres and related product discussion forum
Cc: Ian Kirkham
Subject: Re: [Info-Ingres] How much does a view remember?
Hi Ian,
Sounds nasty!
Perhaps just a note in the do***entation of alter table alter column to
mention the view problem would be a nice side step.
Marty
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?
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
----------------------------------------------------------------------------
--
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?
Hi Everyone,
How much does a view remember about the columns involved in its
definition?
I'd always thought next to nothing as a the view definition was stored
as
a text unit and not as a compiled expression..
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=4096;
create view xxx_view as select a_string from xxx;
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
Is this a bug?
Martin Bowes
------=_NextPart_000_002D_01C8E871.75B8E860
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns=3D"http://www.w3.org/TR/REC-html40"
xmlns:v =3D=20
"urn:schemas-microsoft-com:vml" xmlns:o =3D=20
"urn:schemas-microsoft-com:office:office" xmlns:w =3D=20
"urn:schemas-microsoft-com:office:word" xmlns:m =3D=20
"http://schemas.microsoft.com/office/2004/12/omml"><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<META content=3D"MSHTML 6.00.2900.3086" name=3DGENERATOR><!--[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>@[EMAIL PROTECTED]
{
font-family: Calibri;
}
@[EMAIL PROTECTED]
{
font-family: Tahoma;
}
@[EMAIL PROTECTED]
Section1 {size: 612.0pt 792.0pt; margin: 72.0pt 72.0pt 72.0pt =
72.0pt; }
P.MsoNormal {
FONT-SIZE: 11pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: =
"Calibri","sans-serif"
}
LI.MsoNormal {
FONT-SIZE: 11pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: =
"Calibri","sans-serif"
}
DIV.MsoNormal {
FONT-SIZE: 11pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: =
"Calibri","sans-serif"
}
A:link {
COLOR: blue; TEXT-DECORATION: underline; mso-style-priority: 99
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline; mso-style-priority: 99
}
A:visited {
COLOR: purple; TEXT-DECORATION: underline; mso-style-priority: 99
}
SPAN.MsoHyperlinkFollowed {
COLOR: purple; TEXT-DECORATION: underline; mso-style-priority: 99
}
SPAN.EmailStyle17 {
COLOR: windowtext; FONT-FAMILY: "Calibri","sans-serif"; mso-style-type: =
personal
}
SPAN.EmailStyle18 {
COLOR: navy; FONT-FAMILY: "Arial","sans-serif"; mso-style-type: =
personal
}
SPAN.EmailStyle19 {
COLOR: #1f497d; FONT-FAMILY: "Calibri","sans-serif"; mso-style-type: =
personal-reply
}
..MsoChpDefault {
FONT-SIZE: 10pt; mso-style-type: ex****t-only
}
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 vLink=3Dpurple link=3Dblue>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN =
class=3D046095414-17072008>I see=20
similar problems with other DBMSs.</SPAN></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN =
class=3D046095414-17072008>I=20
think it is good practise is to redefine all rules, procedures and views =
after=20
modifying a table.</SPAN></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D046095414-17072008></SPAN></FONT><FONT face=3DArial =
color=3D#0000ff=20
size=3D2><SPAN class=3D046095414-17072008>Problem is keeping track of=20
them.</SPAN></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN =
class=3D046095414-17072008>In=20
Mssql it is possible to lock a table definition to a view so the table =
cannot be=20
modified until the view is first dropped.</SPAN></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D046095414-17072008></SPAN></FONT><FONT face=3DArial =
color=3D#0000ff=20
size=3D2><SPAN class=3D046095414-17072008></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D046095414-17072008>Paul</SPAN></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D046095414-17072008></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D046095414-17072008></SPAN></FONT><FONT face=3DArial =
color=3D#0000ff=20
size=3D2><SPAN class=3D046095414-17072008></SPAN></FONT> </DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft><FONT =
face=3DTahoma=20
size=3D2>-----Original Message-----<BR><B>From:</B>=20
info-ingres-bounces@[EMAIL PROTECTED]
[mailto:info-ingres-bounces@[EMAIL PROTECTED]
>On Behalf Of=20
</B>Martin Bowes<BR><B>Sent:</B> Thursday, 17 July 2008 7:42 =
PM<BR><B>To:</B>=20
Ingres and related product discussion forum<BR><B>Cc:</B> Ian=20
Kirkham<BR><B>Subject:</B> Re: [Info-Ingres] How much does a view=20
remember?<BR><BR></FONT></DIV>
<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=20
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=20
do***entation of alter table alter column to mention the view problem =
would be=20
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=20
style=3D"BORDER-RIGHT: medium none; PADDING-RIGHT: 0cm; BORDER-TOP: =
#b5c4df 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: =
medium none; PADDING-TOP: 3pt; BORDER-BOTTOM: medium none">
<P class=3DMsoNormal><B><SPAN lang=3DEN-US=20
style=3D"FONT-SIZE: 10pt; FONT-FAMILY: =
'Tahoma','sans-serif'">From:</SPAN></B><SPAN=20
lang=3DEN-US style=3D"FONT-SIZE: 10pt; FONT-FAMILY: =
'Tahoma','sans-serif'">=20
info-ingres-bounces@[EMAIL PROTECTED]
[mailto:info-ingres-bounces@[EMAIL PROTECTED]
<B>On Behalf Of =
</B>Ian=20
Kirkham<BR><B>Sent:</B> 17 July 2008 10:10<BR><B>To:</B> Ingres and =
related=20
product discussion forum<BR><B>Subject:</B> Re: [Info-Ingres] How much =
does a=20
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=20
style=3D"FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: =
'Arial','sans-serif'">Hi=20
Martin,<o:p></o:p></SPAN></P>
<P class=3DMsoNormal><SPAN lang=3DEN-US=20
style=3D"FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: =
'Arial','sans-serif'">The=20
text ****tion is just there for information and the real information =
used is=20
the CX data. There are a number of actions that if done can invalidate =
a view.=20
There is no link retained with the underlying metadata so as you have =
seen,=20
the view is vulnerable to certain types of structure change. It would =
be=20
feasible to write a script or code to refresh a views CX data based on =
the=20
stored text, the CX data is stored in a column too which can be =
updated. The=20
more nutty problem to crack though would be the detecting of the =
dependency in=20
the first place and then either blocking the action or, if valid,=20
refre****ng.<o:p></o:p></SPAN></P>
<P class=3DMsoNormal><SPAN lang=3DEN-US=20
style=3D"FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: =
'Arial','sans-serif'">Regards,<o:p></o:p></SPAN></P>
<P class=3DMsoNormal><SPAN lang=3DEN-US=20
style=3D"FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: =
'Arial','sans-serif'">Ian<o:p></o:p></SPAN></P>
<P class=3DMsoNormal><SPAN lang=3DEN-US=20
style=3D"FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: =
'Arial','sans-serif'"><o:p> </o:p></SPAN></P>
<DIV>
<DIV class=3DMsoNormal style=3D"TEXT-ALIGN: center" =
align=3Dcenter><SPAN lang=3DEN-US=20
style=3D"FONT-SIZE: 12pt; FONT-FAMILY: 'Times New Roman','serif'">
<HR align=3Dcenter width=3D"100%" SIZE=3D2>
</SPAN></DIV>
<P class=3DMsoNormal><B><SPAN lang=3DEN-US=20
style=3D"FONT-SIZE: 10pt; FONT-FAMILY: =
'Tahoma','sans-serif'">From:</SPAN></B><SPAN=20
lang=3DEN-US style=3D"FONT-SIZE: 10pt; FONT-FAMILY: =
'Tahoma','sans-serif'">=20
info-ingres-bounces@[EMAIL PROTECTED]
[mailto: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=20
related product discussion forum<BR><B>Subject:</B> [Info-Ingres] How =
much=20
does a view remember?</SPAN><SPAN lang=3DEN-US=20
style=3D"FONT-SIZE: 12pt; 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=20
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=20
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=20
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=20
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=20
not default;<o:p></o:p></P>
<P class=3DMsoNormal>/* So we've made the column wider and not =
redefined the=20
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=20
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=20
the longer string<o:p></o:p></P>
<P class=3DMsoNormal> * appears to be corrupted - or truncated - =
depending=20
on whether the<o:p></o:p></P>
<P class=3DMsoNormal> * extraction is done with simple SQL or =
with=20
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></BLOCKQUOTE></BODY></HTML>
------=_NextPart_000_002D_01C8E871.75B8E860--
|
