Re: [PATCH] Lockable views

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

Current the patch ignores t2 in the case above.

So we have options below:

- Leave as it is (ignore tables appearing in a subquery)

- Lock all tables including in a subquery

- Check subquery in the view definition. If there are some tables
involved, emit an error and abort.

The first one might be different from what users expect. There may be
a risk that the second one could cause deadlock. So it seems the third
one seems to be the safest IMO.

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