On Mon, 16 Oct 2017 10:07:48 +0900 (JST) Tatsuo Ishii <is...@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 <nag...@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
