Re: [HACKERS] 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] 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.orgdevrim.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- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 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
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] 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
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [HACKERS] 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
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[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.orgdevrim.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 [EMAIL PROTECTED]
Re: [HACKERS] SELECT FOR UPDATE NOWAIT and PostgreSQL 8.0
DB2 8.2 now supports NOWAIT also... Best Regards, Simon Riggs
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Devrim GUNDUZ
Sent: 08 September 2004 23:57
To: [EMAIL PROTECTED]
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 [EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SELECT FOR UPDATE NOWAIT
Paulo Scardine wrote: LockAcquire has a dontWait parameter, which do just what I want. The executor level calls heap_open(relid, RowShareLock) when doing FOR UPDATEs. Should we define something like RowShareLockNoWait, so heap_open() or other lower level functions can call LockAcquire() with dontWait set? By the way, is this kind of question on-topic for pgsql-hackers? I think there are two issues with implementing nowait locking: If we have special syntax for FOR UPDATE, we will need it for other commands that need no wait behavior, and after a while they all carry around that cruft --- SET seems easier and more useful. Second, I don't think we want to carry around a NOWAIT boolean in all our structures --- a SET would control it easier. The SET can be checked right in the lock code, and I think having it control only exclusive locks would do almost everything we want. Sounds reasonable to me. You'll have my vote for the SET way. Regards, Christoph ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] SELECT FOR UPDATE NOWAIT
Rod Taylor wrote: -- Start of PGP signed section. On Fri, 2003-07-18 at 19:46, Paulo Scardine wrote: My boss is asking for something like Oracle's SELECT FOR UPDATE NOWAIT. Is there any such feature? If no, should I look forward into implementing this? Any advice? Lookup STATEMENT_TIMEOUT and set it to a very short time. Some people have said they want to distinguish between a slow query (busy system) and waiting on a lock. I can particulary see wanting to do a NOWAIT only on exclusive locks --- not sure how many really want that, though. I think I'm a quite attentive to the SQL and HACKERS list, and I see requests for a NOWAIT option at least once a month, and it's growing. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] SELECT FOR UPDATE NOWAIT
(Excuse my english, my native language is portuguese) I think I'm a quite attentive to the SQL and HACKERS list, and I see requests for a NOWAIT option at least once a month, and it's growing. Regards, Christoph Yes. I have done a little google about pgsql select for update nowait and find lot of people looking for this feature. If there is a lock and NOWAIT is specified, then raise error instead of waiting for the lock to release. We have some long distance connections that get very busy sometimes and is hard to distinguish between network problems and locked rows. Do you see it as something hard to implement? Any advice? Is it just to add an opt_nowait to the FOR UPDATE clause in the parser and checking for this option when trying to get the lock later? TIA, -- Paulo Scardine - Original Message - From: Christoph Haller [EMAIL PROTECTED] Sent: Wednesday, July 23, 2003 4:35 AM Subject: Re: [HACKERS] SELECT FOR UPDATE NOWAIT ... Lookup STATEMENT_TIMEOUT and set it to a very short time. Some people have said they want to distinguish between a slow query (busy system) and waiting on a lock. I can particulary see wanting to do a NOWAIT only on exclusive locks --- not sure how many really want that, though. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.490 / Virus Database: 289 - Release Date: 16/6/2003 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] SELECT FOR UPDATE NOWAIT
Paulo Scardine wrote: (Excuse my english, my native language is portuguese) I think I'm a quite attentive to the SQL and HACKERS list, and I see requests for a NOWAIT option at least once a month, and it's growing. Regards, Christoph Yes. I have done a little google about pgsql select for update nowait and find lot of people looking for this feature. If there is a lock and NOWAIT is specified, then raise error instead of waiting for the lock to release. We have some long distance connections that get very busy sometimes and is hard to distinguish between network problems and locked rows. Do you see it as something hard to implement? Any advice? Is it just to add an opt_nowait to the FOR UPDATE clause in the parser and checking for this option when trying to get the lock later? My guess is that we will implement it was a SET variable so it can control FOR UPDATE/LOCK/UPDATE/DELETE. Added to TODO: Add GUC variable to prevent waiting on locks -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] SELECT FOR UPDATE NOWAIT
My guess is that we will implement it was a SET variable so it can control FOR UPDATE/LOCK/UPDATE/DELETE. Added to TODO: Add GUC variable to prevent waiting on locks Interesting. I have a lot of potentially dumb questions: - Is this easier to implement as a user variable than in the parser or is some kind of police (SQL compliance, etc.)? - These locks are grant by LockAcquire() or by higher level functions? - Where is the best place to put this? TIA, -- Paulo Scardine --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.490 / Virus Database: 289 - Release Date: 16/6/2003 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SELECT FOR UPDATE NOWAIT
Paulo Scardine [EMAIL PROTECTED] writes: - Where is the best place to put this? I think it would be a really *bad* idea to put it in LockAcquire; that risks breaking things that you don't want broken. Whether it's special syntax or a GUC variable, the restriction should only apply to SELECT FOR UPDATE row locks, perhaps user-commanded LOCK TABLE operations, and maybe one or two other places that are known to be used only for user-written operations and not for system-initiated ones. Those places would need to check whether to do a conditional or unconditional lock. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] SELECT FOR UPDATE NOWAIT
Tom Lane wrote: Paulo Scardine [EMAIL PROTECTED] writes: - Where is the best place to put this? I think it would be a really *bad* idea to put it in LockAcquire; that risks breaking things that you don't want broken. Whether it's special syntax or a GUC variable, the restriction should only apply to SELECT FOR UPDATE row locks, perhaps user-commanded LOCK TABLE operations, and maybe one or two other places that are known to be used only for user-written operations and not for system-initiated ones. Those places would need to check whether to do a conditional or unconditional lock. My original idea was to have it apply only for exclusive locks. It seemed those were mostly the locks didn't want to wait for. Shared locking pg_class is something that you would want to wait for. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] SELECT FOR UPDATE NOWAIT
LockAcquire has a dontWait parameter, which do just what I want. The executor level calls heap_open(relid, RowShareLock) when doing FOR UPDATEs. Should we define something like RowShareLockNoWait, so heap_open() or other lower level functions can call LockAcquire() with dontWait set? By the way, is this kind of question on-topic for pgsql-hackers? TIA, -- Paulo Scardine - Original Message - From: Bruce Momjian [EMAIL PROTECTED] To: Tom Lane [EMAIL PROTECTED] Cc: Paulo Scardine [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, July 23, 2003 4:30 PM Subject: Re: [HACKERS] SELECT FOR UPDATE NOWAIT Tom Lane wrote: Paulo Scardine [EMAIL PROTECTED] writes: - Where is the best place to put this? I think it would be a really *bad* idea to put it in LockAcquire; that risks breaking things that you don't want broken. Whether it's special syntax or a GUC variable, the restriction should only apply to SELECT FOR UPDATE row locks, perhaps user-commanded LOCK TABLE operations, and maybe one or two other places that are known to be used only for user-written operations and not for system-initiated ones. Those places would need to check whether to do a conditional or unconditional lock. My original idea was to have it apply only for exclusive locks. It seemed those were mostly the locks didn't want to wait for. Shared locking pg_class is something that you would want to wait for. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.490 / Virus Database: 289 - Release Date: 16/6/2003 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SELECT FOR UPDATE NOWAIT
Paulo Scardine wrote: LockAcquire has a dontWait parameter, which do just what I want. The executor level calls heap_open(relid, RowShareLock) when doing FOR UPDATEs. Should we define something like RowShareLockNoWait, so heap_open() or other lower level functions can call LockAcquire() with dontWait set? By the way, is this kind of question on-topic for pgsql-hackers? I think there are two issues with implementing nowait locking: If we have special syntax for FOR UPDATE, we will need it for other commands that need no wait behavior, and after a while they all carry around that cruft --- SET seems easier and more useful. Second, I don't think we want to carry around a NOWAIT boolean in all our structures --- a SET would control it easier. The SET can be checked right in the lock code, and I think having it control only exclusive locks would do almost everything we want. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] SELECT FOR UPDATE NOWAIT
Rod Taylor wrote: -- Start of PGP signed section. On Fri, 2003-07-18 at 19:46, Paulo Scardine wrote: My boss is asking for something like Oracle's SELECT FOR UPDATE NOWAIT. Is there any such feature? If no, should I look forward into implementing this? Any advice? Lookup STATEMENT_TIMEOUT and set it to a very short time. Some people have said they want to distinguish between a slow query (busy system) and waiting on a lock. I can particulary see wanting to do a NOWAIT only on exclusive locks --- not sure how many really want that, though. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] SELECT FOR UPDATE NOWAIT
My boss is asking for something like Oracle's SELECT FOR UPDATE NOWAIT. Is there any such feature? If no, should I look forward into implementing this? Any advice? Thank you, -- Paulo Scardine --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.490 / Virus Database: 289 - Release Date: 16/6/2003 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] SELECT FOR UPDATE NOWAIT
On Fri, 2003-07-18 at 19:46, Paulo Scardine wrote: My boss is asking for something like Oracle's SELECT FOR UPDATE NOWAIT. Is there any such feature? If no, should I look forward into implementing this? Any advice? Lookup STATEMENT_TIMEOUT and set it to a very short time. signature.asc Description: This is a digitally signed message part
