Re: more on delete from join

From: Nilone <reaanb_at_gmail.com>
Date: Sat, 29 Aug 2009 11:37:28 -0700 (PDT)
Message-ID: <f5661d68-8a02-4ab1-8e7d-312448adb17d_at_o35g2000vbi.googlegroups.com>


On Aug 27, 9:07 pm, Kevin Kirkpatrick <kvnkrkpt..._at_gmail.com> wrote:
> On Aug 27, 4:24 am, Nilone <rea..._at_gmail.com> wrote:
> > Let's clearly separate the relational model from the DBMS.  The
> > relational model requires no addition or modification to support
> > updateable views, since a view is just another relvar.  The problem is
> > not in updating the view (which is considered transient by the DBMS),
> > but in updating the persistent base relations accordingly.  As such,
> > it resembles constraint satisfaction problems, a completely different
> > (but related) branch of logic.
>
> I'll have to disagree.  Updating a base table is asserting/denying
> propositions which are part of the data model.  Updating a view is
> asserting conclusions that should be reachable by the database, and
> hoping the dbms correctly guesses at appropriate changes to the
> propositions such that the new conclusion will be reached.

I agree with you, but it has as little to do with the relational model as the unsolvability of some systems of equations has to do with the mechanics of arithmetic. I wasn't opposing your point, only your attribution of the problem to RT.

>
> Please tell me how there is a theoretically correct way to handle this
> real-life example:
>
> SQL> CREATE TABLE T1 (C1 NUMBER, C2 NUMBER)
>   2  /
>
> Table created.
>
> SQL> CREATE VIEW V1 AS SELECT C1 FROM T1 WHERE C1 < 10
>   2  /
>
> View created.
>
> SQL> INSERT INTO V1 VALUES (10)
>   2  /
>
> 1 row created.
>
> I mean, on how many levels is this just *wrong*?

Some views are meant to derive conclusions from the base propositions, and clearly shouldn't be updateable. On the other hand, when a view selects a subset of the tuples in a relvar, I see no reason to prevent inserts and updates as long as the affected tuples conform to the selection criteria.

>
> > Whether the DBMS should support updateable views depends on the
> > implementer, I'd say, but it's certainly a powerful and useful feature
> > that I want in any DBMS I use.  Even though the derivation of a view
> > is not always reversible, it is still useful when it is, and I can
> > often design a view to be so.
>
> Similarly, table triggers are powerful and "useful" features... but
> the question that allows me to say, "leave em out" applies to
> updatable views, too: can the same ends be accomplished with means
> that are on theoretically sound ground?

Does it come down to subjective factors like elegance vs convenience? Or is there a negative consequence to allowing updateable views when the effect is unambiguous?

>
> > My biggest frustration with views in existing products is not that
> > some views aren't updateable, but that it is difficult to see whether
> > a view is updateable or not, based on the syntax of query.
>
> Reminds me of some of my biggest frustration with triggers in existing
> products.

Thankfully, I haven't ever created a trigger or had to deal with any. Received on Sat Aug 29 2009 - 20:37:28 CEST

Original text of this message