> 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.000000000 +0900
--- ./src/backend/access/heap/heapam.c 2003-07-12 13:52:43.000000000 +0900
***************
*** 479,484 ****
--- 479,517 ----
return r;
}
+ Relation
+ conditional_relation_open(Oid relationId, LOCKMODE lockmode, bool no_wait)
+ {
+ Relation r;
+
+ 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.000000000 +0900
--- ./src/backend/commands/lockcmds.c 2003-07-12 13:53:30.000000000 +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.orig 2003-07-12 12:45:30.000000000 +0900
--- ./src/backend/parser/gram.y 2003-07-12 13:37:27.000000000 +0900
***************
*** 161,166 ****
--- 161,167 ----
%type <ival> opt_lock lock_type cast_context
%type <boolean> opt_force opt_or_replace
+ %type <boolean> opt_no_wait
%type <list> 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; }
+ ;
+
/*****************************************************************************
*
*** ./src/backend/parser/keywords.c.orig 2003-07-12 13:39:23.000000000 +0900
--- ./src/backend/parser/keywords.c 2003-07-12 13:39:47.000000000 +0900
***************
*** 318,323 ****
--- 318,324 ----
{"version", VERSION},
{"view", VIEW},
{"volatile", VOLATILE},
+ {"wait", WAIT},
{"when", WHEN},
{"where", WHERE},
{"with", WITH},
*** ./src/include/nodes/parsenodes.h.orig 2003-07-12 12:48:48.000000000 +0900
--- ./src/include/nodes/parsenodes.h 2003-07-12 13:44:04.000000000 +0900
***************
*** 1593,1598 ****
--- 1593,1599 ----
NodeTag type;
List *relations; /* relations to lock */
int mode; /* lock mode */
+ bool no_wait; /* no wait mode */
} LockStmt;
/* ----------------------
*** src/include/access/heapam.h.orig 2003-07-19 14:08:22.000000000 +0900
--- src/include/access/heapam.h 2003-07-19 14:08:48.000000000 +0900
***************
*** 136,141 ****
--- 136,142 ----
/* heapam.c */
extern Relation relation_open(Oid relationId, LOCKMODE lockmode);
+ extern Relation conditional_relation_open(Oid relationId, LOCKMODE lockmode, bool
no_wait);
extern Relation relation_openrv(const RangeVar *relation, LOCKMODE lockmode);
extern Relation relation_openr(const char *sysRelationName, LOCKMODE lockmode);
extern void relation_close(Relation relation, LOCKMODE lockmode);
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
