Re: [HACKERS] disallow LOCK on a view - the Tom Lane remix

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

* 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

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

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

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