Thread
-
INSERT INTO view means what exactly?
Tom Lane <tgl@sss.pgh.pa.us> — 1999-05-25T14:42:39Z
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 -
Re: [HACKERS] INSERT INTO view means what exactly?
Jan Wieck <jwieck@debis.com> — 1999-05-25T15:52:05Z
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) # -
Re: [HACKERS] INSERT INTO view means what exactly?
Bruce Momjian <maillist@candle.pha.pa.us> — 1999-09-21T19:44:07Z
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 -
Re: [HACKERS] INSERT INTO view means what exactly?
Jan Wieck <wieck@debis.com> — 1999-09-21T20:19:33Z
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) #