Re: [HACKERS] [PATCH] Lockable views

From: Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>
To: Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp>, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] [PATCH] Lockable views
Date: 2018-03-28 03:08:29
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Tue, 27 Mar 2018 23:28:04 +0900
Yugo Nagata <nagata(at)sraoss(dot)co(dot)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.


> 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:
> > The Enterprise PostgreSQL Company
> --
> Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>

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

Attachment Content-Type Size
lock_view-v9.patch text/x-diff 14.1 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2018-03-28 03:28:19 Re: Parallel Aggregates for string_agg and array_agg
Previous Message Peter Geoghegan 2018-03-28 02:58:21 Re: [HACKERS] MERGE SQL Statement for PG11