Re: [HACKERS] [PATCHES] NO WAIT ...
--On Wednesday, February 18, 2004 13:56:14 -0500 Bruce Momjian [EMAIL PROTECTED] wrote: Tom Lane wrote: The question is whether we should have a GUC variable to control no waiting on locks or add NO WAIT to specific SQL commands. Does anyone want to vote _against_ the GUC idea for nowait locking. (We already have two voting for such a variable.) If there is no one except Tom, we can continue. I'm with Tom. Playing with *ALL* locks is just asking for TROUBLE. (I don't know if I count). -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp2so5kCS9ew.pgp Description: PGP signature
Re: [HACKERS] [PATCHES] NO WAIT ...
I agree with Tom here. I have used the Oracle NOWAIT feature in the past and think it is a great feature IMHO. But when you need to use it, you want it to apply very specifically to a single statement. Using a sledge hammer when you need a tweezers isn't the right way to go. Once I have written patches for 7.3 to implement this feature for LOCK statement. For example: test=# LOCK TABLE sales NO WAIT; ERROR: Cannot aquire relation lock If there's enough interest, I will modify and submit it for 7.5. -- Tatsuo Ishii ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] NO WAIT ...
Here is the patch I promised (against current). Regression tests all
passed. One thing I have not checked is the doc(lock.sgml). For some
reason I failed to install docbook V4.2 (I have working docbook V3.1
though), and I couldn't test the correctness of the file. Also, it
would be nice if some one checks my English grammer:-)
--
Tatsuo Ishii
It seems NOWAIT is the winner...
--
Tatsuo Ishii
Oracle uses NOWAIT so we should go for that one.
Regards,
Hans
Tatsuo Ishii wrote:
If NOWAIT is the choice, I could live with it. If there's no
objection, I will go with NOWAIT, not NO WAIT.
--
Tatsuo Ishii
Tatsuo Ishii [EMAIL PROTECTED] writes:
LOCK TABLE table NO WAIT is OK for 7.5? If ok, I will make patches
against current with some docs changes.
Dept of minor gripes: can we do this without turning NO into a
keyword? Even as a nonreserved word, I think that would be annoying.
no is a common abbreviation for number so I think it's likely to
get used as a column name.
If Oracle spells it NOWAIT then I'd be much happier with that...
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/2952/30706 or +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Index: doc/src/sgml/ref/lock.sgml
===
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/lock.sgml,v
retrieving revision 1.40
diff -c -r1.40 lock.sgml
*** doc/src/sgml/ref/lock.sgml 14 Dec 2003 00:05:29 - 1.40
--- doc/src/sgml/ref/lock.sgml 9 Mar 2004 12:42:31 -
***
*** 20,26
refsynopsisdiv
synopsis
! LOCK [ TABLE ] replaceable class=PARAMETERname/replaceable [, ...] [ IN
replaceable class=PARAMETERlockmode/replaceable MODE ]
where replaceable class=PARAMETERlockmode/replaceable is one of:
--- 20,26
refsynopsisdiv
synopsis
! LOCK [ TABLE ] replaceable class=PARAMETERname/replaceable [, ...] [ IN
replaceable class=PARAMETERlockmode/replaceable MODE ] [ NOWAIT ]
where replaceable class=PARAMETERlockmode/replaceable is one of:
***
*** 34,41
para
commandLOCK TABLE/command obtains a table-level lock, waiting if
!necessary for any conflicting locks to be released. Once obtained,
!the lock is held for the remainder of the current transaction.
(There is no commandUNLOCK TABLE/command command; locks are always
released at transaction end.)
/para
--- 34,43
para
commandLOCK TABLE/command obtains a table-level lock, waiting if
!necessary for any conflicting locks to be released.
!If literalNOWAIT/literal is given, commandLOCK TABLE/command
!does not wait for acquiring lock, and throws an error instead.
!Once obtained, the lock is held for the remainder of the current transaction.
(There is no commandUNLOCK TABLE/command command; locks are always
released at transaction end.)
/para
Index: src/backend/access/heap/heapam.c
===
RCS file: /cvsroot/pgsql-server/src/backend/access/heap/heapam.c,v
retrieving revision 1.162
diff -c -r1.162 heapam.c
*** src/backend/access/heap/heapam.c16 Jan 2004 20:51:30 - 1.162
--- src/backend/access/heap/heapam.c9 Mar 2004 12:42:33 -
***
*** 464,469
--- 464,496
return r;
}
+ Relation
+ conditional_relation_open(Oid relationId, LOCKMODE lockmode, bool nowait)
+ {
+ Relationr;
+
+ Assert(lockmode = NoLock lockmode MAX_LOCKMODES);
+
+ /* The relcache does all the real work... */
+ r = RelationIdGetRelation(relationId);
+
+ if (!RelationIsValid(r))
+ elog(ERROR, could not open relation with OID %u, relationId);
+
+ if (lockmode != NoLock)
+ {
+ if (nowait)
+ {
+ if (!ConditionalLockRelation(r, lockmode))
+ elog(ERROR, could not aquire relation lock);
+ }
+ else
+ LockRelation(r, lockmode);
+ }
+
+ return r;
+ }
+
/*
*relation_openrv - open any relation specified by a RangeVar
*
Re: [HACKERS] [PATCHES] NO WAIT ...
Tatsuo Ishii [EMAIL PROTECTED] writes: Here is the patch I promised (against current). This is missing the necessary adjustments in backend/nodes/ (copy and equal funcs). Also the NOWAIT keyword must be added to the list of nonreserved keywords near the bottom of gram.y. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] NO WAIT ...
Tatsuo Ishii [EMAIL PROTECTED] writes: Here is the patch I promised (against current). This is missing the necessary adjustments in backend/nodes/ (copy and equal funcs). Also the NOWAIT keyword must be added to the list of nonreserved keywords near the bottom of gram.y. Thanks for the review. I'll work on this. -- Tatsuo Ishii ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] NO WAIT ...
This is missing the necessary adjustments in backend/nodes/ (copy and
equal funcs). Also the NOWAIT keyword must be added to the list of
nonreserved keywords near the bottom of gram.y.
Thanks for the review. I'll work on this.
Here is the revised patch.
--
Tatsuo Ishii
===
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/lock.sgml,v
retrieving revision 1.40
diff -c -r1.40 lock.sgml
*** doc/src/sgml/ref/lock.sgml 14 Dec 2003 00:05:29 - 1.40
--- doc/src/sgml/ref/lock.sgml 10 Mar 2004 01:35:18 -
***
*** 20,26
refsynopsisdiv
synopsis
! LOCK [ TABLE ] replaceable class=PARAMETERname/replaceable [, ...] [ IN
replaceable class=PARAMETERlockmode/replaceable MODE ]
where replaceable class=PARAMETERlockmode/replaceable is one of:
--- 20,26
refsynopsisdiv
synopsis
! LOCK [ TABLE ] replaceable class=PARAMETERname/replaceable [, ...] [ IN
replaceable class=PARAMETERlockmode/replaceable MODE ] [ NOWAIT ]
where replaceable class=PARAMETERlockmode/replaceable is one of:
***
*** 34,41
para
commandLOCK TABLE/command obtains a table-level lock, waiting if
!necessary for any conflicting locks to be released. Once obtained,
!the lock is held for the remainder of the current transaction.
(There is no commandUNLOCK TABLE/command command; locks are always
released at transaction end.)
/para
--- 34,43
para
commandLOCK TABLE/command obtains a table-level lock, waiting if
!necessary for any conflicting locks to be released.
!If literalNOWAIT/literal is given, commandLOCK TABLE/command
!does not wait for acquiring lock, and throws an error instead.
!Once obtained, the lock is held for the remainder of the current transaction.
(There is no commandUNLOCK TABLE/command command; locks are always
released at transaction end.)
/para
Index: src/backend/access/heap/heapam.c
===
RCS file: /cvsroot/pgsql-server/src/backend/access/heap/heapam.c,v
retrieving revision 1.162
diff -c -r1.162 heapam.c
*** src/backend/access/heap/heapam.c16 Jan 2004 20:51:30 - 1.162
--- src/backend/access/heap/heapam.c10 Mar 2004 01:35:21 -
***
*** 464,469
--- 464,496
return r;
}
+ Relation
+ conditional_relation_open(Oid relationId, LOCKMODE lockmode, bool nowait)
+ {
+ Relationr;
+
+ Assert(lockmode = NoLock lockmode MAX_LOCKMODES);
+
+ /* The relcache does all the real work... */
+ r = RelationIdGetRelation(relationId);
+
+ if (!RelationIsValid(r))
+ elog(ERROR, could not open relation with OID %u, relationId);
+
+ if (lockmode != NoLock)
+ {
+ if (nowait)
+ {
+ if (!ConditionalLockRelation(r, lockmode))
+ elog(ERROR, could not aquire relation lock);
+ }
+ else
+ LockRelation(r, lockmode);
+ }
+
+ return r;
+ }
+
/*
*relation_openrv - open any relation specified by a RangeVar
*
Index: src/backend/commands/lockcmds.c
===
RCS file: /cvsroot/pgsql-server/src/backend/commands/lockcmds.c,v
retrieving revision 1.8
diff -c -r1.8 lockcmds.c
*** src/backend/commands/lockcmds.c 29 Nov 2003 19:51:47 - 1.8
--- src/backend/commands/lockcmds.c 10 Mar 2004 01:35:21 -
***
*** 59,65
aclcheck_error(aclresult, ACL_KIND_CLASS,
get_rel_name(reloid));
! rel = relation_open(reloid, lockstmt-mode);
/* Currently, we only allow plain tables to be locked */
if (rel-rd_rel-relkind != RELKIND_RELATION)
--- 59,65
aclcheck_error(aclresult, ACL_KIND_CLASS,
get_rel_name(reloid));
! rel = conditional_relation_open(reloid, lockstmt-mode,
lockstmt-nowait);
/* Currently, we only allow plain tables to be locked */
if (rel-rd_rel-relkind != RELKIND_RELATION)
Index: src/backend/nodes/copyfuncs.c
===
RCS file: /cvsroot/pgsql-server/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.277
diff -c -r1.277 copyfuncs.c
*** src/backend/nodes/copyfuncs.c 14 Jan 2004 23:01:54 - 1.277
--- src/backend/nodes/copyfuncs.c 10 Mar 2004 01:35:25 -
***
*** 2316,2321
--- 2316,2322
COPY_NODE_FIELD(relations);
COPY_SCALAR_FIELD(mode);
+ COPY_SCALAR_FIELD(nowait);
return newnode;
}
Index:
Re: [HACKERS] [PATCHES] NO WAIT ...
Yes, I think it looks good. --- Tatsuo Ishii wrote: LOCK TABLE table NO WAIT is OK for 7.5? If ok, I will make patches against current with some docs changes. -- Tatsuo Ishii Tatsuo Ishii wrote: I agree with Tom here. I have used the Oracle NOWAIT feature in the past and think it is a great feature IMHO. But when you need to use it, you want it to apply very specifically to a single statement. Using a sledge hammer when you need a tweezers isn't the right way to go. Once I have written patches for 7.3 to implement this feature for LOCK statement. For example: test=# LOCK TABLE sales NO WAIT; ERROR: Cannot aquire relation lock If there's enough interest, I will modify and submit it for 7.5. -- Tatsuo Ishii ---(end of broadcast)--- TIP 8: explain analyze is your friend That would be great. Many people are asking for that. Maybe I have time to implement that for SELECT FOR UPDATE. Here it is(against 7.3.3). -- Tatsuo Ishii ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- 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] [PATCHES] NO WAIT ...
Tatsuo Ishii [EMAIL PROTECTED] writes: LOCK TABLE table NO WAIT is OK for 7.5? If ok, I will make patches against current with some docs changes. Dept of minor gripes: can we do this without turning NO into a keyword? Even as a nonreserved word, I think that would be annoying. no is a common abbreviation for number so I think it's likely to get used as a column name. If Oracle spells it NOWAIT then I'd be much happier with that... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] NO WAIT ...
If NOWAIT is the choice, I could live with it. If there's no objection, I will go with NOWAIT, not NO WAIT. -- Tatsuo Ishii Tatsuo Ishii [EMAIL PROTECTED] writes: LOCK TABLE table NO WAIT is OK for 7.5? If ok, I will make patches against current with some docs changes. Dept of minor gripes: can we do this without turning NO into a keyword? Even as a nonreserved word, I think that would be annoying. no is a common abbreviation for number so I think it's likely to get used as a column name. If Oracle spells it NOWAIT then I'd be much happier with that... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] NO WAIT ...
If NOWAIT is the choice, I could live with it. If there's no objection, I will go with NOWAIT, not NO WAIT. How about WITHOUT WAIT, which is like many of our other commands? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] NO WAIT ...
Christopher Kings-Lynne [EMAIL PROTECTED] writes: If NOWAIT is the choice, I could live with it. If there's no objection, I will go with NOWAIT, not NO WAIT. How about WITHOUT WAIT, which is like many of our other commands? The first question in my mind is exactly how does Oracle spell this? Let's not go inventing compatible-with-no-one syntax if we don't have to. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [PATCHES] NO WAIT ...
It seems NOWAIT is the winner... -- Tatsuo Ishii Oracle uses NOWAIT so we should go for that one. Regards, Hans Tatsuo Ishii wrote: If NOWAIT is the choice, I could live with it. If there's no objection, I will go with NOWAIT, not NO WAIT. -- Tatsuo Ishii Tatsuo Ishii [EMAIL PROTECTED] writes: LOCK TABLE table NO WAIT is OK for 7.5? If ok, I will make patches against current with some docs changes. Dept of minor gripes: can we do this without turning NO into a keyword? Even as a nonreserved word, I think that would be annoying. no is a common abbreviation for number so I think it's likely to get used as a column name. If Oracle spells it NOWAIT then I'd be much happier with that... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [PATCHES] NO WAIT ...
The question is whether we should have a GUC variable to control no waiting on locks or add NO WAIT to specific SQL commands. Does anyone want to vote _against_ the GUC idea for nowait locking. (We already have two voting for such a variable.) I vote against. We got bit by both the regex and the autocommit GUC vars and this is setting up to cause a similar headache with old code on new platforms. I vote for the GUC. Imho it is not comparable to the autocommit case, since it does not change the way your appl needs to react (appl needs to react to deadlock already). I personally think a wait period in seconds would be more useful. Milli second timeouts tend to be misused with way too low values in this case, imho. Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] NO WAIT ...
Zeugswetter Andreas SB SD wrote: The question is whether we should have a GUC variable to control no waiting on locks or add NO WAIT to specific SQL commands. Does anyone want to vote _against_ the GUC idea for nowait locking. (We already have two voting for such a variable.) I vote against. We got bit by both the regex and the autocommit GUC vars and this is setting up to cause a similar headache with old code on new platforms. I vote for the GUC. Imho it is not comparable to the autocommit case, since it does not change the way your appl needs to react (appl needs to react to deadlock already). I personally think a wait period in seconds would be more useful. Milli second timeouts tend to be misused with way too low values in this case, imho. I understand, but GUC lost the vote. I have updated the TODO list to indicate this. Tatsuo posted a patch to add NO WAIT to the LOCK command, so we will see if we can get that into CVS. -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] NO WAIT ...
I vote for the GUC. Imho it is not comparable to the autocommit case, since it does not change the way your appl needs to react (appl needs to react to deadlock already). Wrote one program a while ago that was very time sensitive. By the time deadlock detection had been kicked off, the data was already invalid and due to be replaced -- thus, it's impossible to have deadlocks with the chosen design for that application. The point is, PostgreSQL is fairly versatile and is a component of many different environments. Method X might be great for what you're doing, but it doesn't apply across the board. The regex GUC doesn't impact a majority of applications either, but it proved catastrophic to some. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [PATCHES] NO WAIT ...
I personally think a wait period in seconds would be more useful. Milli second timeouts tend to be misused with way too low values in this case, imho. I understand, but GUC lost the vote. I have updated the TODO list to indicate this. Tatsuo posted a patch to add NO WAIT to the LOCK command, so we will see if we can get that into CVS. Ok, I can see the advantages of that approach too. Too bad there is no standard for this. And it is probably really true that statement_timeout solves the problem of very long (indefinite :-) waits for locks. Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] NO WAIT ...
The question is whether we should have a GUC variable to control no waiting on locks or add NO WAIT to specific SQL commands. Does anyone want to vote _against_ the GUC idea for nowait locking. (We already have two voting for such a variable.) I vote against. We got bit by both the regex and the autocommit GUC vars and this is setting up to cause a similar headache with old code on new platforms. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] NO WAIT ...
Larry Rosenman wrote: -- Start of PGP signed section. --On Wednesday, February 18, 2004 13:56:14 -0500 Bruce Momjian [EMAIL PROTECTED] wrote: Tom Lane wrote: The question is whether we should have a GUC variable to control no waiting on locks or add NO WAIT to specific SQL commands. Does anyone want to vote _against_ the GUC idea for nowait locking. (We already have two voting for such a variable.) If there is no one except Tom, we can continue. I'm with Tom. Playing with *ALL* locks is just asking for TROUBLE. OK, seems folks want the NO WAIT to apply only to the requested lock, and want syntax to show that. -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] NO WAIT ...
Tatsuo Ishii wrote: I agree with Tom here. I have used the Oracle NOWAIT feature in the past and think it is a great feature IMHO. But when you need to use it, you want it to apply very specifically to a single statement. Using a sledge hammer when you need a tweezers isn't the right way to go. Once I have written patches for 7.3 to implement this feature for LOCK statement. For example: test=# LOCK TABLE sales NO WAIT; ERROR: Cannot aquire relation lock If there's enough interest, I will modify and submit it for 7.5. -- Tatsuo Ishii ---(end of broadcast)--- TIP 8: explain analyze is your friend That would be great. Many people are asking for that. Maybe I have time to implement that for SELECT FOR UPDATE. Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
