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
