Re: [PATCH] Lockable views

> Hi,
>
> Attached is a patch to enable views to be locked.

Nice.

> PostgreSQL has supported automatically updatable views since 9.3, so we can
> udpate simply defined views like regular tables. However, currently,
> table-level locks on views are not supported. We can not execute LOCK TABLE
> for views, while we can get row-level locks by FOR UPDATE/SHARE. In some
> situations that we need table-level locks on tables, we may also need
> table-level locks on automatically updatable views. Although we can lock
> base-relations manually, it would be useful if we can lock views without
> knowing the definition of the views.
>
> In the attached patch, only automatically-updatable views that do not have
> INSTEAD OF rules or INSTEAD OF triggers are lockable. It is assumed that
> those views definition have only one base-relation. When an auto-updatable
> view is locked, its base relation is also locked. If the base relation is a
> view again, base relations are processed recursively. For locking a view,
> the view owner have to have he priviledge to lock the base relation.
>
> * Example
>
> test=# CREATE TABLE tbl (i int);
> CREATE TABLE
>
> test=# CREATE VIEW v1 AS SELECT * FROM tbl;
> CREATE VIEW
> test=# BEGIN;
> BEGIN
> test=# LOCK TABLE v1;
> LOCK TABLE
> test=# SELECT relname, locktype, mode FROM pg_locks,pg_class c WHERE c.oid=relation AND relname NOT LIKE 'pg%';
> relname | locktype | mode
> ---------+----------+---------------------
> tbl | relation | AccessExclusiveLock
> v1 | relation | AccessExclusiveLock
> (2 rows)
>
> test=# END;
> COMMIT
>
> test=# CREATE VIEW v2 AS SELECT * FROM v1;
> CREATE VIEW
> test=# BEGIN;
> BEGIN
> test=# LOCK TABLE v2;
> LOCK TABLE
> test=# SELECT relname, locktype, mode FROM pg_locks,pg_class c WHERE c.oid=relation AND relname NOT LIKE 'pg%';
> relname | locktype | mode
> ---------+----------+---------------------
> v2 | relation | AccessExclusiveLock
> tbl | relation | AccessExclusiveLock
> v1 | relation | AccessExclusiveLock
> (3 rows)
>
> test=# END;
> COMMIT
>
> 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

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

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp