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(at)debis(dot)com (Jan Wieck) #