Обсуждение: FW: look-up cache failures

I haven't gotten a response to my question...

Is there a more appropriate forum I should be posting this to???

Thank-you.
Scott.

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of
wsmuir@islandnet.com
Sent: March 9, 2001 7:45 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] look-up cache failures


Could someone clarify what is apropriate for these situations for me
please...

Firstly I'm using 7.0.2 on Linux and FreeBSD and am in the development
cycle for a system.

My middle tier programmer asks me from time to time to add another
attribute to views that I have made, and everytime I do, I feel like
I'm tangling with disaster....

I have nested views and views which call on plpgsql functions,

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.

Anyways, I am understanding the behaviour of this after fighting with
it for a while, but I'm wondering why it is so hard to do what I'm
doing...  Are nested views a big no-no??? same deal with plpgsql
functions... if a view uses a plpgsql function and you make a change
to the function, the view won't work again until you drop it and
recreate it....

I've figured out that it has to do with creation/saving order for
visibility...  in the lookup cache...

What SHOULD i be doing?  Will 7.0.3 help me with this???  7.1?

Thankyou

Scott.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

"Scott Muir" <wsmuir@islandnet.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