Обсуждение: INSERT INTO view means what exactly?
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.
regards, tom lane
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) #
Does anyone know a cause for this?
> 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.
>
> regards, tom lane
>
>
-- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
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) #