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

Bruce Momjian wrote:

>
> Does anyone know a cause for this?

    This  is  one of the frequently asked RULE-/VIEW-questions. I
    think I've answered it at least a half dozen times up to  now
    and  if  I  recall  right,  explained  it  it  detail  in the
    documentation of the rule system too. Seems I failed to  make
    it funny enough to let people read until the end ;-)

    Well,  the  cause is that there is a rewrite rule for SELECT,
    but none for INSERT. Thus, the INSERT goes through and  get's
    executed as if "z" where a table, what it in fact is, because
    there are  all  catalog  entries  plus  a  relation-file  for
    tuples. So why should the executor throw them away?

    At  the  time of the INSERT, the relations file "z" lost it's
    zero-size, and as soon as you drop the _RETz  rule,  you  can
    SELECT the "bar" (and order a beer).

    One  possible  solution would be to let the rewriter check on
    INSERT/UPDATE/DELETE if a SELECT rule exists but none for the
    requested  event  and  complain  about  it. But I thought the
    rewriter is already complicated enough, so I've let it out.

    Another solution would be, to  set  the  ACL  by  default  to
    owner=r  and  force  people  to  change ACL's when they setup
    rules to make views updateable. Maybe the better solution.


Jan

>
>
> > 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.


--

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