[HACKERS] [PATCH] Lockable views

Hi,

Attached is a patch to enable views to be locked.

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



Regards,

-- 
Yugo Nagata <nagata@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