Re: Really bad/weird stuff with views over tables in 7.0.2

Alfred Perlstein <bright(at)wintelcom(dot)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