-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Hi,

AFAIR there was a thread about "SELECT FOR UPDATE NOWAIT" availability in 
{7.5,8.0}, 7-8 months ago.

Now we have LOCK TABLE ... NOWAIT; but I wonder whether we'll have the 
SELECT ... NOWAIT one.  Today I got a request for this; and it was 
reported that this feature will be used in a huge project.

If there is an unapplied patch that I've missed (even though I didn't see 
one in  http://momjian.postgresql.org/cgi-bin/pgpatches2), I'd like to 
know it -- taking all the risks, surely.

Regards,
- --
Devrim GUNDUZ 
devrim~gunduz.org				devrim.gunduz~linux.org.tr
 			http://www.tdmsoft.com
 			http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFBP44ttl86P3SPfQ4RAqC2AJoCeQrLeEdD6dE1S4mQO+gGRzsZxQCg2OM4
dAWpHfXywbDS+dADccfGqCY=
=1+Yy
-----END PGP SIGNATURE-----

Thu, 9 Sep 2004 01:56:42 +0300 (EEST)
Devrim GUNDUZ <devrim@gunduz.org>
SELECT FOR UPDATE NOWAIT and PostgreSQL 8.0

DB2 8.2 now supports NOWAIT also...		Best Regards, Simon Riggs

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Devrim GUNDUZ
> Sent: 08 September 2004 23:57
> To: pgsql-hackers@postgresql.org
> Subject: [HACKERS] SELECT FOR UPDATE NOWAIT and PostgreSQL 8.0
>
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> Hi,
>
> AFAIR there was a thread about "SELECT FOR UPDATE NOWAIT" availability in
> {7.5,8.0}, 7-8 months ago.
>
> Now we have LOCK TABLE ... NOWAIT; but I wonder whether we'll have the
> SELECT ... NOWAIT one.  Today I got a request for this; and it was
> reported that this feature will be used in a huge project.
>
> If there is an unapplied patch that I've missed (even though I didn't see
> one in  http://momjian.postgresql.org/cgi-bin/pgpatches2), I'd like to
> know it -- taking all the risks, surely.
>
> Regards,
> - --
> Devrim GUNDUZ
> devrim~gunduz.org				devrim.gunduz~linux.org.tr
>  			http://www.tdmsoft.com
>  			http://www.gunduz.org
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.1 (GNU/Linux)
>
> iD8DBQFBP44ttl86P3SPfQ4RAqC2AJoCeQrLeEdD6dE1S4mQO+gGRzsZxQCg2OM4
> dAWpHfXywbDS+dADccfGqCY=
> =1+Yy
> -----END PGP SIGNATURE-----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org


Thu, 9 Sep 2004 00:35:36 +0100
"Simon Riggs" <simon@2ndquadrant.com>
Re: SELECT FOR UPDATE NOWAIT and PostgreSQL 8.0
Devrim GUNDUZ wrote:
[ PGP not available, raw data follows ]
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> 
> Hi,
> 
> AFAIR there was a thread about "SELECT FOR UPDATE NOWAIT" availability in 
> {7.5,8.0}, 7-8 months ago.
> 
> Now we have LOCK TABLE ... NOWAIT; but I wonder whether we'll have the 
> SELECT ... NOWAIT one.  Today I got a request for this; and it was 
> reported that this feature will be used in a huge project.
> 
> If there is an unapplied patch that I've missed (even though I didn't see 
> one in  http://momjian.postgresql.org/cgi-bin/pgpatches2), I'd like to 
> know it -- taking all the risks, surely.

I don't know of any patch done.  The solution suggested was to use
statement_timeout before the SELECT FOR UPDATE.  I am not 100% excited
about that because there is no way to know if the query is slow because
of a lock or just system slowness, but the logic is that you really
don't care why you have failed to do a lock or not, just that the query
is taking a long time.   It does solve the problem and allow us to not
add NOWAIT to UPDATE and DELETE too.  The other problem is that queries
do a lot of locking (think system tables) so there is no way to know
which locks we shouldn't wait for.  At last LOCK specifies the object,
but of course it doesn't do row-level control.

Care to suggest an FAQ.

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

Fri, 10 Sep 2004 17:58:23 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: SELECT FOR UPDATE NOWAIT and PostgreSQL 8.0
On Friday 10 September 2004 17:58, Bruce Momjian wrote:
> Devrim GUNDUZ wrote:
> > Hi,
> >
> > AFAIR there was a thread about "SELECT FOR UPDATE NOWAIT" availability in
> > {7.5,8.0}, 7-8 months ago.
> >
> > Now we have LOCK TABLE ... NOWAIT; but I wonder whether we'll have the
> > SELECT ... NOWAIT one.  Today I got a request for this; and it was
> > reported that this feature will be used in a huge project.
> >
> > If there is an unapplied patch that I've missed (even though I didn't see
> > one in  http://momjian.postgresql.org/cgi-bin/pgpatches2), I'd like to
> > know it -- taking all the risks, surely.
>
> I don't know of any patch done.  The solution suggested was to use
> statement_timeout before the SELECT FOR UPDATE.  I am not 100% excited
> about that because there is no way to know if the query is slow because
> of a lock or just system slowness, but the logic is that you really
> don't care why you have failed to do a lock or not, just that the query
> is taking a long time.   

Hmm... this seems the exact opposite of how I would tend to think the feature 
would be used... ie. you don't really care how long the query takes, just 
that you can't get the lock. 

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

Sun, 12 Sep 2004 08:49:16 -0400
Robert Treat <xzilla@users.sourceforge.net>
Re: SELECT FOR UPDATE NOWAIT and PostgreSQL 8.0
>Robert Treat...
> On Friday 10 September 2004 17:58, Bruce Momjian wrote:
> > Devrim GUNDUZ wrote:
> > > Hi,
> > >
> > > AFAIR there was a thread about "SELECT FOR UPDATE NOWAIT"
> availability in
> > > {7.5,8.0}, 7-8 months ago.
> > >
> > > Now we have LOCK TABLE ... NOWAIT; but I wonder whether we'll have the
> > > SELECT ... NOWAIT one.  Today I got a request for this; and it was
> > > reported that this feature will be used in a huge project.
> > >

Well, it shouldn't be too much of a patch - just cloning the code?

Perhaps they can start in development without it and we'll patch it in
later.

> > > If there is an unapplied patch that I've missed (even though
> I didn't see
> > > one in  http://momjian.postgresql.org/cgi-bin/pgpatches2), I'd like to
> > > know it -- taking all the risks, surely.
> >
> > I don't know of any patch done.  The solution suggested was to use
> > statement_timeout before the SELECT FOR UPDATE.  I am not 100% excited
> > about that because there is no way to know if the query is slow because
> > of a lock or just system slowness, but the logic is that you really
> > don't care why you have failed to do a lock or not, just that the query
> > is taking a long time.
>
> Hmm... this seems the exact opposite of how I would tend to think
> the feature
> would be used... ie. you don't really care how long the query takes, just
> that you can't get the lock.
>

Agreed - and this is important! I thought we'd done NOWAIT on the SELECT...

Oh well, 8.1 will be better still.

Best Regards, Simon Riggs


Mon, 13 Sep 2004 23:07:37 +0100
"Simon Riggs" <simon@2ndquadrant.com>
Re: SELECT FOR UPDATE NOWAIT and PostgreSQL 8.0
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Hi,

On Mon, 13 Sep 2004, Simon Riggs wrote:

> Now we have LOCK TABLE ... NOWAIT; but I wonder whether we'll have the
> SELECT ... NOWAIT one.  Today I got a request for this; and it was
> reported that this feature will be used in a huge project.
>
> Well, it shouldn't be too much of a patch - just cloning the code?
>
> Perhaps they can start in development without it and we'll patch it in
> later.

I learned that the code is ready. They'll change the code now.

>> Hmm... this seems the exact opposite of how I would tend to think
>> the feature
>> would be used... ie. you don't really care how long the query takes, just
>> that you can't get the lock.
>
> Agreed - and this is important! I thought we'd done NOWAIT on the SELECT...
>
> Oh well, 8.1 will be better still.

Bruce: Any TODO here? ;)

Regards,

- --
Devrim GUNDUZ 
devrim~gunduz.org				devrim.gunduz~linux.org.tr
 			http://www.tdmsoft.com
 			http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFBRythtl86P3SPfQ4RAg5+AJ9jdgkolYwBgeLhTBRMt0W0TGd8AwCeJQuM
95igCBca4aisP82g4374nxs=
=Yr9x
-----END PGP SIGNATURE-----

Tue, 14 Sep 2004 20:33:19 +0300 (EEST)
Devrim GUNDUZ <devrim@gunduz.org>
Re: SELECT FOR UPDATE NOWAIT and PostgreSQL 8.0
Devrim GUNDUZ wrote:
> > Now we have LOCK TABLE ... NOWAIT; but I wonder whether we'll have the
> > SELECT ... NOWAIT one.  Today I got a request for this; and it was
> > reported that this feature will be used in a huge project.
> >
> > Well, it shouldn't be too much of a patch - just cloning the code?
> >
> > Perhaps they can start in development without it and we'll patch it in
> > later.
> 
> I learned that the code is ready. They'll change the code now.
> 
> >> Hmm... this seems the exact opposite of how I would tend to think
> >> the feature
> >> would be used... ie. you don't really care how long the query takes, just
> >> that you can't get the lock.
> >
> > Agreed - and this is important! I thought we'd done NOWAIT on the SELECT...
> >
> > Oh well, 8.1 will be better still.
> 
> Bruce: Any TODO here? ;)

OK, but the NOWAIT has to be done for SELECT FOR UPDATE, UPDATE, and
DELETE.  Anyone want to suggest an API for that?  Anddo you realize
there are lots of locks for those commands, like locks on pg_class and
stuff.  Would it be only for exclusive locks?  As you can see there are
some unanswered questions.

-- 
  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, 15 Sep 2004 09:24:54 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: SELECT FOR UPDATE NOWAIT and PostgreSQL 8.0