> 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