Re: FW: look-up cache failures

"Scott Muir" <wsmuir(at)islandnet(dot)com> writes:
> if I happen to make a modification (using pgaccess) to a view which
> has another view which uses it, it corrupts things and I have to drop
> all of the views which make reference to the modified one and recreate
> them... OR I can use pg_dump to allow me to make the modifications to
> a dumpfile, drop the db and recreate using the modified dump.

Cross-references between views and functions are stored in terms of OIDs.
If you drop and re-create a view or function, the new object has a new
OID, so it doesn't satisfy the pre-existing references.

We really need some kind of ALTER FUNCTION/ALTER VIEW command so that
you can modify the contents of a function or view without changing its
OID.

In the meantime, what most people do is to define sets of related
functions and views in scripts:

DROP FUNCTION foo(...);
CREATE FUNCTION foo(...) ...;
DROP VIEW v;
CREATE VIEW v ...;

If you need to make a change, edit the script and then feed it to psql.
Being able to use a real text editor on the script is a big improvement
over typing directly at psql anyway.

regards, tom lane