On Tue, Feb 6, 2018 at 1:28 AM, Tatsuo Ishii <is...@sraoss.co.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 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
