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

On Tue, 17 Oct 2017 11:59:05 +0900 (JST)
Tatsuo Ishii <ishii@sraoss.co.jp> wrote:

> > I'm a bit confused. What is difference between tables and functions
> > in a subquery with regard to view locking? I think also none view queries
> > using a subquery do not care about the changes of tables in the 
> > subquery while executing the query. I might be misnderstanding
> > the problem you mentioned.
> 
> The difference is in the function cases we concern the function
> definition. While the table cases need to care about table
> definitions *and* contents of the table.
> 
> If we are changing the table definition, AccessExclusiveLock will be
> held for the table and the updation will be blocked anyway. So we
> don't need to care about the table definition changes.
> 
> On the other hand, table contents changes need to be cared because no
> automatic locking are held in some cases. I think whether tables in
> the subquery need locking or not is depending on use cases.
> 
> So I dug into the previous candidates a little bit more:
> 
> 1) Leave as it is (ignore tables appearing in a subquery)
> 
> 2) Lock all tables including in a subquery
> 
> 3) Check subquery in the view definition. If there are some tables
>    involved, emit an error and abort.
> 
> I think one of the problems with #2 is, we will lock tables involved
> by the view in random order, which could cause unwanted dead
> locks. This is not good and I cannot see any easy way to avoid
> this. Also some tables may not need to be locked.
> 
> Problem with #3 is, it does not help a user who wants to control
> lockings by himself/herself.
> 
> So it seem #1 is the most reasonable way to deal with the problem
> assuming that it's user's responsibility to take appropriate locks on
> the tables in the subquery.

Thank you for your response. I agree to adopt #1.

> 
> > BTW, I found that if we have to handle subqueries in where clause, we would
> > also have to care about subqueries in target list... The view defined as
> > below is also updatable.
> > 
> >  =# create view v7 as select (select * from tbl2 limit 1) from tbl;
> 
> The view is not updatable. You will get something like if you try to update v7:
> 
> DETAIL:  Views that have no updatable columns are not automatically updatable.

Although you can not insert into or update v7, you can delete tuples from v7
since it just delete tuples from table tbl regardless with any column.
However, as disussed above, if it is user's responsibility to take appropriate
locks on the tables in subqueries in the target list, we don't need to
care about these. 

> 
> On the other hand this:
> 
> create view v7 as select i, (select j from tbl2 limit 1) from tbl;
> 
> will be updatable. In this case column j of v7 will never be
> updatable. And you should do something like:
> 
> insert into v7(i) values...
> 
> In short, you don't need to care about a subquery appearing in the TLE
> as far as the view locking concerns.
> 
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
> 
> 
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers


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