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);

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,

The little bit of investigation I've done leads me to belive I can determine the
difference between a table and a view, because they are correctly seperated in
pg_views and pg_tables. I'll do some more research and see if I can actually do
this, or if you and Jan are right :)

Thanks,
- Ryan

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, Pennsylvania 19026

Andreas Zeugswetter wrote:

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.

Couldn't all views be expressed with the rule system, if we had subselects
in the
from clause ? This would be useful for other SQL too. RDB has this e.g.

I hope so,

because the FROM clause is what I (thinking in querytrees)
usually call the rangetable. After parsing, all relations
(tables and views - the parser doesn't care) the user
mentioned in his query appear in the querytree as RTE's
(Range Table Entries).

On a first thought it looks simple to just add another Node
pointer to the RTE structure and if a view has something that
requires materialization just throw it's querytree from
pg_rewrite into there. The planner then has to produce the
entire subtree for that as a left- or righttree for the
"relation".

The problem is just to decide which restrictions from the
WHERE clause could be taken down into this subselecting RTE
to reduce the amount of data the view materializes instead of
filtering them out later.

Example:

CREATE VIEW v1 AS SELECT a, sum(b) FROM t1 GROUP BY a;

SELECT count(*) FROM v1 WHERE a < 10;

Let's assume now that t1 has a million rows but only a few
hundred that match a < 10. If we now materialize the view in
a subplan without telling a < 10, a seqscan over the entire
table plus sorting/grouping and summing would happen instead
of fetching the few tuples by index and then sort/group/sum.

The opposite:

CREATE VIEW v2 AS SELECT a, sum(c) FROM t2 GROUP BY a;

SELECT v1.a FROM v1, v2 WHERE v1.a = v2.a AND v1.b = v2.c;

This time there is no chance - we ask for comparision of two
aggregates of different views. The WHERE clause here can only
be evaluated after both views have completely been
materialized.

I do not beleive, that Stonebraker had an incomplete Rule System in mind.

At least his concept is expandable to meet our needs. An
expandable concept is never really incomplete as long as it
never leaves the drawing board :-)

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) #

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.

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.

Couldn't all views be expressed with the rule system, if we had

subselects

in the
from clause ? This would be useful for other SQL too. RDB has this e.g.

I hope so,

because the FROM clause is what I (thinking in querytrees)
usually call the rangetable. After parsing, all relations
(tables and views - the parser doesn't care) the user
mentioned in his query appear in the querytree as RTE's
(Range Table Entries).

On a first thought it looks simple to just add another Node
pointer to the RTE structure and if a view has something that
requires materialization just throw it's querytree from
pg_rewrite into there. The planner then has to produce the
entire subtree for that as a left- or righttree for the
"relation".

The problem is just to decide which restrictions from the
WHERE clause could be taken down into this subselecting RTE
to reduce the amount of data the view materializes instead of
filtering them out later.

Example:

CREATE VIEW v1 AS SELECT a, sum(b) FROM t1 GROUP BY a;

SELECT count(*) FROM v1 WHERE a < 10;

Let's assume now that t1 has a million rows but only a few
hundred that match a < 10. If we now materialize the view in
a subplan without telling a < 10, a seqscan over the entire
table plus sorting/grouping and summing would happen instead
of fetching the few tuples by index and then sort/group/sum.

The opposite:

CREATE VIEW v2 AS SELECT a, sum(c) FROM t2 GROUP BY a;

SELECT v1.a FROM v1, v2 WHERE v1.a = v2.a AND v1.b = v2.c;

This time there is no chance - we ask for comparision of two
aggregates of different views. The WHERE clause here can only
be evaluated after both views have completely been
materialized.

I do not beleive, that Stonebraker had an incomplete Rule System in

mind.

At least his concept is expandable to meet our needs. An
expandable concept is never really incomplete as long as it
never leaves the drawing board :-)

Jan

Would it be possible to make the executor reentrant for those
subqueries which couldn't be rewritten/resolved into the parent query.
If you took your second example above and store the results of v1
and v2 into temp tables v1_temp, v2_temp respectively, They could be used to
complete the query on another executor pass.
You wouldn't need to re-parse/optimize because you could simple
replace the sections of the RTE with the oids of the temp tables and then
execute. There wouldn't be any indexes to optimize upon so you could just
choose a join method (i.e. HASH) that would work best with the number of
rows that need to be sequentially scanned and/or sorted.
I think it would be dog slow but it would work for those cases.

I haven't thought through all of the possible cases but it appears
that the best case for combining is a single table single constraint
situation.
From your first example it's easy to see that the constant would be
taken into the subselect and since this leaves the outside query without any
constraining terms then see if you can just rewrite the select list to
perform the query without the subselect.

If you're willing to give me a fairly comprehensive query/view
combinations I'm willing to work out a strategy to resolve them all; I don't
know how efficient it will all be but I'll give it a whirl.

discussion can always be useful,
-DEJ