Re: [HACKERS] create rule changes table to view ?

Peter Eisentraut wrote:

>
> On Mon, 12 Jul 1999, Tom Lane wrote:
>
> > Ryan Bradetich <rbrad(at)hpb50023(dot)boi(dot)hp(dot)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(at)debis(dot)com (Jan Wieck) #