Re: [HACKERS] [PATCH] Lockable views

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
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