Re: [HACKERS] INSERT INTO view means what exactly?

Tom Lane wrote:

>
> With current sources:
>
> regression=> CREATE TABLE x (y text);
> CREATE
> regression=> CREATE VIEW z AS select * from x;
> CREATE
> regression=> INSERT INTO x VALUES ('foo');
> INSERT 411635 1
> regression=> INSERT INTO z VALUES ('bar');
> INSERT 411636 1
> regression=> select * from x;
> y
> ---
> foo
> (1 row)
>
> regression=> select * from z;
> y
> ---
> foo
> (1 row)
>
> OK, where'd tuple 411636 go?  Seems to me that the insert should either
> have been rejected or caused an insert into x, depending on how
> transparent you think views are (I always thought they were
> read-only?).  Dropping the data into never-never land and giving a
> misleading success response code is not my idea of proper behavior.

    Tuple 411636 went into data/base/regression/x :-)

    You  can  verify that by looking at the file - it surely lost
    it's zero size and has a data block now. Also vacuum on  that
    relation will tell that there is a tuple now!

    This  is  because  from the parsers point of view there is no
    difference between a table and a view. There is  no  rule  ON
    INSERT  setup  for  relation  x,  so  the rewrite system does
    nothing and thus the plan will  become  a  real  insert  into
    relation  x.  But  when doing the "SELECT * FROM z", the rule
    _RETz is triggered and it's rewritten into a "SELECT  *  FROM
    x".  Thus  you'll  never see your data again (unless you drop
    the rule _RETz and select after that).

    Making views auto transparent (by setting up  INSERT,  UPDATE
    and  DELETE  rules  as well) is impossible, because in a join
    not selecting all attributes the system cannot guess where to
    take the missing ones from.

    It  might be a good idea to abort if there's a SELECT rule on
    the result relation but not  one  for  the  actual  operation
    performed.  I'll  put  that  onto  my personal TODO for after
    v6.5.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #