Horton

Thread

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

    Alfred Perlstein <bright@wintelcom.net> — 2000-08-29T18:46:59Z

    * 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

  2. disallow LOCK on a view - the Tom Lane remix

    Mark Hollomon <mhh@mindspring.com> — 2000-08-29T19:16:12Z

    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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-08-29T22:58:41Z

    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

  4. Re: disallow LOCK on a view - the Tom Lane remix

    Alfred Perlstein <bright@wintelcom.net> — 2000-08-29T23:14:00Z

    * 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."

  5. RE: disallow LOCK on a view - the Tom Lane remix

    Hiroshi Inoue <inoue@tpf.co.jp> — 2000-08-29T23:34:48Z

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

  6. Re: disallow LOCK on a view - the Tom Lane remix

    Alfred Perlstein <bright@wintelcom.net> — 2000-08-30T00:23:02Z

    * 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]

  7. Re: disallow LOCK on a view - the Tom Lane remix

    Mark Hollomon <mhh@mindspring.com> — 2000-08-30T01:03:00Z

    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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-08-30T03:52:28Z

    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

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

    Alfred Perlstein <bright@wintelcom.net> — 2000-08-30T05:05:11Z

    * 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

  10. Re: disallow LOCK on a view - the Tom Lane remix

    Mark Hollomon <mhh@nortelnetworks.com> — 2000-08-30T12:18:02Z

    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

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

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-09-12T04:34:55Z

    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

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

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-09-12T04:35:43Z

    > 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