Hi

Was already implemented the timeout on the "SELECT ... FOR UPDATE" (non-blocking lock) and/or is possible known if the lock exist on the specified ROW before executing the SELECT?

Please note: ours need is the timeout/verify at the ROW level, not at the table level. 

Is already OK? Is in the TODO list?
May you suggest an alternative method?

Thank you.








Hi
 
Was already implemented the timeout on the "SELECT 
... FOR UPDATE" (non-blocking lock) and/or is possible known if the lock exist 
on the specified ROW before executing the SELECT?
 
Please note: ours need is the timeout/verify at the 
ROW level, not at the table level. 
 
Is already OK? Is in the TODO list?
May you suggest an alternative method?
 
Thank you.
Thu, 21 Oct 2004 12:44:33 +0200
"ronzo" <m.ronzoni@nocerainformatica.net>
Postresql 8.0 Beta 3 - SELECT ... FOR UPDATE
On Thursday 21 October 2004 06:44, you wrote:
> Hi
>
> Was already implemented the timeout on the "SELECT ... FOR UPDATE"
> (non-blocking lock) and/or is possible known if the lock exist on the
> specified ROW before executing the SELECT?
>

No.

> Please note: ours need is the timeout/verify at the ROW level, not at the
> table level.
>
> Is already OK? Is in the TODO list?
> May you suggest an alternative method?
>
> Thank you.

You would need a more extensive implementation of row level locks than 
PostgreSQL currently offers. There have been discussions about this in the 
past, but afaik no one is actively working on it.  You can probably find more 
info in the archives about it, also I believe it is on the TODO list, so you 
might find some more detail by looking there.  

-- 
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Fri, 22 Oct 2004 08:41:58 -0400
Robert Treat <xzilla@users.sourceforge.net>
Re: Postresql 8.0 Beta 3 - SELECT ... FOR UPDATE

There is a statement_timeout that will control how long a statement can
execute before being cancelled.  We have never agreed that controlling
how long we wait for an individual lock is valuable.

---------------------------------------------------------------------------

Robert Treat wrote:
> On Thursday 21 October 2004 06:44, you wrote:
> > Hi
> >
> > Was already implemented the timeout on the "SELECT ... FOR UPDATE"
> > (non-blocking lock) and/or is possible known if the lock exist on the
> > specified ROW before executing the SELECT?
> >
> 
> No.
> 
> > Please note: ours need is the timeout/verify at the ROW level, not at the
> > table level.
> >
> > Is already OK? Is in the TODO list?
> > May you suggest an alternative method?
> >
> > Thank you.
> 
> You would need a more extensive implementation of row level locks than 
> PostgreSQL currently offers. There have been discussions about this in the 
> past, but afaik no one is actively working on it.  You can probably find more 
> info in the archives about it, also I believe it is on the TODO list, so you 
> might find some more detail by looking there.  
> 
> -- 
> Robert Treat
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 26 Oct 2004 13:35:13 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Postresql 8.0 Beta 3 - SELECT ... FOR UPDATE
ronzo wrote:
> Hi
> 
> Was already implemented the timeout on the "SELECT ... FOR UPDATE" (non-blocking lock) and/or is possible known if the lock exist on the specified ROW before executing the SELECT?
> 
> Please note: ours need is the timeout/verify at the ROW level, not at the table level. 
> 
> Is already OK? Is in the TODO list?
> May you suggest an alternative method?

We have discussed this at length and no one could state why having an
timeout per lock is any better than using a statement_timeout.

We can not do a NOWAIT on a single SELECT because there are alot of
locks used even for a select and having them fail randomly would be
useless.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 24 Nov 2004 22:13:23 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Postresql 8.0 Beta 3 - SELECT ... FOR UPDATE
On Wed, 2004-11-24 at 22:13 -0500, Bruce Momjian wrote:
> 
> We have discussed this at length and no one could state why having an
> timeout per lock is any better than using a statement_timeout.

Actually, I hit one.

I have a simple queue and a number of processes pulling jobs out of the
queue. Due to transactional requirements, the database is appropriate
for a first cut.

Anyway, a statement_timeout of 100ms is usually plenty to determine that
the job is being processed, and for one of the pollers to move on, but
every once in a while a large job (4 to 5MB chunk of data) would find
itself in the queue which takes more than 100ms to pull out.

Not a big deal, just bump the timeout in this case.

Anyway, it shows a situation where it would be nice to differentiate
between statement_timeout and lock_timeout OR it demonstrates that I
should be using userlocks...

-- 


Wed, 24 Nov 2004 22:33:45 -0500
Rod Taylor <pg@rbt.ca>
Re: Postresql 8.0 Beta 3 - SELECT ... FOR UPDATE
Rod Taylor wrote:
> On Wed, 2004-11-24 at 22:13 -0500, Bruce Momjian wrote:
> > 
> > We have discussed this at length and no one could state why having an
> > timeout per lock is any better than using a statement_timeout.
> 
> Actually, I hit one.
> 
> I have a simple queue and a number of processes pulling jobs out of the
> queue. Due to transactional requirements, the database is appropriate
> for a first cut.
> 
> Anyway, a statement_timeout of 100ms is usually plenty to determine that
> the job is being processed, and for one of the pollers to move on, but
> every once in a while a large job (4 to 5MB chunk of data) would find
> itself in the queue which takes more than 100ms to pull out.
> 
> Not a big deal, just bump the timeout in this case.
> 
> Anyway, it shows a situation where it would be nice to differentiate
> between statement_timeout and lock_timeout OR it demonstrates that I
> should be using userlocks...

Wouldn't a LOCK NOWAIT be a better solution?  That is new in 8.0.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 24 Nov 2004 22:47:56 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Postresql 8.0 Beta 3 - SELECT ... FOR UPDATE
On Wed, 2004-11-24 at 22:47 -0500, Bruce Momjian wrote:
> Rod Taylor wrote:
> > On Wed, 2004-11-24 at 22:13 -0500, Bruce Momjian wrote:
> > > 
> > > We have discussed this at length and no one could state why having an
> > > timeout per lock is any better than using a statement_timeout.
> > 
> > Actually, I hit one.
> > 
> > I have a simple queue and a number of processes pulling jobs out of the
> > queue. Due to transactional requirements, the database is appropriate
> > for a first cut.
> > 
> > Anyway, a statement_timeout of 100ms is usually plenty to determine that
> > the job is being processed, and for one of the pollers to move on, but
> > every once in a while a large job (4 to 5MB chunk of data) would find
> > itself in the queue which takes more than 100ms to pull out.
> > 
> > Not a big deal, just bump the timeout in this case.
> > 
> > Anyway, it shows a situation where it would be nice to differentiate
> > between statement_timeout and lock_timeout OR it demonstrates that I
> > should be using userlocks...
> 
> Wouldn't a LOCK NOWAIT be a better solution?  That is new in 8.0.

On a for update?

-- 


Wed, 24 Nov 2004 22:59:18 -0500
Rod Taylor <pg@rbt.ca>
Re: Postresql 8.0 Beta 3 - SELECT ... FOR UPDATE
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Rod Taylor wrote:
>> Anyway, it shows a situation where it would be nice to differentiate
>> between statement_timeout and lock_timeout OR it demonstrates that I
>> should be using userlocks...

> Wouldn't a LOCK NOWAIT be a better solution?  That is new in 8.0.

LOCK NOWAIT is only helpful if you can express your problem as not
wanting to wait for a table-level lock.  When you're trying to grab a
row-level lock via SELECT FOR UPDATE, there isn't any provision for
NOWAIT.

The notion of a global lock_timeout setting is bogus IMHO, because
every proposed application of it has failed to consider the locks taken
internally by the system.  But that objection wouldn't apply to a SELECT
FOR UPDATE NOWAIT command where the "no wait" behavior only applied to
the row lock being explicitly grabbed.

I thought I remembered someone working on such a thing just recently.

			regards, tom lane

Wed, 24 Nov 2004 23:01:00 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Postresql 8.0 Beta 3 - SELECT ... FOR UPDATE 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Rod Taylor wrote:
> >> Anyway, it shows a situation where it would be nice to differentiate
> >> between statement_timeout and lock_timeout OR it demonstrates that I
> >> should be using userlocks...
> 
> > Wouldn't a LOCK NOWAIT be a better solution?  That is new in 8.0.
> 
> LOCK NOWAIT is only helpful if you can express your problem as not
> wanting to wait for a table-level lock.  When you're trying to grab a
> row-level lock via SELECT FOR UPDATE, there isn't any provision for
> NOWAIT.
> 
> The notion of a global lock_timeout setting is bogus IMHO, because
> every proposed application of it has failed to consider the locks taken
> internally by the system.  But that objection wouldn't apply to a SELECT
> FOR UPDATE NOWAIT command where the "no wait" behavior only applied to
> the row lock being explicitly grabbed.
> 
> I thought I remembered someone working on such a thing just recently.

Added to TODO:

	* Allow FOR UPDATE queries to do NOWAIT locks

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 1 Dec 2004 23:40:28 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Postresql 8.0 Beta 3 - SELECT ... FOR UPDATE