script to keep views "correct"? 
Author Message
 script to keep views "correct"?

Tom Jenkins sez:

Quote:
} Hello all,
} is there a script somewhere that ensures that changing a view that other
} views depend on, will recreate those other views?  in our development
} server we're constantly reworking/tweaking views and invariably forget
} to recreate these other views and end up getting cache lookup errors.
}
} same thing with functions/triggers

I have also been looking for something similar. Basically, whenever a view
(or whatever) gets created, the table/view/function names are resolved and
it is "compiled." This is great for speed, but if you change something that
other things depend upon, they fall apart. In particular, I had a table for
which one of the columns had a default that took its value from a function.
When I changed the function, it was no longer possible to insert into the
table until I did an ALTER TABLE ALTER COLUMN SET DEFAULT.

It is absolutely reasonable that this is how things work, but it would be
really good if some dependency-sensitive script could generate the SQL to
regenerate/adjust all of the things that might depend on each other. I
figure that it is difficult or impossible to see what changed and broke
what, but just assuming that everything changed, there should be a way to
spit out appropriate SQL to regenerate (in this order?):

1. views
2. functions
3. triggers/rules
4. column defaults

Obviously, views that depended on one another would have to be listed in a
topological sort. I think that pg_dump actually does most of this with
appropriate flags, but at least column defaults would have to be handled
differently (since we *really* don't want to drop/create the table, just
set its column defaults).

Quote:
} Tom Jenkins

--Greg

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly



Tue, 14 Dec 2004 02:47:16 GMT
 script to keep views "correct"?


Quote:
> Hello all,
> is there a script somewhere that ensures that changing a view that other
> views depend on, will recreate those other views?=20=20
> same thing with functions/triggers

I believe there's work going on to create a dependencies table, but of cour=
se=20
that's a lot of individual changes to make sure everything writes to it, so=
 I=20
don't think it's imminent.

I try to keep all my source SQL/functions outside the database and manage t=
hem=20
with make. Not ideal, but the best I've come up with so far.

- Richard Huxton

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Tue, 14 Dec 2004 03:22:55 GMT
 script to keep views "correct"?

Quote:


> > Hello all,
> > is there a script somewhere that ensures that changing a view that other
> > views depend on, will recreate those other views?  
> > same thing with functions/triggers

> I believe there's work going on to create a dependencies table, but of course
> that's a lot of individual changes to make sure everything writes to it, so I
> don't think it's imminent.

that will be very nice.

Quote:

> I try to keep all my source SQL/functions outside the database and manage them
> with make. Not ideal, but the best I've come up with so far.

hrmmm... haven't thought about make

although pg_rewrite seems to have the info i want; i just have to parse
it

Quote:
> - Richard Huxton

--

Tom Jenkins
Development InfoStructure
http://www.devis.com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Tue, 14 Dec 2004 03:51:04 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. max of ("...","...","..")

2. Correcting "invalid use of null"

3. correct use of "space" newbie

4. correcting database that was "marked suspect"

5. Correcting "Invalid use of Null"

6. is this "SELECT" correct

7. Correct behaviour of "DROP DATABASE"

8. Crystal Reports "keep together"

9. Keeping aggregates in "rolling window" scenario

10. ADO Connection object "Keep alive"

11. Keeping aggregates in "rolling window" scenario


 
Powered by phpBB® Forum Software