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