Is it possible to define rules on system catalog tables (like
iiattribute) and
have changes (insert/delete/update) trigger a procedure to update those
table(s)
storing the view information ?

The Ingres replicator works this way.

Jürgen

-------- Original Message --------
Subject:     Re: [Info-Ingres] How much does a view remember?
Date:     Fri, 18 Jul 2008 00:58:58 +1000
From:     Paul White <shift7solutions@gmail.com>
Reply-To:     Ingres and related product discussion forum
<info-ingres@kettleriverconsulting.com>
To:     Ingres and related product discussion forum
<info-ingres@kettleriverconsulting.com>

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@kettleriverconsulting.com
   [mailto:info-ingres-bounces@kettleriverconsulting.com]On 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 documentation of alter table alter column
   to mention the view problem would be a nice side step.

   

   Marty

   

   From: info-ingres-bounces@kettleriverconsulting.com
   [mailto:info-ingres-bounces@kettleriverconsulting.com] 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 portion 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, refreshing.

   Regards,

   Ian

   

   ------------------------------------------------------------------------

   From: info-ingres-bounces@kettleriverconsulting.com
   [mailto:info-ingres-bounces@kettleriverconsulting.com] 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

I note with interest that you can insert a longer value through the
view, so it is only the retrieval from the view that has a problem. I
then looked at the standard and it does indeed allow a string column
to be increased in size. It appears to me that it expects subsequent
operations on a view column based on a changed underlying column to
reflect the new attributes of the underlying column. So the insert
behaviour I observed is correct and the retrieval behaviour that Marty
observed is wrong. Resolution of views is tricky stuff though - we try
to avoid excessive work when the view is "simple" like this, so the
posts that warn of messing around with these sorts of definition
dependencies are prudent and correct.

We should be able to get this right, though. The definitions of the
taboles underlying view definitions are available as we parse the
query, so I suspect we could fix it without a horrible amount of work.

Doug.