Re: Requirements for update languages?

Leandro Guimarães Faria Corsetti Dutra <lgcdutra_at_terra.com.br> writes:

> Jens Lechtenbörger wrote:
>
> > while there are some criteria to assess relational query
> > languages (adequate, relationally complete, optimizable) I
> > wonder what makes a good update language for a data model.
>
> Basically, it should be a D as in The Third Manifesto:
> http://www.thethirdmanifesto.com/.

Thank you for this link. I'll check it out.

>
> [...]
>
> > 2. As a db user, I expect that I can undo (inadvertent) data
> > manipulations, e.g., undo an insertion via a deletion or vice
> > versa. Does anybody else believe that this is a reasonable
> > requirement?
>
> That's one of the ideas behind transactions: you can do
> whatever you want, until you terminate your transaction with a
> COMMIT.
I really meant that I would like to undo committed changes. Via updates in another transaction...

> OTOH there is this new idea from Date that transactions
> are an unnecessary complication because we should just have atomic
> statements that make all the transaction's updates in parallel.
> This would take COMMIT from us, but than perhaps we should create
> something like a branch of work particular to an interactive
> session, even perhaps using a transaction-like sintax:
>
> BEGIN WORK; [\205] COMMIT;
What do you mean by "branch of work"? A private workspace, like in optimistic concurrency control?

> A nice UI would then keep the user informed in a kinda
> status bar as to how many system resources he's using in
> uncommitted work.
>
>
> Now for the specific case of inserting back deleted data.
> I don't think it is reasonable. That because if I say "delete all
> this user's from the key I used to specify the user. I know it's
> gone.

First, you know that it's gone, but you might not know what it was. How can you judge about it's value and be allowed to delete it?

Next, if you say "delete all...", then you don't need view updates. You can access the base relation directly.

In particular, you need deletion rights on the base relation. This is an important point. (If your initial design did not include deletion rights on the base relation it was flawed, as deletions on the view imply deletions on the base relation.)

Now, I feel that users with the right to delete should also be allowed to select, just to make sure they know what they are doing. Of course, this is just my opinion.

Besides, I believe that "problematic" view updates (i.e., those that cannot be undone using further view updates) can be avoided by proper schema design. (As I don't know how people are using view updates I cannot prove this. Unfortunately.)

> Now if I wanted to have it back, I'd like to have a nice
> way of restoring deleted data, like using a backup in the way of
> HSMs: it would be nice to tell a system "give me whatever was in
> such and such a tuple at such and such a time". A relational
> system could be able to fetch that data from backup thru an HSM
> and give me that data, but that is orthogonal to deleting it.

I'm not sure what you mean by "whatever was in such and such a tuple". If we are in the setting of a deletion from a projection view then the user does not have access to all attributes of the base relation tuples. Hence, the system must not show them to the user via backups.

Another problem coming up here is that different users may have changed the tuple since "such and such a time". The updates of those users will be lost when a backup is installed, violating serializability. When using view updates instead of backups (which is possible in my setting), the effects of those updates will be based upon all intermediate changes.

> > 3. As a db admin, I expect that users know what they are doing
> > when they manipulate data. Does anybody else believe that this
> > is a reasonable requirement?
>
> Well, that's what we have access control and declared
> constraints for.

I don't see that. What constraint prevents a user from deleting in a projection that preserves the key? (I assume that users access the database via an external schema. In particular, users should neither know nor care whether they access a view or a base relation.)

> > Again, for view updates this is often not the case. E.g., in
> > case of projections users delete tuples they do not know
> > completely; in case of deletions from unions they do not even
> > know from which relation something gets deleted.
>
> And why it should matter? Suffices them to know to
> identify which information they deleted; the system (and therefore
> the DBA) should know where it was deleted from because he knows
> the constraints derivation rules. Then the information can be
> fetched from a backup if necessary.

First, how many times may I ask the DBA to undo my updates before my account disappears?
Second, as suggested above, installing backups might violate serializability.

> > It turns out that (2) and (3) above are equivalent. I wrote a
> > paper about this issue, where I warn against using view updates.
> > If you are interested, take a look at:
> > http://dbms.uni-muenster.de/publications/downloads/view-updates.pdf
> > A small warning: An almost identical version of that paper got
> > rejected at ICDT'03. Basically, the anonymous referees did not
> > buy (2) and (3).
>
> I have to agree with them. While you have a nice point on
> your points (2) and (3) being equivalent, and your first
> paragraphs make for interesting reading, I don't agree with the
> assumptions that SQL is sane, bags are desirable, and reversal
> thru views are desirable.

I neither said that SQL is sane nor that bags are desirable, but that's the way how commercially successful databases work... In fact, my first observation implies that SQL in conjunction with bags is flawed.

Nevertheless, my argumentation does neither rely on the update language being SQL nor relations being bags. My point is that a large class of view updates (those without a constant complement translator; please see my paper for details) is dangerous and should not be allowed. My conjecture is that all those problematic view updates can be avoided by proper (external) schema design.

Jens Received on Fri Nov 08 2002 - 22:45:41 CET