Re: [HACKERS] create rule changes table to view ? - Mailing list pgsql-hackers

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