Thread: 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-----
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
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,
Pennsylvania19073
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
>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
-----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-----
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, Pennsylvania19073