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
> 

Mon, 12 Jul 1999 00:39:00 -0600 (MDT)
Ryan Bradetich <rbrad@hpb50023.boi.hp.com>
Re: [HACKERS] create rule changes table to view ?
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

Mon, 12 Jul 1999 11:00:44 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] create rule changes table to view ? 
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) #



Mon, 12 Jul 1999 18:10:52 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] create rule changes table to view ?
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.


Mon, 12 Jul 1999 14:49:05 -0400 (EDT)
Peter Eisentraut <peter@pathwaynet.com>
Re: [HACKERS] create rule changes table to view ? 
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

Mon, 12 Jul 1999 17:24:44 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] create rule changes table to view ? 
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) #



Tue, 13 Jul 1999 03:25:27 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] create rule changes table to view ?
>     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

Mon, 12 Jul 1999 21:35:40 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] create rule changes table to view ?
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.

Tue, 13 Jul 1999 08:58:17 -0400 (EDT)
"D'Arcy" "J.M." Cain <darcy@druid.net>
Re: [HACKERS] create rule changes table to view ?
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.


Tue, 13 Jul 1999 14:11:13 -0400 (EDT)
Peter Eisentraut <peter@pathwaynet.com>
Re: [HACKERS] create rule changes table to view ?