Re: [PATCH] Lockable views

On Mon, 16 Oct 2017 10:07:48 +0900 (JST)
Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)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(at)sraoss(dot)co(dot)jp>