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 ...
--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 ...
> > 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
! LOCK [ TABLE ] name [, ...] [ IN
lockmode MODE ]
where lockmode is one of:
--- 20,26
! LOCK [ TABLE ] name [, ...] [ IN
lockmode MODE ] [ NOWAIT ]
where lockmode is one of:
***
*** 34,41
LOCK TABLE 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 UNLOCK TABLE command; locks are always
released at transaction end.)
--- 34,43
LOCK TABLE obtains a table-level lock, waiting if
!necessary for any conflicting locks to be released.
!If NOWAIT is given, LOCK TABLE
!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 UNLOCK TABLE command; locks are always
released at transaction end.)
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: src/backend/nodes/equalfuncs.c
===
RCS file: /cvsroot/pgsql-server/src/backend/nodes/equalfuncs.c,v
retrieving revision 1.215
diff -c -r1.215 equalfuncs.c
*** src/backend/nodes/equalfuncs.c 14 Jan 2004 23:01:55 - 1.215
--- src/backend/nodes/equalfuncs.c 10 Mar 2004 01:35:28 -
***
*** 1252,125
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 ...
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 ...
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
! LOCK [ TABLE ] name [, ...] [ IN
lockmode MODE ]
where lockmode is one of:
--- 20,26
! LOCK [ TABLE ] name [, ...] [ IN
lockmode MODE ] [ NOWAIT ]
where lockmode is one of:
***
*** 34,41
LOCK TABLE 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 UNLOCK TABLE command; locks are always
released at transaction end.)
--- 34,43
LOCK TABLE obtains a table-level lock, waiting if
!necessary for any conflicting locks to be released.
!If NOWAIT is given, LOCK TABLE
!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 UNLOCK TABLE command; locks are always
released at transaction end.)
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
*
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 l
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
On Thu, 2004-02-19 at 11:01, Bruce Momjian wrote:
> 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.
>
Is it premature to add "allow vacuum command to use no wait semantics on
locks" to the TODO list?
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
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 ...
> 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 ...
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 ...
> > 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 ...
> 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
*** ./src/backend/access/heap/heapam.c.orig 2003-07-04 09:12:10.0 +0900
--- ./src/backend/access/heap/heapam.c 2003-07-12 13:52:43.0 +0900
***
*** 479,484
--- 479,517
return r;
}
+ Relation
+ conditional_relation_open(Oid relationId, LOCKMODE lockmode, bool no_wait)
+ {
+ Relationr;
+
+ Assert(lockmode >= NoLock && lockmode < MAX_LOCKMODES);
+
+ /*
+* increment access statistics
+*/
+ IncrHeapAccessStat(local_open);
+ IncrHeapAccessStat(global_open);
+
+ /* The relcache does all the real work... */
+ r = RelationIdGetRelation(relationId);
+
+ if (!RelationIsValid(r))
+ elog(ERROR, "Relation %u does not exist", relationId);
+
+ if (lockmode != NoLock)
+ {
+ if (no_wait)
+ {
+ if (!ConditionalLockRelation(r, lockmode))
+ elog(ERROR, "Cannot aquire relation lock");
+ }
+ else
+ LockRelation(r, lockmode);
+ }
+
+ return r;
+ }
+
/*
*relation_openrv - open any relation specified by a RangeVar
*
*** ./src/backend/commands/lockcmds.c.orig 2003-07-04 09:21:30.0 +0900
--- ./src/backend/commands/lockcmds.c 2003-07-12 13:53:30.0 +0900
***
*** 58,64
if (aclresult != ACLCHECK_OK)
aclcheck_error(aclresult, 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)
--- 58,64
if (aclresult != ACLCHECK_OK)
aclcheck_error(aclresult, get_rel_name(reloid));
! rel = conditional_relation_open(reloid, lockstmt->mode,
lockstmt->no_wait);
/* Currently, we only allow plain tables to be locked */
if (rel->rd_rel->relkind != RELKIND_RELATION)
*** ./src/backend/parser/gram.y.orig2003-07-12 12:45:30.0 +0900
--- ./src/backend/parser/gram.y 2003-07-12 13:37:27.0 +0900
***
*** 161,166
--- 161,167
%type opt_lock lock_type cast_context
%typeopt_force opt_or_replace
+ %typeopt_no_wait
%type user_list
***
*** 392,397
--- 393,400
VACUUM VALID VALIDATOR VALUES VARCHAR VARYING
VERBOSE VERSION VIEW VOLATILE
+ WAIT
+
WHEN WHERE WITH WITHOUT WORK WRITE
YEAR_P
***
*** 4050,4061
}
;
! LockStmt: LOCK_P opt_table qualified_name_list opt_lock
{
LockStmt *n = makeNode(LockStmt);
n->relations = $3;
n->mode = $4;
$$ = (Node *)n;
}
;
--- 4053,4065
}
;
! LockStmt: LOCK_P opt_table qualified_name_list opt_lock opt_no_wait
{
LockStmt *n = makeNode(LockStmt);
n->relations = $3;
n->mode = $4;
+ n->no_wait = $5;
$$ = (Node *)n;
}
;
***
*** 4074,4079
--- 4078,4087
| ACCESS EXCLUSIVE { $$ =
AccessExclusiveLock; }
;
+ opt_no_wait: NO WAIT { $$ = TRUE; }
+ | /*EMPTY*/ { $$ =
FALSE; }
+ ;
+
/**
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]
Re: [HACKERS] [PATCHES] NO WAIT ...
Tom Lane wrote: Bruce Momjian <[EMAIL PROTECTED]> writes: The question is whether we should have a GUC variable to control no waiting on locks or add NO WAIT to specific SQL commands. That's only a minor part of the issue. The major problem I have with the patch is that it affects *all* locks, including system-internal lock attempts that the user is probably not even aware of much less able to control. It's like giving someone a poorly-aligned shotgun when what they need is a rifle --- they'll end up putting holes in a lot of other things besides what they intended. I absolutely agree to that. I think that what we actually want is something that is narrowly tailored to affect only row-level locks taken by SELECT FOR UPDATE, and maybe one or two other places that (a) people can make specific use-cases for, and (b) we can be certain are only invoked by user commands and never indirectly from behind-the-scenes system operations. I would gereralize this to user table row level and explicit lock table. There is no need to force a separate SELECT FOR UPDATE in front of every UPDATE or DELETE attempt in order to achieve the desired nolock behaviour. The reason for proposing syntax rather than a GUC variable is the same one of control. If you set a GUC variable then it will be hard to prevent it from breaking operations other than the one you thought you intended. (Example: you think you are only causing your SELECT FOR UPDATE to error out, but what about ones done behind the scenes for foreign key checks?) GUC variables are good for stuff that tends to apply application-wide, which is why I thought regex_flavor wasn't too dangerous, but they're terrible for functions that you want to apply to only certain specific operations. And I can't imagine an app where that wouldn't be true for NO WAIT. This all needs a lot more detail than it has so far. If one tries to UPDATE a row with NOLOCK, and a trigger fired during this would block on the attempt to create a temp table, I think the resulting transaction abort would rather be reported as a bug to us, then accepted as a nice feature. If there is a call for vote, I vote against. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [PATCHES] NO WAIT ...
TODO updated: < * Add GUC variable to prevent waiting on locks > * Add NO WAIT option to various SQL commands --- Barry Lind 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. > > thanks, > --Barry > > Tom Lane wrote: > > Bruce Momjian <[EMAIL PROTECTED]> writes: > > > >>The question is whether we should have a GUC variable to control no > >>waiting on locks or add NO WAIT to specific SQL commands. > > > > > > That's only a minor part of the issue. The major problem I have with > > the patch is that it affects *all* locks, including system-internal > > lock attempts that the user is probably not even aware of much less > > able to control. It's like giving someone a poorly-aligned shotgun > > when what they need is a rifle --- they'll end up putting holes in > > a lot of other things besides what they intended. > > > > I think that what we actually want is something that is narrowly > > tailored to affect only row-level locks taken by SELECT FOR UPDATE, > > and maybe one or two other places that (a) people can make specific > > use-cases for, and (b) we can be certain are only invoked by user > > commands and never indirectly from behind-the-scenes system operations. > > > > The reason for proposing syntax rather than a GUC variable is the same > > one of control. If you set a GUC variable then it will be hard to > > prevent it from breaking operations other than the one you thought you > > intended. (Example: you think you are only causing your SELECT FOR > > UPDATE to error out, but what about ones done behind the scenes for > > foreign key checks?) GUC variables are good for stuff that tends to > > apply application-wide, which is why I thought regex_flavor wasn't too > > dangerous, but they're terrible for functions that you want to apply to > > only certain specific operations. And I can't imagine an app where that > > wouldn't be true for NO WAIT. > > > > regards, tom lane > > > > ---(end of broadcast)--- > > TIP 5: Have you checked our extensive FAQ? > > > >http://www.postgresql.org/docs/faqs/FAQ.html > > > ---(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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
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 ...
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > The question is whether we should have a GUC variable to control no > > waiting on locks or add NO WAIT to specific SQL commands. > > That's only a minor part of the issue. The major problem I have with > the patch is that it affects *all* locks, including system-internal > lock attempts that the user is probably not even aware of much less > able to control. It's like giving someone a poorly-aligned shotgun > when what they need is a rifle --- they'll end up putting holes in > a lot of other things besides what they intended. > > I think that what we actually want is something that is narrowly > tailored to affect only row-level locks taken by SELECT FOR UPDATE, > and maybe one or two other places that (a) people can make specific > use-cases for, and (b) we can be certain are only invoked by user > commands and never indirectly from behind-the-scenes system operations. > > The reason for proposing syntax rather than a GUC variable is the same > one of control. If you set a GUC variable then it will be hard to > prevent it from breaking operations other than the one you thought you > intended. (Example: you think you are only causing your SELECT FOR > UPDATE to error out, but what about ones done behind the scenes for > foreign key checks?) GUC variables are good for stuff that tends to > apply application-wide, which is why I thought regex_flavor wasn't too > dangerous, but they're terrible for functions that you want to apply to > only certain specific operations. And I can't imagine an app where that > wouldn't be true for NO WAIT. Well, they have statement_timeout to prevent a command from taking too long, so that obviously isn't the usage case for NO WAIT. The problem I see for statement_timeout emulating NO WAIT is that on a lightly loaded machine, the usual query time is different from a loaded machine, so guessing a number seems difficult. Saying "Oh, the query is taking longer than X seconds, I must be waiting on a lock" is prone to failure. And if you get a faster machine or have an app that runs on machines of different speeds, it doesn't work either. One idea would be to allow the NOWAIT take a duration like statement_timeout so you could say I only want to wait a maximum of X ms before failing. However, if the usage case for NOWAIT is for an applicaiton to return a string saying "Record Locked", a GUC variable will not work and we have to be fine-grained about it as you suggest. -- 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 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. thanks, --Barry Tom Lane wrote: Bruce Momjian <[EMAIL PROTECTED]> writes: The question is whether we should have a GUC variable to control no waiting on locks or add NO WAIT to specific SQL commands. That's only a minor part of the issue. The major problem I have with the patch is that it affects *all* locks, including system-internal lock attempts that the user is probably not even aware of much less able to control. It's like giving someone a poorly-aligned shotgun when what they need is a rifle --- they'll end up putting holes in a lot of other things besides what they intended. I think that what we actually want is something that is narrowly tailored to affect only row-level locks taken by SELECT FOR UPDATE, and maybe one or two other places that (a) people can make specific use-cases for, and (b) we can be certain are only invoked by user commands and never indirectly from behind-the-scenes system operations. The reason for proposing syntax rather than a GUC variable is the same one of control. If you set a GUC variable then it will be hard to prevent it from breaking operations other than the one you thought you intended. (Example: you think you are only causing your SELECT FOR UPDATE to error out, but what about ones done behind the scenes for foreign key checks?) GUC variables are good for stuff that tends to apply application-wide, which is why I thought regex_flavor wasn't too dangerous, but they're terrible for functions that you want to apply to only certain specific operations. And I can't imagine an app where that wouldn't be true for NO WAIT. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] NO WAIT ...
Bruce Momjian 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. This strikes me as a very broad-brush approach. I suspect that even if we did not cause major breakage we would end up wanting to go back and implement the finer grained per command mechanism. So I'm not in favor - always open to persuasion, of course ;-) cheers andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
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 ...
Bruce Momjian <[EMAIL PROTECTED]> writes: > The question is whether we should have a GUC variable to control no > waiting on locks or add NO WAIT to specific SQL commands. That's only a minor part of the issue. The major problem I have with the patch is that it affects *all* locks, including system-internal lock attempts that the user is probably not even aware of much less able to control. It's like giving someone a poorly-aligned shotgun when what they need is a rifle --- they'll end up putting holes in a lot of other things besides what they intended. I think that what we actually want is something that is narrowly tailored to affect only row-level locks taken by SELECT FOR UPDATE, and maybe one or two other places that (a) people can make specific use-cases for, and (b) we can be certain are only invoked by user commands and never indirectly from behind-the-scenes system operations. The reason for proposing syntax rather than a GUC variable is the same one of control. If you set a GUC variable then it will be hard to prevent it from breaking operations other than the one you thought you intended. (Example: you think you are only causing your SELECT FOR UPDATE to error out, but what about ones done behind the scenes for foreign key checks?) GUC variables are good for stuff that tends to apply application-wide, which is why I thought regex_flavor wasn't too dangerous, but they're terrible for functions that you want to apply to only certain specific operations. And I can't imagine an app where that wouldn't be true for NO WAIT. 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 ...
Tom Lane wrote: > =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <[EMAIL PROTECTED]> writes: > > The problem with adding NO WAIT to specific commands is that is > > inheritly unflexible. I think this is why the community has agreed on > > implementing it based on GUC. > > I recall no such agreement ... when was this exactly? In any case > Bruce's recent complaints about regex_flavor have altered my opinions > about GUC variables a bit. They are bigger safety risks than they look, > especially ones that change semantics and are intended to be modified on > the fly. > > > Do you think it would help to reduce the GUCs flexibility by reducing > > the lock levels a user is allowed to define? > > I will vote against the patch no matter what, but I agree that it would > be less dangerous if it were confined to only apply to a limited set of > lock types. 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. -- 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
