Re: [PATCH] Lockable views

On Thu, 12 Oct 2017 13:11:45 +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.

> >
> >> test=# END;
> >> ROLLBACK
> >>
> >> test=# CREATE FUNCTION fnc() RETURNS trigger AS $$ BEGIN RETURN NEW; END; $$ LANGUAGE plpgsql;
> >> CREATE FUNCTION
> >> test=# CREATE TRIGGER trg INSTEAD OF INSERT ON v1 FOR EACH ROW EXECUTE PROCEDURE fnc();
> >> CREATE TRIGGER
> >> test=# BEGIN;
> >> BEGIN
> >> test=# LOCK TABLE v1;
> >> ERROR: cannot lock view "v1"
> >> DETAIL: views that have an INSTEAD OF trigger are not lockable
> >> test=# END;
> >> ROLLBACK
> >
> > 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....)

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.

Is this unacceptable behavior?

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