Re: [HACKERS] [PATCH] Lockable views

On Tue, 6 Feb 2018 11:12:37 -0500
Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Tue, Feb 6, 2018 at 1:28 AM, Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp> wrote:
> >> But what does that have to do with locking?
> >
> > Well, if the view is not updatable, I think there will be less point
> > to allow to lock the base tables in the view because locking is
> > typically used in a case when updates are required.
> >
> > Of course we could add special triggers to allow to update views that
> > are not automatically updatable but that kind of views are tend to
> > complex and IMO there's less need the automatic view locking feature.
>
> Hmm. Well, I see now why you've designed the feature in the way that
> you have, but I guess it still seems somewhat arbitrary to me. If you
> ignore the deadlock consideration, then there's no reason not to
> define the feature as locking every table mentioned anywhere in the
> query, including subqueries, and it can work for all views whether
> updatable or not. If the deadlock consideration is controlling, then
> I guess we can't do better than what you have, but I'm not sure how
> future-proof it is. If in the future somebody makes views updateable
> that involve a join, say from the primary key of one table to a unique
> key of another so that no duplicate rows can be introduced, then
> they'll either have to write code to make this feature identify and
> lock the "main" table, which I'm not sure would be strong enough in
> all cases, or lock them all, which reintroduces the deadlock problem.
>
> Personally, I would be inclined to view the deadlock problem as not
> very important. I just don't see how that is going to come up very

I agree that the deadlock won't occur very often and this is not
so important.

I have updated the lockable-view patch to v8.

This new version doen't consider the deadlock problem, and all tables
or views appearing in the view definition query are locked recursively.
Also, this allows all kinds of views to be locked even if it is not
auto-updatable view.

> often. What I do think will be an issue is that if you start locking
> lots of tables, you might prevent the system from getting much work
> done, whether or not you also cause any deadlocks. But I don't see
> what we can do about that, really. If users want full control over
> which tables get locked, then they have to name them explicitly. Or
> alternatively, maybe they should avoid the need for full-table locks
> by using SSI, gaining the benefits of (1) optimistic rather than
> pessimistic concurrency control, (2) finer-grained locking, and (3)
> not needing to issue explicit LOCK commands.

> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

--
Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>