Re: [HACKERS] [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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers