Re: [HACKERS] [PATCH] Lockable views

On Mon, 16 Oct 2017 10:07:48 +0900 (JST)
Tatsuo Ishii <ishii@sraoss.co.jp> wrote:

> >> >> test=# CREATE VIEW v3 AS SELECT count(*) FROM v1;
> >> >> CREATE VIEW
> >> >> test=# BEGIN;
> >> >> BEGIN
> >> >> test=# LOCK TABLE v3;
> >> >> ERROR:  cannot lock view "v3"
> >> >> DETAIL:  Views that return aggregate functions are not automatically updatable.
> >> > 
> >> > It would be nice if the message would be something like:
> >> > 
> >> > DETAIL:  Views that return aggregate functions are not lockable
> > 
> > This uses messages from view_query_is_auto_updatable() of the rewrite system directly. 
> > Although we can modify the messages, I think it is not necessary for now
> > since we can lock only automatically updatable views.
> 
> You could add a flag to view_query_is_auto_updatable() to switch the
> message between
> 
>  DETAIL:  Views that return aggregate functions are not automatically updatable.
> 
> and
> 
>  DETAIL:  Views that return aggregate functions are not lockable

OK. I'll change view_query_is_auto_updatable() so.

> 
> >> > I wonder if we should lock tables in a subquery as well. For example,
> >> > 
> >> > create view v1 as select * from t1 where i in (select i from t2);
> >> > 
> >> > In this case should we lock t2 as well?
> >> 
> >> Current the patch ignores t2 in the case above.
> >> 
> >> So we have options below:
> >> 
> >> - Leave as it is (ignore tables appearing in a subquery)
> >> 
> >> - Lock all tables including in a subquery
> >> 
> >> - Check subquery in the view definition. If there are some tables
> >>   involved, emit an error and abort.
> >> 
> >> The first one might be different from what users expect. There may be
> >> a risk that the second one could cause deadlock. So it seems the third
> >> one seems to be the safest IMO.
> > 
> > Make sense. Even if the view is locked, when tables in a subquery is
> > modified, the contents of view can change. To avoid it, we have to
> > lock tables, or give up to lock such views. 
> > 
> > We can say the same thing for functions in a subquery. If the definition
> > of the functions are changed, the result of the view can change.
> > We cannot lock functions, but should we abtain row-level lock on pg_proc
> > in such cases? (of cause, or give up to lock such views....)
> 
> I think we don't need to care about function definition changes used
> in where clauses in views. None view queries using functions do not
> care about the definition changes of functions while executing the
> query. So why updatable views need to care them?

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.

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;

> 
> > BTW, though you mentioned the risk of deadlocks, even when there
> > are no subquery, deadlock can occur in the current patch.
> > 
> > For example, lock a table T in Session1, and then lock a view V
> > whose base relation is T in Session2. Session2 will wait for 
> > Session1 to release the lock on T. After this, when Session1 try to
> > lock view V, the deadlock occurs and the query is canceled.
> 
> You are right. Dealocks could occur in any case.
> 
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp


-- 
Yugo Nagata <nagata@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