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

jwieck@debis.com (Jan Wieck) writes:

Tom Lane wrote:

regression=> INSERT INTO z VALUES ('bar');
INSERT 411636 1

OK, where'd tuple 411636 go?

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

.../z, you meant --- yup, I see you are right. Weird. I didn't
realize that views had an underlying table.

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.

I agree, that would be a good safety feature.

regards, tom lane

Tom Lane wrote:

jwieck@debis.com (Jan Wieck) writes:

Tom Lane wrote:

regression=> INSERT INTO z VALUES ('bar');
INSERT 411636 1

OK, where'd tuple 411636 go?

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

.../z, you meant --- yup, I see you are right. Weird. I didn't
realize that views had an underlying table.

They ARE a table - only that a rule ON SELECT hides their
(normal) emptyness :-)

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