Thread: Re: [HACKERS] create rule changes table to view ?
Wow... I actully know the answer to this question :) psql declares the the type to be view? if the relkind is a relation and the relhasrules = true in pg_class for that entry. I will pull the latest source and see if I can come up with a better way for determining the type tomorrow, if someone else doesn't beat me to it :) -Ryan > I just began to learn rules with 6.5 and notice: > test=> \dt > Database = test > +------------------+----------------------------------+----------+ > | Owner | Relation | Type | > +------------------+----------------------------------+----------+ > | megera | access_log | table | > | megera | hits | table | > | megera | junk_qwerty | table | > +------------------+----------------------------------+----------+ > > test=> create rule log_hits as on update to hits do instead insert into hits values ( NEW.msg_id, 1); > CREATE > test=> \dt > Database = test > +------------------+----------------------------------+----------+ > | Owner | Relation | Type | > +------------------+----------------------------------+----------+ > | megera | access_log | table | > | megera | hits | view? | > | megera | junk_qwerty | table | > +------------------+----------------------------------+----------+ > > Table hits now becomes view ? > > > Regards, > > Oleg > > _____________________________________________________________ > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > Sternberg Astronomical Institute, Moscow University (Russia) > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(095)939-16-83, +007(095)939-23-83 >
Ryan Bradetich <rbrad@hpb50023.boi.hp.com> writes: > psql declares the the type to be view? if the relkind is a relation > and the relhasrules = true in pg_class for that entry. I will pull > the latest source and see if I can come up with a better way for > determining the type tomorrow, if someone else doesn't beat me to it The way Jan explained it to me, a view *is* a table that happens to have an "on select do instead" rule attached to it. If the table has data in it (which it normally wouldn't) you can't see that data anyway because of the select rule. This is another example like SERIAL columns, UNIQUE columns, etc, where we are not really leaving enough information in the system tables to allow accurate reconstruction of what the user originally said. Was it a CREATE VIEW, or a CREATE TABLE and manual attachment of a rule? No way to tell. In one sense it doesn't matter a whole lot, but for psql displays and pg_dump it would be nice to know what happened. regards, tom lane
Tom Lane wrote: > > Ryan Bradetich <rbrad@hpb50023.boi.hp.com> writes: > > psql declares the the type to be view? if the relkind is a relation > > and the relhasrules = true in pg_class for that entry. I will pull > > the latest source and see if I can come up with a better way for > > determining the type tomorrow, if someone else doesn't beat me to it > > The way Jan explained it to me, a view *is* a table that happens to > have an "on select do instead" rule attached to it. If the table > has data in it (which it normally wouldn't) you can't see that data > anyway because of the select rule. Right > > This is another example like SERIAL columns, UNIQUE columns, etc, where > we are not really leaving enough information in the system tables to > allow accurate reconstruction of what the user originally said. Was > it a CREATE VIEW, or a CREATE TABLE and manual attachment of a rule? > No way to tell. In one sense it doesn't matter a whole lot, but for > psql displays and pg_dump it would be nice to know what happened. Oh - but for VIEW's we leave enough information in the system tables. Rules on event SELECT actually 1. must be INSTEAD 2. have exactly one action. This action must be another SELECT which exactly produces a targetlist where all attributes are in the order and of the types of the tables schema 3. must be named "_RET<tablename>" 4. must be the only rule on event SELECT. These restrictions clearly tell that if a table has an ON SELECT rule, it IS A VIEW! There is absolutely no other possibility. Stonebraker originally planned to have other rules on the SELECT case too, namely attribute rules which only rewrite a single attribute of a table, and rules performing other actions than a SELECT if someone scans that table. But AFAIK these plans never materialized. The problem on SELECT rules is that they have totally different semantics than any other rules in that they must get applied not only on SELECT. Instead we also rewrite things like INSERT ... SELECT and DELETE ... WHERE x = view.y AND view.z = ... so views become usable in all kinds of statements. When fixing the rewrite system for v6.4 I decided to simplify the rewriting of SELECT rules by restricting them totally to views. After that, I simply took out all that screwed up code dealing with attribute rewriting and sent it down to the bit recycling. I don't plan to turn this wheel back. And if someone else ever succeeds in doing so, we'll have another "ruleguru" :-) So if you find an entry in pg_rewrite with ev_type=1 and ev_class=<my_tables_oid>, then my_table is a view - end of story. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #========================================= wieck@debis.com (Jan Wieck) #
On Mon, 12 Jul 1999, Tom Lane wrote: > Ryan Bradetich <rbrad@hpb50023.boi.hp.com> writes: > > psql declares the the type to be view? if the relkind is a relation > > and the relhasrules = true in pg_class for that entry. I will pull > > the latest source and see if I can come up with a better way for > > determining the type tomorrow, if someone else doesn't beat me to it > > The way Jan explained it to me, a view *is* a table that happens to > have an "on select do instead" rule attached to it. If the table > has data in it (which it normally wouldn't) you can't see that data > anyway because of the select rule. Does anyone else see a problem with this? This sort of approach almost prevents views with distinct, union, order by, etc. from ever being implemented. I don't know what other people use their views for but I use them to store complicated queries. So, in essence it would suffice to store the text of the query with a view rather than faking tables for it, thus confusing all sorts of utility programs. Then again, I'd be interested to know what to developers' idea of normal usage of a view is. -- Peter Eisentraut PathWay Computing, Inc.
Peter Eisentraut <peter@pathwaynet.com> writes: >> The way Jan explained it to me, a view *is* a table that happens to >> have an "on select do instead" rule attached to it. If the table >> has data in it (which it normally wouldn't) you can't see that data >> anyway because of the select rule. > Does anyone else see a problem with this? This sort of approach almost > prevents views with distinct, union, order by, etc. from ever being > implemented. What makes you think that? We do have work to do before some of those things will work, but I don't think it has anything to do with whether there is an empty table underlying a view... regards, tom lane
Peter Eisentraut wrote: > > On Mon, 12 Jul 1999, Tom Lane wrote: > > > Ryan Bradetich <rbrad@hpb50023.boi.hp.com> writes: > > > psql declares the the type to be view? if the relkind is a relation > > > and the relhasrules = true in pg_class for that entry. I will pull > > > the latest source and see if I can come up with a better way for > > > determining the type tomorrow, if someone else doesn't beat me to it > > > > The way Jan explained it to me, a view *is* a table that happens to > > have an "on select do instead" rule attached to it. If the table > > has data in it (which it normally wouldn't) you can't see that data > > anyway because of the select rule. > > Does anyone else see a problem with this? This sort of approach almost > prevents views with distinct, union, order by, etc. from ever being > implemented. Pardon - YES and NO! After all I think (even if it was a really great job) that Stonebraker was wrong. Views cannot be completely implemented by rules. That would make it impossibly complicated for a query planner. But I'm a YESBUTTER :-) But it really was a great job! In the actual version of PostgreSQL you can define a view that's a join of 3 tables and then select from that view by joining it with another 2 tables. The result will be a querytree that's exactly what you would have to type if there wouldn't be any view's at all - a join over 5 tables. That (however complicated) querytree is handed to the optimizer. It is the optimizer's job to decide the best access path for a 5 table join. YESBUT! Stonebraker was wrong - and must have been bacause today we want to get SQL92 compliant - and that spec didn't existed when he designed our rule sytem. The rule system is something we got from the good old v4.2 Postgres. That wasn't an SQL database, the querylanguage was POSTQUEL. So it isn't surprising that the original rule system spec's don't meet today's SQL needs. For thing's like aggregates, distinct/grouping and the like, we need to take a step backward and really do some kind of view materialization (create a real execution path for the view's definition). But don't force that to be done whenever a view is used - that doesn't make things better. > > I don't know what other people use their views for but I use them to store > complicated queries. So, in essence it would suffice to store the text of > the query with a view rather than faking tables for it, thus confusing all > sorts of utility programs. > > Then again, I'd be interested to know what to developers' idea of normal > usage of a view is. It doesn't count what 95% of our users use view's for. A view is a relation like a table, and if appearing in the rangetable, it must be treated like a relation. Well - let's only store the "QUERY TEXT" of a view: CREATE VIEW v1 AS SELECT X.a, X.b, Y.b AS c FROM tab1 X, tab2 Y WHERE X.a = Y.a; Simple enough - O.K.? Now we execute some simple queries: SELECT * FROM vi; SELECT Z.a, V.b, V.c FROM tab3 Z, v1 V WHERE Z.a = V.a; SELECT Z.a, SUM(V.c) FROM tab3 Z, v1 V WHERE Z.a = V.a; INSERT INTO tab4 SELECT Z.a, SUM(V.c) FROM tab3 Z, v1 V WHERE Z.a = V.a AND V.b > 2; DELETE FROM tab5 WHERE aa = v1.a AND bb < v1.c; Simple enough? All valid SQL statements! Could you now simply explain HOW to build the correct final statements by incorporating the stored "QUERY TEXT" into the above statements? I really mean HOW - not what the equivalent statements, hand translated, would look like (I've read querytrees like printed in debug level 3 several night's until I understood how rules should work - so I know how to rewrite the above by hand). The way I know to express this in C is the rule system you find in rewrite_handler.c and rewrite_manip.c (mostly). If you know an easier way, let me know. PLEASE DON'T READ THIS REPLY AS A SORT OF A FLAME. I KNOW THAT IT IS HARD TO UNDERSTAND THE RULE SYSTEM - I HAD TO TAKE THAT LEARNING CURVE MYSELF. AFTER ALL I STILL MIGHT HAVE MISSED SOMETHING - THUS I THINK WE STILL NEED MATERIALIZATION OF VIEWS IN SOME CASES (yesbut only in few cases - not in all view cases). Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #========================================= wieck@debis.com (Jan Wieck) #
> Stonebraker was wrong - and must have been bacause today we > want to get SQL92 compliant - and that spec didn't existed > when he designed our rule sytem. The rule system is > something we got from the good old v4.2 Postgres. That > wasn't an SQL database, the querylanguage was POSTQUEL. So it > isn't surprising that the original rule system spec's don't > meet today's SQL needs. > > For thing's like aggregates, distinct/grouping and the like, > we need to take a step backward and really do some kind of > view materialization (create a real execution path for the > view's definition). But don't force that to be done whenever > a view is used - that doesn't make things better. Thanks. Now I understand why aggregates cause problems with rules. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Thus spake Peter Eisentraut > I don't know what other people use their views for but I use them to store > complicated queries. So, in essence it would suffice to store the text of > the query with a view rather than faking tables for it, thus confusing all > sorts of utility programs. > > Then again, I'd be interested to know what to developers' idea of normal > usage of a view is. I use it for access control. Remember, in PostgreSQL we can grant and revoke access to tables independent of the table it is a view of. I use it to allow wider access to a subset of the fields in a table. -- D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
On Tue, 13 Jul 1999, Jan Wieck wrote: > > > The way Jan explained it to me, a view *is* a table that happens to > > > have an "on select do instead" rule attached to it. If the table > > > has data in it (which it normally wouldn't) you can't see that data > > > anyway because of the select rule. > > > > Does anyone else see a problem with this? This sort of approach almost > > prevents views with distinct, union, order by, etc. from ever being > > implemented. > > Pardon - YES and NO! > > After all I think (even if it was a really great job) that > Stonebraker was wrong. Views cannot be completely implemented > by rules. That would make it impossibly complicated for a > query planner. That was my point. Sure some of these things above could be done, but it's a dead end of sorts. > > I don't know what other people use their views for but I use them to store > > complicated queries. So, in essence it would suffice to store the text of > > the query with a view rather than faking tables for it, thus confusing all > > sorts of utility programs. > > > > Then again, I'd be interested to know what to developers' idea of normal > > usage of a view is. > > It doesn't count what 95% of our users use view's for. A view Um, it should though, shouldn't it? > Well - let's only store the "QUERY TEXT" of a view: > Now we execute some simple queries: > Simple enough? All valid SQL statements! Could you now simply > explain HOW to build the correct final statements by > incorporating the stored "QUERY TEXT" into the above > statements? Well, this would be trivial if you'd allow subselects in the FROM clause. But now I am beginning to realize that this is the very reason those subselects in the from clause aren't possible. Perhaps we ought to think up some math magic there. But I can't think of anything short of a temporary table of sorts right now. Anyway, you guys are doing a great job. If I had some more time I'd dig myself into this business and help out. Until that day, I'm sure you have your reasons for things to be the way they are, I'm just trying to point out ideas for improvements. -- Peter Eisentraut PathWay Computing, Inc.
Thus spake Peter Eisentraut > I don't know what other people use their views for but I use them to store > complicated queries. So, in essence it would suffice to store the text of > the query with a view rather than faking tables for it, thus confusing all > sorts of utility programs. > > Then again, I'd be interested to know what to developers' idea of normal > usage of a view is. I use it for access control. Remember, in PostgreSQL we can grant and revoke access to tables independent of the table it is a view of. I use it to allow wider access to a subset of the fields in a table. -- D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.