Re: [PATCH] Lockable views

> 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