Обсуждение: Really bad/weird stuff with views over tables in 7.0.2

There's two problems here that kept me up all night hacking
in order to keep my system from crashing an burning so bear with
me if you can.

If you define a table and then create a select query rule over it
then drop the rule the table will be gone.

Another related problem is that let's say you have done this and
the table you've "hidden" with a view is rather large and has
indexes then postgresql will seriously choke on trying to 
vacuum and/or vacuum analyze the table which is really a view!

thanks,
-- 
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."

On Sat, 2 Sep 2000, Alfred Perlstein wrote:

> If you define a table and then create a select query rule over it
> then drop the rule the table will be gone.

What were you doing precisely?  When I made a simple table and 
then turned it into a view with a rule, dropping the rule
didn't seem to drop the table for me, I could still select
from it, etc after the rule dropped.  [I think I probably
misunderstood what you were doing, but...]

create table aa1 (a int);
create rule "_RETaa1" as on select to aa1 do instead select anum as a from a;
select * from aa1;
drop rule "_RETaa1";
select * from aa1; 

seems to work.  The first select gives me whatever was in a
and the second gives me anything i inserted into aa1 before
making the rule.

Alfred Perlstein <bright@wintelcom.net> writes:
> If you define a table and then create a select query rule over it
> then drop the rule the table will be gone.

> Another related problem is that let's say you have done this and
> the table you've "hidden" with a view is rather large and has
> indexes then postgresql will seriously choke on trying to 
> vacuum and/or vacuum analyze the table which is really a view!

regression=# create table foo(f1 int primary key);
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo'
CREATE
regression=# insert into foo values(1);
INSERT 272365 1
regression=# insert into foo values(2);
INSERT 272366 1
regression=# insert into foo values(3);
INSERT 272367 1
regression=# select * from foo;f1
---- 1 2 3
(3 rows)

regression=# create rule "_RETfoo" as on select to foo do instead
regression-# select f1+10 as f1 from int4_tbl;
CREATE
regression=# select * from foo;    f1
-------------         10     123466    -123446-2147483639-2147483637
(5 rows)

regression=# drop rule "_RETfoo" ;
DROP
regression=# select * from foo;f1
---- 1 2 3
(3 rows)

regression=# vacuum foo;
VACUUM
regression=# vacuum verbose analyze foo;
NOTICE:  --Relation foo--
NOTICE:  Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 3: Vac 0, Keep/VTL 0/
0, Crash 0, UnUsed 0, MinLen 36, MaxLen 36; Re-using: Free/Avail. Space 0/0; End
Empty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index foo_pkey: Pages 2; Tuples 3. CPU 0.00s/0.00u sec.
NOTICE:  Analyzing...
VACUUM
regression=#

Looks OK from here ... how about a reproducible example?
        regards, tom lane

* Tom Lane <tgl@sss.pgh.pa.us> [000902 11:06] wrote:
> Alfred Perlstein <bright@wintelcom.net> writes:
> > If you define a table and then create a select query rule over it
> > then drop the rule the table will be gone.
> 
> > Another related problem is that let's say you have done this and
> > the table you've "hidden" with a view is rather large and has
> > indexes then postgresql will seriously choke on trying to 
> > vacuum and/or vacuum analyze the table which is really a view!
> 
> Looks OK from here ... how about a reproducible example?

Ok, typo on my part, if you type "DROP VIEW foo;" that nukes the rule and
the table behind it.  Is that the expected behavior?  I'll try to
figure out a way to demonstrate the problem I thought I was having
with data in both tables later right now I desperately need sleep. :)

thanks,
-Alfred

Alfred Perlstein <bright@wintelcom.net> writes:
> Ok, typo on my part, if you type "DROP VIEW foo;" that nukes the rule and
> the table behind it.  Is that the expected behavior?

Well, yeah: a view *is* a table + ON SELECT rule, at least in current
releases.  Mark Hollomon just submitted a patch that would create a
distinction, but it's not even been applied to CVS yet...
        regards, tom lane