Re: [HACKERS] [PATCH] Lockable views - Mailing list pgsql-hackers
From | Tatsuo Ishii |
---|---|
Subject | Re: [HACKERS] [PATCH] Lockable views |
Date | |
Msg-id | 20171012.085026.1813681504034082258.t-ishii@sraoss.co.jp Whole thread Raw |
In response to | [HACKERS] [PATCH] Lockable views (Yugo Nagata <nagata@sraoss.co.jp>) |
Responses |
Re: [HACKERS] [PATCH] Lockable views
|
List | pgsql-hackers |
> 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
pgsql-hackers by date: