Обсуждение: VIEW problem
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?" ----------------------------------------------
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?" ----------------------------------------------
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 #
>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.
(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.
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!