Postgres Pro
Downloads
Home   >   mailing lists

Thread: VIEW problem

VIEW problem

From
Tim Uckun
Date:
This sequence of events seems to break something.

1) Create table foo
2) create table bar
3) create view foobar linking the two tables.
4) drop table foo
5) Create table foo (identical to first table)
6) SELECT from view foobar.

step six causes a relation not found error.

Is this a bug? As long as the SQL statement that the view is based on is
still valid why does it care if the table is dropped and recreated?
----------------------------------------------
              Tim Uckun
       Mobile Intelligence Unit.
----------------------------------------------
    "There are some who call me TIM?"
----------------------------------------------

Re: VIEW problem

From
Tim Uckun
Date:
At 05:48 PM 10/5/2000 +1100, you wrote:

>I believe this is because the OIDs are not identical. Internally, Postgres is
>referring to the OIDs and not to the table name. Might be wrong, I am only a
>postgres newbie, but I think this is the case.

Interesting this makes sense.

>I don't think it is a bug either, this is rather correct and prevents the
>database from doing the wrong thing (your new table foo could be completely
>different from the first table foo)

Here I have to disagree with you. I can't believe that this behaviour was
intended. A view is not materialized and it's simply a RULE which is to say
that it's nothing more then a SQL statement. As long as that SQL statement
is valid, parseable and returns a recordset it really ought not to care
about oids.
----------------------------------------------
              Tim Uckun
       Mobile Intelligence Unit.
----------------------------------------------
    "There are some who call me TIM?"
----------------------------------------------

Re: VIEW problem

From
Jan Wieck
Date:
Tim Uckun wrote:
> At 05:48 PM 10/5/2000 +1100, you wrote:
>
> >I believe this is because the OIDs are not identical. Internally, Postgres is
> >referring to the OIDs and not to the table name. Might be wrong, I am only a
> >postgres newbie, but I think this is the case.
>
> Interesting this makes sense.
>
> >I don't think it is a bug either, this is rather correct and prevents the
> >database from doing the wrong thing (your new table foo could be completely
> >different from the first table foo)
>
> Here I have to disagree with you. I can't believe that this behaviour was
> intended. A view is not materialized and it's simply a RULE which is to say
> that it's nothing more then a SQL statement. As long as that SQL statement
> is valid, parseable and returns a recordset it really ought not to care
> about oids.

    You're  right and this is a long standing - let's say lacking
    capability instead of bug.

    The problem is that the rules aren't stored as their  queries
    string  representation,  given  in  the  original CREATE RULE
    statement.   Instead,   the   internal   queries    parsetree
    representation is stored to avoid the need to parse the rules
    any time they're used again. In the case of a DROP/CREATE  of
    an   underlying  object,  this  preparsed  querytree  becomes
    invalid, so that the view would need to be recompiled, so  to
    say.  But the system doesn't know the original definition any
    more so it can't.

    I have a personal TODO on this.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Re: VIEW problem

From
Stephan Szabo
Date:
>This sequence of events seems to break something.
>
>1) Create table foo
>2) create table bar
>3) create view foobar linking the two tables.
>4) drop table foo
>5) Create table foo (identical to first table)
>6) SELECT from view foobar.
>
>step six causes a relation not found error.
>
>Is this a bug? As long as the SQL statement that the view is based on is
>still valid why does it care if the table is dropped and recreated?

Sort of, but not the one you're thinking of.
Technically step 4 probably should have either prevented you from dropping
the table or automatically dropped the view based on whether you give
CASCADE or RESTRICT, however we don't currently support that notion
(hopefully 7.2 or so).

-----
SQL92 draft (11.18 <drop table statement>
     4) If RESTRICT is specified, then T shall not be referenced in
        the <query expression> of any view descriptor or the <search
        condition> of any constraint descriptor.

        Note: If CASCADE is specified, then such referencing objects
        will be dropped by the execution of the <revoke statement> spec-
        ified in the General Rules of this Subclause.

Re: VIEW problem

From
Stephan Szabo
Date:
(My normal mail account is dead so I'm
reading from the archives on the web :(
- And I'm not sure that my last mail on
the subject got through)

About view rules:
 Of course, you shouldn't actually be
able to get into the situation with
a view rule, since dropping the table
the view points to should drop the view
(or refuse to drop the table) it appears
from looking at drop table in the spec.

Re: Re: VIEW problem

From
Date:
On Fri, 6 Oct 2000, Stephan Szabo wrote:

> About view rules:
>  Of course, you shouldn't actually be
> able to get into the situation with
> a view rule, since dropping the table
> the view points to should drop the view
> (or refuse to drop the table) it appears
> from looking at drop table in the spec.

If you specify that the view have this behaviour, yes... but even Oracle
has gotten me on several occasions for this same thing, dropping a table,
then rebuilding, then fscking up the view.

Brett W. McCoy
                                              http://www.chapelperilous.net
---------------------------------------------------------------------------
Beam me up, Scotty!  It ate my phaser!