Thread: 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