Re: [HACKERS] [PATCH] Lockable views

Yugo Nagata wrote:
> On Fri, 27 Oct 2017 07:11:14 +0200
> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> > On Wed, Oct 11, 2017 at 11:36 AM, Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp> wrote:
> > > In the attached patch, only automatically-updatable views that do not have
> > > INSTEAD OF rules or INSTEAD OF triggers are lockable. It is assumed that
> > > those views definition have only one base-relation. When an auto-updatable
> > > view is locked, its base relation is also locked. If the base relation is a
> > > view again, base relations are processed recursively. For locking a view,
> > > the view owner have to have he priviledge to lock the base relation.
> >
> > Why is this the right behavior?
> >
> > I would have expected LOCK TABLE v to lock the view and nothing else.

> This discussion is one about 7 years ago when automatically-updatable views
> are not supported. Since 9.3, simple views can be updated as well as tables,
> so now I think it is reasonable that LOCK TABLE for views locks their base
> tables.

I agree with Yugo Nagata -- LOCK TABLE is in some cases necessary to
provide the right isolation so that an operation can be carried out
without interference from other processes that want to process the same
data -- and if a view is provided on top of existing tables, preventing
concurrent changes to the data returned by the view is done by locking
the view and recursively the tables that the view are built on, as if
the view were a table. This is why LOCK TABLE is the right command to
do it.

Also, if an application is designed using a table and concurrent changes
are prevented via LOCK TABLE, then when the underlying schema is changed
and the table is replaced by a view, the application continues to work
unchanged; not only syntactically (no error because of table-locking a
view) but also semantically because new application code that modifies
data in underlying tables from paths other than the view will need to
compete with those through the view, which is correct.

> > See http://postgr.es/m/AANLkTi=KupesJHRdEvGfbT30aU_iYRO6zwK+fwwY_sGd@mail.gmail.com
> > for previous discussion of this topic.

> If we want to lock only the view, it seems to me that LOCK VIEW syntax is good.
> However, to realize this, changing the syntax to avoid a shift/reduce
> conflict will be needed as disucussed in the "LOCK for non-tables" thread.

+1 on making TABLE mandatory in LOCK [TABLE], since that will support
this new LOCK VIEW thing as well as locking other object types.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services