Tom Lane wrote:
>
> Fernando Nasser <fnasser@cygnus.com> writes:
> > I guess the principle (for Oracle folks) was that, for the user, there should
> > be no distinction between a real table and a view. Thus, it should not matter
> > for the user if the thing that is being locked is a real table or if it
> > is actually being implemented as a view. Consider that it may have been
> > a table one day, but the DBA changed it into a view. So that SQL will
> > not work anymore and give the "ERROR: LOCK TABLE: v is not a table" message.
> > This violates the Data Independence notion.
>
> I don't really buy this, because it makes life difficult for DBAs who
> want to do creative things with views. Update rules don't necessarily
> touch exactly the same set of tables that are mentioned in the select
> rule. But that's the only set that a LOCK implementation might possibly
> know about.
>
> Consider: for the view as view (ie, select) there's no real need to do
> locking at all. The implicit read locks that will be grabbed as the
> view is expanded will do fine. For updates, the behavior can and should
> be defined by the rewrite rules that the DBA supplies. (Hmm, I'm not
> sure that LOCK is one of the allowed query types in a rule --- if not,
> it probably should be, so that the rule author can ensure the right
> kinds of locks are grabbed in the right sequence.)
>
These are good points. I suppose Oracle needs this because they
have DBMS-implemented updatable views (not with rules as we do).
BTW, it seems we have a SQL non-conformance issue here: views that are
only projections+selections of a single base table are SQL-updatable.
We should allow updates to those by rewriting them to refer to the base table.
And instead of just ignoring updates (unless we have rules in place) for
non-updatable views we should print some error like
"ERROR: attempt to modify non-updatable view".
> Another serious issue, which gets back to your original point, is that
> we have no good idea what order to lock the base tables in. If we had
> a concurrent-lock implementation it wouldn't matter, but in the absence
> of one I am not sure it's a good idea to put in a LOCK that is going to
> lock base tables in some arbitrary order.
>
This is true. It should not be allowed (as it is not useful, as you've
pointed out) for non-updatable views.
--
Fernando Nasser
Red Hat - Toronto E-Mail: fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario M4P 2C9
