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 |
Message-ID: | 20180328120829.d844b159.nagata@sraoss.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
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.
Regards,
> 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>
--
Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>
Attachment | Content-Type | Size |
---|---|---|
lock_view-v9.patch | text/x-diff | 14.1 KB |
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 |