Re: [PATCH] Lockable views

>> >> 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

>> > 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?

> 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