Awhile ago I said that I wanted to create a new flavor of table-level
lock for concurrent VACUUM to get on a table.  RowExclusiveLock is
not the right thing because it is not self-exclusive, whereas we don't
want more than one VACUUM mangling a table at a time.  But anything
higher locks out concurrent writers, which we don't want either.
So we need an intermediate lock type that will conflict with itself
as well as with ShareLock and above.  (It must conflict with ShareLock
since we don't want new indexes being created during VACUUM either...)

I'm having a hard time coming up with a name, though.  I originally
called it "VacuumLock" but naming it after its primary use seems bogus.
Some other possibilities that I don't much like either:

	SchemaLock	--- basically we're locking down the table schema
	WriteShareLock	--- sharing access with writers

Any better ideas out there?  Where did the existing lock type names
come from, anyway?  (Not SQL92 or SQL99, for sure.)

BTW, I'm assuming that I should make the new lock type available
at the user level as a LOCK TABLE option.  Any objections to that?

			regards, tom lane

Thu, 21 Jun 2001 17:15:25 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Good name for new lock type for VACUUM?
Tom Lane wrote:

>Awhile ago I said that I wanted to create a new flavor of table-level
>lock for concurrent VACUUM to get on a table.  RowExclusiveLock is
>not the right thing because it is not self-exclusive, whereas we don't
>want more than one VACUUM mangling a table at a time.  But anything
>higher locks out concurrent writers, which we don't want either.
>So we need an intermediate lock type that will conflict with itself
>as well as with ShareLock and above.  (It must conflict with ShareLock
>since we don't want new indexes being created during VACUUM either...)
>
*snip*

>
>BTW, I'm assuming that I should make the new lock type available
>at the user level as a LOCK TABLE option.  Any objections to that?
>
I think that type of lock would best be kept to the system level.  

*thinking out loud*
If your goal is to have it used more often, then user level might 
provide more opportunities for testing.  However, I can't really think 
of any situation where it would be beneficial to a user.  The rest of 
the locks seem to take care of everything else.

Is it going to timeout?  If a connection is dropped by a user, will the 
lock release?



Thu, 21 Jun 2001 18:40:16 -0500
Thomas Swan <tswan@olemiss.edu>
Re: Good name for new lock type for VACUUM?
Thomas Swan <tswan@olemiss.edu> writes:
> I think that type of lock would best be kept to the system level.  

Why?

I don't have a scenario offhand where it'd be useful, but if we've
discovered it's useful for VACUUM then there may be cases where a lock
with these properties would be useful to users as well.  Besides, we
have several lock types that are exposed to users even though we've
found no uses for them at the system level.

> Is it going to timeout?  If a connection is dropped by a user, will the 
> lock release?

No, and yes, same as any other lock.

			regards, tom lane

Thu, 21 Jun 2001 20:10:14 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Re: Good name for new lock type for VACUUM? 
Tom Lane writes:

> Awhile ago I said that I wanted to create a new flavor of table-level
> lock for concurrent VACUUM to get on a table.

> I'm having a hard time coming up with a name, though.  I originally
> called it "VacuumLock" but naming it after its primary use seems bogus.

Not that a name like "share row exclusive" is any less bogus. ;-)

I've been staring at the lock names for an hour now and the best name I've
come up with is SHARE UPDATE EXCLUSIVE, as in "share update, otherwise
exclusive" (the implication being that update would allow select as well),
or some permutation thereof.

Any other constructs that follow the existing patterns lead to
significantly less desirable names like

EXCLUSIVE ROW EXCLUSIVE == like ROW EXCLUSIVE, but self-exclusive, or

ROW EXCLUSIVE SHARE == like SHARE, but allows ROW EXCLUSIVE

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Fri, 22 Jun 2001 21:58:20 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Good name for new lock type for VACUUM?
Peter Eisentraut <peter_e@gmx.net> writes:
> I've been staring at the lock names for an hour now and the best name I've
> come up with is SHARE UPDATE EXCLUSIVE, as in "share update, otherwise
> exclusive" (the implication being that update would allow select as well),
> or some permutation thereof.

Okay with me, unless someone else comes up with a better idea...

			regards, tom lane

Fri, 22 Jun 2001 15:59:18 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Good name for new lock type for VACUUM? 
> Tom Lane writes:
> 
> > Awhile ago I said that I wanted to create a new flavor of table-level
> > lock for concurrent VACUUM to get on a table.
> 
> > I'm having a hard time coming up with a name, though.  I originally
> > called it "VacuumLock" but naming it after its primary use seems bogus.
> 
> Not that a name like "share row exclusive" is any less bogus. ;-)
> 
> I've been staring at the lock names for an hour now and the best name I've
> come up with is SHARE UPDATE EXCLUSIVE, as in "share update, otherwise
> exclusive" (the implication being that update would allow select as well),
> or some permutation thereof.
> 
> Any other constructs that follow the existing patterns lead to
> significantly less desirable names like
> 
> EXCLUSIVE ROW EXCLUSIVE == like ROW EXCLUSIVE, but self-exclusive, or
> 
> ROW EXCLUSIVE SHARE == like SHARE, but allows ROW EXCLUSIVE

Sounds good.  I documented the lock types as best I could in the LOCK
manual page.  I think that is as good as we can do to explain them.

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

Fri, 22 Jun 2001 16:58:01 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Good name for new lock type for VACUUM?
> -----Original Message-----
> From: Tom Lane
> 
> Peter Eisentraut <peter_e@gmx.net> writes:
> > I've been staring at the lock names for an hour now and the 
> best name I've
> > come up with is SHARE UPDATE EXCLUSIVE, as in "share update, otherwise
> > exclusive" (the implication being that update would allow 
> select as well),
> > or some permutation thereof.
> 
> Okay with me, unless someone else comes up with a better idea...
> 

I have no better idea but I hope to leave VacuumLock as an alias
because I can't remember others.

regards,
Hiroshi Inoue

Sat, 23 Jun 2001 11:53:39 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: Good name for new lock type for VACUUM? 
> -----Original Message-----
> From: Hiroshi Inoue
> > -----Original Message-----
> > From: Tom Lane
> >
> > Peter Eisentraut <peter_e@gmx.net> writes:
> > > I've been staring at the lock names for an hour now and the
> > best name I've
> > > come up with is SHARE UPDATE EXCLUSIVE, as in "share update, otherwise
> > > exclusive" (the implication being that update would allow
> > select as well),
> > > or some permutation thereof.
> >
> > Okay with me, unless someone else comes up with a better idea...
> >
>
> I have no better idea but I hope to leave VacuumLock as an alias
> because I can't remember others.
>

Isn't it a better idea to have a separate 'SELF EXCLUSIVE' lock
which conflicts with only itself ?

regards,
HIroshi Inoue


Sat, 23 Jun 2001 23:39:39 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: Good name for new lock type for VACUUM? 
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> Isn't it a better idea to have a separate 'SELF EXCLUSIVE' lock
> which conflicts with only itself ?

*Only* itself?  What would that be useful for?  Not for locking
tables, anyway --- if you don't conflict with AccessExclusiveLock
then it's possible for someone to drop the table while you're
working on it.

			regards, tom lane

Sat, 23 Jun 2001 11:51:42 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Good name for new lock type for VACUUM? 
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> 
> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> > Isn't it a better idea to have a separate 'SELF EXCLUSIVE' lock
> > which conflicts with only itself ?
> 
> *Only* itself?  What would that be useful for?

Isn't VacuumLock = RowExclusiveLock  + SelfExclusiveLock 
for the table ?

regards,
Hiroshi Inoue

Sun, 24 Jun 2001 06:11:01 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: Good name for new lock type for VACUUM? 
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> Isn't it a better idea to have a separate 'SELF EXCLUSIVE' lock
> which conflicts with only itself ?
>> 
>> *Only* itself?  What would that be useful for?

> Isn't VacuumLock = RowExclusiveLock  + SelfExclusiveLock 
> for the table ?

Oh, I see, you're suggesting acquiring two separate locks on the table.
Hmm.  There would be a risk of deadlock if two processes tried to
acquire these locks in different orders.  That's not a big problem for
VACUUM, since all processes would presumably be executing the same
VACUUM code.  But it raises questions about just how useful this lock
type would be in general-purpose use.  You could never acquire *only*
this lock type, it'd have to be combined with something else, so it
seems like any usage would have to be carefully examined for deadlocks.

Still, it's an interesting alternative.  Comments anyone?

			regards, tom lane

Sat, 23 Jun 2001 17:29:03 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Good name for new lock type for VACUUM? 
> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> > Isn't it a better idea to have a separate 'SELF EXCLUSIVE' lock
> > which conflicts with only itself ?
> >> 
> >> *Only* itself?  What would that be useful for?
> 
> > Isn't VacuumLock = RowExclusiveLock  + SelfExclusiveLock 
> > for the table ?
> 
> Oh, I see, you're suggesting acquiring two separate locks on the table.
> Hmm.  There would be a risk of deadlock if two processes tried to
> acquire these locks in different orders.  That's not a big problem for
> VACUUM, since all processes would presumably be executing the same
> VACUUM code.  But it raises questions about just how useful this lock
> type would be in general-purpose use.  You could never acquire *only*
> this lock type, it'd have to be combined with something else, so it
> seems like any usage would have to be carefully examined for deadlocks.
> 
> Still, it's an interesting alternative.  Comments anyone?

SelfExclusiveLock is clear and can't be confused with other lock types.

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

Sat, 23 Jun 2001 18:11:07 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Good name for new lock type for VACUUM?
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Still, it's an interesting alternative.  Comments anyone?

> SelfExclusiveLock is clear and can't be confused with other lock types.

It could possibly be made a little less dangerous if "SelfExclusiveLock"
were defined to conflict with itself and AccessExclusiveLock (and
nothing else).  That would at least mean that holding SelfExclusiveLock
would guarantee the table not go away under you; so there might be
scenarios where holding just that lock would make sense.

Still, I'm not sure that this lock type is as flexible as it seems at
first glance.  What you'd probably really want it to do is guarantee
that no other instance of your same operation (whatever it is) is
running, and then you'd acquire another lock type to lock out other
operations that you couldn't run in parallel with.  Sounds great,
except there'd only be one SelfExclusiveLock per table.  So, for
example, your operation would conflict with VACUUM whether you wanted
it to or not.

Between that and the need-two-locks hazards, I'm unconvinced that this
is a better idea.

			regards, tom lane

Sat, 23 Jun 2001 18:26:38 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Good name for new lock type for VACUUM? 
Tom Lane wrote:
> 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> Still, it's an interesting alternative.  Comments anyone?
> 
> > SelfExclusiveLock is clear and can't be confused with other lock types.
> 
> It could possibly be made a little less dangerous if "SelfExclusiveLock"
> were defined to conflict with itself and AccessExclusiveLock (and
> nothing else).  That would at least mean that holding SelfExclusiveLock
> would guarantee the table not go away under you; so there might be
> scenarios where holding just that lock would make sense.
> 
> Still, I'm not sure that this lock type is as flexible as it seems at
> first glance. 

I don't think "SelfExclusiveLock" is an excellent lock either.
However it seems to point out the reason why we couldn't
place(name) "VacuumLock" properly in our locking system.

regards,
Hiroshi Inoue

Mon, 25 Jun 2001 10:42:40 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: Good name for new lock type for VACUUM?