> 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. > 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. 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 (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers