* 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

Tue, 29 Aug 2000 11:46:59 -0700
Alfred Perlstein <bright@wintelcom.net>
Re: [HACKERS] 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
Tue, 29 Aug 2000 15:16:12 -0400
Mark Hollomon <mhh@mindspring.com>
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

Tue, 29 Aug 2000 18:58:41 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] disallow LOCK on a view - the Tom Lane remix 
* 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."

Tue, 29 Aug 2000 16:14:00 -0700
Alfred Perlstein <bright@wintelcom.net>
Re: disallow LOCK on a view - the Tom Lane remix
> -----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

Wed, 30 Aug 2000 08:34:48 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: disallow LOCK on a view - the Tom Lane remix
* 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]

Tue, 29 Aug 2000 17:23:02 -0700
Alfred Perlstein <bright@wintelcom.net>
Re: disallow LOCK on a view - the Tom Lane remix
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

Tue, 29 Aug 2000 21:03:00 -0400
Mark Hollomon <mhh@mindspring.com>
Re: disallow LOCK on a view - the Tom Lane remix
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

Tue, 29 Aug 2000 23:52:28 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] disallow LOCK on a view - the Tom Lane remix 
* 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

Tue, 29 Aug 2000 22:05:11 -0700
Alfred Perlstein <bright@wintelcom.net>
Re: [HACKERS] disallow LOCK on a view - the Tom Lane remix
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

Wed, 30 Aug 2000 08:18:02 -0400
"Mark Hollomon" <mhh@nortelnetworks.com>
Re: disallow LOCK on a view - the Tom Lane remix
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

Tue, 12 Sep 2000 00:34:55 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] disallow LOCK on a view - the Tom Lane remix
> 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

Tue, 12 Sep 2000 00:35:43 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] disallow LOCK on a view - the Tom Lane remix