Re: [HACKERS] [PATCH] Lockable views - Mailing list pgsql-hackers

On Tue, 27 Mar 2018 23:28:04 +0900
Yugo Nagata <nagata@sraoss.co.jp> wrote:

I found the previous patch was broken and this can't handle
views that has subqueries as bellow;

　CREATE VIEW lock_view6 AS SELECT * from (select * from lock_tbl1) sub;

I fixed this and attached the updated version including additional tests.

Regards,

> On Tue, 6 Feb 2018 11:12:37 -0500
> Robert Haas <robertmhaas@gmail.com> wrote:
> 
> > On Tue, Feb 6, 2018 at 1:28 AM, Tatsuo Ishii <ishii@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
> 
> 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@sraoss.co.jp>


-- 
Yugo Nagata <nagata@sraoss.co.jp>