Thread: disallow LOCK on a view - the Tom Lane remix
Here is a patch against CVS (without my earlier patch) to disallow LOCK x if x is a view. It does not use the SPI interface. -- Mark Hollomon mhh@mindspring.com
Attachment
Alfred Perlstein <bright@wintelcom.net> writes:
> * Mark Hollomon <mhh@mindspring.com> [000829 11:26] wrote:
>> Here is a patch against CVS (without my earlier patch)
>> to disallow
>> LOCK x
>> if x is a view.
> Waitasec, why?? This can be very useful if you want to atomically lock
> something that sits "in front" of several other tables that you need to
> do something atomically with.
> Does it cause corruption if allowed?
No, but I doubt that it does anything useful either ... the system
is going to be acquiring locks on the referenced tables, not the
view itself.
A full (exclusive) LOCK on the view itself might work (by preventing
other backends from reading the view definition), but lesser types of
locks would certainly not operate as desired. Even an exclusive lock
wouldn't prevent re-execution of previously planned queries against
the view, as could happen in plpgsql functions for example.
Moreover, a lock on the view would not prevent people from
accessing/manipulating the referenced tables; they'd just have to
not go through the view.
All in all, the behavior seems squirrelly enough that I agree with
Mark: better to consider it a disallowed operation than to have to
deal with complaints that it didn't do whatever the user thought
it would do.
regards, tom lane
Alfred Perlstein <bright@wintelcom.net> writes:
> Ok, I'm wondering if this patch will cause problems locking a table
> that has had:
> CREATE RULE "_RETfoo" AS ON SELECT TO foo DO INSTEAD SELECT * FROM foo1;
> I need to be able to lock the table 'foo' exclusively while I swap
> out the underlying rule to forward to another table.
Uh, do you actually need any sort of lock for that?
Seems to me that if you do
BEGIN;
DELETE RULE "_RETfoo";
CREATE RULE "_RETfoo" AS ...;
COMMIT;
then any other transaction will see either the old rule definition
or the new one. No intermediate state, no need for a lock as such.
BTW, this seems to be a counterexample for my prior suggestion that
pg_class should have a "relviewrule" OID column. If it did, you'd
have to update that field when doing something like the above.
Pain-in-the-neck factor looms large...
regards, tom lane
* Mark Hollomon <mhh@mindspring.com> [000829 11:26] wrote: > Here is a patch against CVS (without my earlier patch) > to disallow > > LOCK x > > if x is a view. > > It does not use the SPI interface. Waitasec, why?? This can be very useful if you want to atomically lock something that sits "in front" of several other tables that you need to do something atomically with. Does it cause corruption if allowed? thanks, -Alfred
* Tom Lane <tgl@sss.pgh.pa.us> [000829 20:52] wrote: > Alfred Perlstein <bright@wintelcom.net> writes: > > Ok, I'm wondering if this patch will cause problems locking a table > > that has had: > > CREATE RULE "_RETfoo" AS ON SELECT TO foo DO INSTEAD SELECT * FROM foo1; > > I need to be able to lock the table 'foo' exclusively while I swap > > out the underlying rule to forward to another table. > > Uh, do you actually need any sort of lock for that? > > Seems to me that if you do > BEGIN; > DELETE RULE "_RETfoo"; > CREATE RULE "_RETfoo" AS ...; > COMMIT; > then any other transaction will see either the old rule definition > or the new one. No intermediate state, no need for a lock as such. > Ugh! I keep on forgetting that transactions are atomic. Thanks. > BTW, this seems to be a counterexample for my prior suggestion that > pg_class should have a "relviewrule" OID column. If it did, you'd > have to update that field when doing something like the above. > Pain-in-the-neck factor looms large... I'd prefer this stuff be as simple as possible, it's already getting quite complex. thanks, -Alfred
OK, previous patch unapplied, and this patch was applied. > Here is a patch against CVS (without my earlier patch) > to disallow > > LOCK x > > if x is a view. > > It does not use the SPI interface. > > -- > Mark Hollomon > mhh@mindspring.com [ Attachment, skipping... ] -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> Pain-in-the-neck factor looms large... Can we copyright that term? :-) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026