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

Tue, 25 May 1999 10:42:39 -0400
Tom Lane <tgl@sss.pgh.pa.us>
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) #



Tue, 25 May 1999 17:52:05 +0200 (MET DST)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] INSERT INTO view means what exactly?
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

Tue, 25 May 1999 14:53:28 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] INSERT INTO view means what exactly? 
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) #



Tue, 25 May 1999 21:22:51 +0200 (MET DST)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] INSERT INTO view means what exactly?
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, Pennsylvania 19026

Tue, 21 Sep 1999 15:44:07 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
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) #



Tue, 21 Sep 1999 22:19:33 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] INSERT INTO view means what exactly?