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
* 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 15:58] wrote: > 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. This is a bug that could be solved with a sequence of callbacks hooked to a relation that are called when that relation changes. > 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. 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. -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
> -----Original Message----- > From: Alfred Perlstein > > * 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? > If I remember correctly,the problem is "LOCK VIEW" acquires a lock for the target view itself but doesn't acquire the lock for the base tables of the view. Regards. Hiroshi Inoue
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
On Tue, Aug 29, 2000 at 04:14:00PM -0700, Alfred Perlstein wrote: > > 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. > Yes, it would. 'foo' would be seen as view. Okay, this gives me a reason to to do it the hard way. I will try to add a relisview attribute to pg_class. That way, we can differentiate between tables with rules and things created with 'CREATE VIEW'. Hmmm... guess I'll need to change the definition of the pg_views view as well. -- Mark Hollomon mhh@mindspring.com
* Mark Hollomon <mhh@mindspring.com> [000829 17:13] wrote: > On Tue, Aug 29, 2000 at 04:14:00PM -0700, Alfred Perlstein wrote: > > > > 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. > > > > Yes, it would. 'foo' would be seen as view. > > Okay, this gives me a reason to to do it the hard way. > > I will try to add a relisview attribute to pg_class. > That way, we can differentiate between tables with rules > and things created with 'CREATE VIEW'. > > Hmmm... guess I'll need to change the definition of the pg_views > view as well. Ok, thanks I appreciate you taking my situation into consideration. thanks, -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
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
* 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
Tom Lane wrote: > > 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 was already considering the possiblity of a 'ALTER VIEW' command that would effectively allow you do that. CREATE VIEW bar as select * from foo1; ALTER VIEW bar as select * from foo2; It would update the "relviewrule" field. -- Mark Hollomon mhh@nortelnetworks.com ESN 451-9008 (302)454-9008
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