diff options
Diffstat (limited to 'src')
36 files changed, 889 insertions, 111 deletions
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index ae15c0b9556..808b942c5e8 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -4090,7 +4090,7 @@ get_mxact_status_for_lock(LockTupleMode mode, bool is_update) * cid: current command ID (used for visibility test, and stored into * tuple's cmax if lock is successful) * mode: indicates if shared or exclusive tuple lock is desired - * nowait: if true, ereport rather than blocking if lock not available + * wait_policy: what to do if tuple lock is not available * follow_updates: if true, follow the update chain to also lock descendant * tuples. * @@ -4103,6 +4103,7 @@ get_mxact_status_for_lock(LockTupleMode mode, bool is_update) * HeapTupleMayBeUpdated: lock was successfully acquired * HeapTupleSelfUpdated: lock failed because tuple updated by self * HeapTupleUpdated: lock failed because tuple updated by other xact + * HeapTupleWouldBlock: lock couldn't be acquired and wait_policy is skip * * In the failure cases, the routine fills *hufd with the tuple's t_ctid, * t_xmax (resolving a possible MultiXact, if necessary), and t_cmax @@ -4114,7 +4115,7 @@ get_mxact_status_for_lock(LockTupleMode mode, bool is_update) */ HTSU_Result heap_lock_tuple(Relation relation, HeapTuple tuple, - CommandId cid, LockTupleMode mode, bool nowait, + CommandId cid, LockTupleMode mode, LockWaitPolicy wait_policy, bool follow_updates, Buffer *buffer, HeapUpdateFailureData *hufd) { @@ -4220,16 +4221,28 @@ l3: */ if (!have_tuple_lock) { - if (nowait) + switch (wait_policy) { - if (!ConditionalLockTupleTuplock(relation, tid, mode)) - ereport(ERROR, - (errcode(ERRCODE_LOCK_NOT_AVAILABLE), - errmsg("could not obtain lock on row in relation \"%s\"", - RelationGetRelationName(relation)))); + case LockWaitBlock: + LockTupleTuplock(relation, tid, mode); + break; + case LockWaitSkip: + if (!ConditionalLockTupleTuplock(relation, tid, mode)) + { + result = HeapTupleWouldBlock; + /* recovery code expects to have buffer lock held */ + LockBuffer(*buffer, BUFFER_LOCK_EXCLUSIVE); + goto failed; + } + break; + case LockWaitError: + if (!ConditionalLockTupleTuplock(relation, tid, mode)) + ereport(ERROR, + (errcode(ERRCODE_LOCK_NOT_AVAILABLE), + errmsg("could not obtain lock on row in relation \"%s\"", + RelationGetRelationName(relation)))); + break; } - else - LockTupleTuplock(relation, tid, mode); have_tuple_lock = true; } @@ -4432,21 +4445,35 @@ l3: if (status >= MultiXactStatusNoKeyUpdate) elog(ERROR, "invalid lock mode in heap_lock_tuple"); - /* wait for multixact to end */ - if (nowait) + /* wait for multixact to end, or die trying */ + switch (wait_policy) { - if (!ConditionalMultiXactIdWait((MultiXactId) xwait, - status, infomask, relation, - NULL)) - ereport(ERROR, - (errcode(ERRCODE_LOCK_NOT_AVAILABLE), - errmsg("could not obtain lock on row in relation \"%s\"", - RelationGetRelationName(relation)))); + case LockWaitBlock: + MultiXactIdWait((MultiXactId) xwait, status, infomask, + relation, &tuple->t_data->t_ctid, XLTW_Lock, NULL); + break; + case LockWaitSkip: + if (!ConditionalMultiXactIdWait((MultiXactId) xwait, + status, infomask, relation, + NULL)) + { + result = HeapTupleWouldBlock; + /* recovery code expects to have buffer lock held */ + LockBuffer(*buffer, BUFFER_LOCK_EXCLUSIVE); + goto failed; + } + break; + case LockWaitError: + if (!ConditionalMultiXactIdWait((MultiXactId) xwait, + status, infomask, relation, + NULL)) + ereport(ERROR, + (errcode(ERRCODE_LOCK_NOT_AVAILABLE), + errmsg("could not obtain lock on row in relation \"%s\"", + RelationGetRelationName(relation)))); + + break; } - else - MultiXactIdWait((MultiXactId) xwait, status, infomask, - relation, &tuple->t_data->t_ctid, - XLTW_Lock, NULL); /* if there are updates, follow the update chain */ if (follow_updates && @@ -4491,18 +4518,30 @@ l3: } else { - /* wait for regular transaction to end */ - if (nowait) + /* wait for regular transaction to end, or die trying */ + switch (wait_policy) { - if (!ConditionalXactLockTableWait(xwait)) - ereport(ERROR, - (errcode(ERRCODE_LOCK_NOT_AVAILABLE), - errmsg("could not obtain lock on row in relation \"%s\"", - RelationGetRelationName(relation)))); + case LockWaitBlock: + XactLockTableWait(xwait, relation, &tuple->t_data->t_ctid, + XLTW_Lock); + break; + case LockWaitSkip: + if (!ConditionalXactLockTableWait(xwait)) + { + result = HeapTupleWouldBlock; + /* recovery code expects to have buffer lock held */ + LockBuffer(*buffer, BUFFER_LOCK_EXCLUSIVE); + goto failed; + } + break; + case LockWaitError: + if (!ConditionalXactLockTableWait(xwait)) + ereport(ERROR, + (errcode(ERRCODE_LOCK_NOT_AVAILABLE), + errmsg("could not obtain lock on row in relation \"%s\"", + RelationGetRelationName(relation)))); + break; } - else - XactLockTableWait(xwait, relation, &tuple->t_data->t_ctid, - XLTW_Lock); /* if there are updates, follow the update chain */ if (follow_updates && @@ -4564,7 +4603,8 @@ l3: failed: if (result != HeapTupleMayBeUpdated) { - Assert(result == HeapTupleSelfUpdated || result == HeapTupleUpdated); + Assert(result == HeapTupleSelfUpdated || result == HeapTupleUpdated || + result == HeapTupleWouldBlock); Assert(!(tuple->t_data->t_infomask & HEAP_XMAX_INVALID)); hufd->ctid = tuple->t_data->t_ctid; hufd->xmax = HeapTupleHeaderGetUpdateXid(tuple->t_data); diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c index 9bf0098b6cb..f4c0ffa0211 100644 --- a/src/backend/commands/trigger.c +++ b/src/backend/commands/trigger.c @@ -2706,7 +2706,7 @@ ltrmark:; tuple.t_self = *tid; test = heap_lock_tuple(relation, &tuple, estate->es_output_cid, - lockmode, false /* wait */ , + lockmode, LockWaitBlock, false, &buffer, &hufd); switch (test) { diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index a546292da6e..a753b207008 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -836,7 +836,7 @@ InitPlan(QueryDesc *queryDesc, int eflags) erm->prti = rc->prti; erm->rowmarkId = rc->rowmarkId; erm->markType = rc->markType; - erm->noWait = rc->noWait; + erm->waitPolicy = rc->waitPolicy; ItemPointerSetInvalid(&(erm->curCtid)); estate->es_rowMarks = lappend(estate->es_rowMarks, erm); } @@ -1871,7 +1871,7 @@ EvalPlanQual(EState *estate, EPQState *epqstate, /* * Get and lock the updated version of the row; if fail, return NULL. */ - copyTuple = EvalPlanQualFetch(estate, relation, lockmode, false /* wait */, + copyTuple = EvalPlanQualFetch(estate, relation, lockmode, LockWaitBlock, tid, priorXmax); if (copyTuple == NULL) @@ -1930,12 +1930,15 @@ EvalPlanQual(EState *estate, EPQState *epqstate, * estate - executor state data * relation - table containing tuple * lockmode - requested tuple lock mode - * noWait - wait mode to pass to heap_lock_tuple + * wait_policy - requested lock wait policy * *tid - t_ctid from the outdated tuple (ie, next updated version) * priorXmax - t_xmax from the outdated tuple * * Returns a palloc'd copy of the newest tuple version, or NULL if we find * that there is no newest version (ie, the row was deleted not updated). + * We also return NULL if the tuple is locked and the wait policy is to skip + * such tuples. + * * If successful, we have locked the newest tuple version, so caller does not * need to worry about it changing anymore. * @@ -1943,7 +1946,8 @@ EvalPlanQual(EState *estate, EPQState *epqstate, * but we use "int" to avoid having to include heapam.h in executor.h. */ HeapTuple -EvalPlanQualFetch(EState *estate, Relation relation, int lockmode, bool noWait, +EvalPlanQualFetch(EState *estate, Relation relation, int lockmode, + LockWaitPolicy wait_policy, ItemPointer tid, TransactionId priorXmax) { HeapTuple copyTuple = NULL; @@ -1992,18 +1996,25 @@ EvalPlanQualFetch(EState *estate, Relation relation, int lockmode, bool noWait, if (TransactionIdIsValid(SnapshotDirty.xmax)) { ReleaseBuffer(buffer); - if (noWait) + switch (wait_policy) { - if (!ConditionalXactLockTableWait(SnapshotDirty.xmax)) - ereport(ERROR, - (errcode(ERRCODE_LOCK_NOT_AVAILABLE), - errmsg("could not obtain lock on row in relation \"%s\"", - RelationGetRelationName(relation)))); + case LockWaitBlock: + XactLockTableWait(SnapshotDirty.xmax, + relation, &tuple.t_data->t_ctid, + XLTW_FetchUpdated); + break; + case LockWaitSkip: + if (!ConditionalXactLockTableWait(SnapshotDirty.xmax)) + return NULL; /* skip instead of waiting */ + break; + case LockWaitError: + if (!ConditionalXactLockTableWait(SnapshotDirty.xmax)) + ereport(ERROR, + (errcode(ERRCODE_LOCK_NOT_AVAILABLE), + errmsg("could not obtain lock on row in relation \"%s\"", + RelationGetRelationName(relation)))); + break; } - else - XactLockTableWait(SnapshotDirty.xmax, - relation, &tuple.t_data->t_ctid, - XLTW_FetchUpdated); continue; /* loop back to repeat heap_fetch */ } @@ -2030,7 +2041,7 @@ EvalPlanQualFetch(EState *estate, Relation relation, int lockmode, bool noWait, */ test = heap_lock_tuple(relation, &tuple, estate->es_output_cid, - lockmode, noWait, + lockmode, wait_policy, false, &buffer, &hufd); /* We now have two pins on the buffer, get rid of one */ ReleaseBuffer(buffer); @@ -2076,6 +2087,10 @@ EvalPlanQualFetch(EState *estate, Relation relation, int lockmode, bool noWait, /* tuple was deleted, so give up */ return NULL; + case HeapTupleWouldBlock: + ReleaseBuffer(buffer); + return NULL; + default: ReleaseBuffer(buffer); elog(ERROR, "unrecognized heap_lock_tuple status: %u", diff --git a/src/backend/executor/nodeLockRows.c b/src/backend/executor/nodeLockRows.c index 814b61efcba..990240bf0a8 100644 --- a/src/backend/executor/nodeLockRows.c +++ b/src/backend/executor/nodeLockRows.c @@ -133,11 +133,15 @@ lnext: test = heap_lock_tuple(erm->relation, &tuple, estate->es_output_cid, - lockmode, erm->noWait, true, + lockmode, erm->waitPolicy, true, &buffer, &hufd); ReleaseBuffer(buffer); switch (test) { + case HeapTupleWouldBlock: + /* couldn't lock tuple in SKIP LOCKED mode */ + goto lnext; + case HeapTupleSelfUpdated: /* @@ -170,12 +174,15 @@ lnext: } /* updated, so fetch and lock the updated version */ - copyTuple = EvalPlanQualFetch(estate, erm->relation, lockmode, erm->noWait, - &hufd.ctid, hufd.xmax); + copyTuple = EvalPlanQualFetch(estate, erm->relation, lockmode, + erm->waitPolicy, &hufd.ctid, hufd.xmax); if (copyTuple == NULL) { - /* Tuple was deleted, so don't return it */ + /* + * Tuple was deleted; or it's locked and we're under SKIP + * LOCKED policy, so don't return it + */ goto lnext; } /* remember the actually locked tuple's TID */ diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 225756c08cc..21b070acdaf 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -959,7 +959,7 @@ _copyPlanRowMark(const PlanRowMark *from) COPY_SCALAR_FIELD(prti); COPY_SCALAR_FIELD(rowmarkId); COPY_SCALAR_FIELD(markType); - COPY_SCALAR_FIELD(noWait); + COPY_SCALAR_FIELD(waitPolicy); COPY_SCALAR_FIELD(isParent); return newnode; @@ -2071,7 +2071,7 @@ _copyRowMarkClause(const RowMarkClause *from) COPY_SCALAR_FIELD(rti); COPY_SCALAR_FIELD(strength); - COPY_SCALAR_FIELD(noWait); + COPY_SCALAR_FIELD(waitPolicy); COPY_SCALAR_FIELD(pushedDown); return newnode; @@ -2452,7 +2452,7 @@ _copyLockingClause(const LockingClause *from) COPY_NODE_FIELD(lockedRels); COPY_SCALAR_FIELD(strength); - COPY_SCALAR_FIELD(noWait); + COPY_SCALAR_FIELD(waitPolicy); return newnode; } diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 905468ee618..358395f61f4 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -2312,7 +2312,7 @@ _equalLockingClause(const LockingClause *a, const LockingClause *b) { COMPARE_NODE_FIELD(lockedRels); COMPARE_SCALAR_FIELD(strength); - COMPARE_SCALAR_FIELD(noWait); + COMPARE_SCALAR_FIELD(waitPolicy); return true; } @@ -2408,7 +2408,7 @@ _equalRowMarkClause(const RowMarkClause *a, const RowMarkClause *b) { COMPARE_SCALAR_FIELD(rti); COMPARE_SCALAR_FIELD(strength); - COMPARE_SCALAR_FIELD(noWait); + COMPARE_SCALAR_FIELD(waitPolicy); COMPARE_SCALAR_FIELD(pushedDown); return true; diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 1ff78ebddd3..56e486c8bc8 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -836,7 +836,7 @@ _outPlanRowMark(StringInfo str, const PlanRowMark *node) WRITE_UINT_FIELD(prti); WRITE_UINT_FIELD(rowmarkId); WRITE_ENUM_FIELD(markType, RowMarkType); - WRITE_BOOL_FIELD(noWait); + WRITE_BOOL_FIELD(waitPolicy); WRITE_BOOL_FIELD(isParent); } @@ -2136,7 +2136,7 @@ _outLockingClause(StringInfo str, const LockingClause *node) WRITE_NODE_FIELD(lockedRels); WRITE_ENUM_FIELD(strength, LockClauseStrength); - WRITE_BOOL_FIELD(noWait); + WRITE_ENUM_FIELD(waitPolicy, LockWaitPolicy); } static void @@ -2327,7 +2327,7 @@ _outRowMarkClause(StringInfo str, const RowMarkClause *node) WRITE_UINT_FIELD(rti); WRITE_ENUM_FIELD(strength, LockClauseStrength); - WRITE_BOOL_FIELD(noWait); + WRITE_ENUM_FIELD(waitPolicy, LockWaitPolicy); WRITE_BOOL_FIELD(pushedDown); } diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index a324100ed7f..a3efdd4eff2 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -321,7 +321,7 @@ _readRowMarkClause(void) READ_UINT_FIELD(rti); READ_ENUM_FIELD(strength, LockClauseStrength); - READ_BOOL_FIELD(noWait); + READ_ENUM_FIELD(waitPolicy, LockWaitPolicy); READ_BOOL_FIELD(pushedDown); READ_DONE(); diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index a509edd3aff..fb74d6bf1f4 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -2232,7 +2232,7 @@ preprocess_rowmarks(PlannerInfo *root) newrc->markType = ROW_MARK_KEYSHARE; break; } - newrc->noWait = rc->noWait; + newrc->waitPolicy = rc->waitPolicy; newrc->isParent = false; prowmarks = lappend(prowmarks, newrc); @@ -2260,7 +2260,7 @@ preprocess_rowmarks(PlannerInfo *root) newrc->markType = ROW_MARK_REFERENCE; else newrc->markType = ROW_MARK_COPY; - newrc->noWait = false; /* doesn't matter */ + newrc->waitPolicy = LockWaitBlock; /* doesn't matter */ newrc->isParent = false; prowmarks = lappend(prowmarks, newrc); diff --git a/src/backend/optimizer/prep/prepsecurity.c b/src/backend/optimizer/prep/prepsecurity.c index 51f10a488a8..b625b5cffc2 100644 --- a/src/backend/optimizer/prep/prepsecurity.c +++ b/src/backend/optimizer/prep/prepsecurity.c @@ -233,19 +233,19 @@ expand_security_qual(PlannerInfo *root, List *tlist, int rt_index, { case ROW_MARK_EXCLUSIVE: applyLockingClause(subquery, 1, LCS_FORUPDATE, - rc->noWait, false); + rc->waitPolicy, false); break; case ROW_MARK_NOKEYEXCLUSIVE: applyLockingClause(subquery, 1, LCS_FORNOKEYUPDATE, - rc->noWait, false); + rc->waitPolicy, false); break; case ROW_MARK_SHARE: applyLockingClause(subquery, 1, LCS_FORSHARE, - rc->noWait, false); + rc->waitPolicy, false); break; case ROW_MARK_KEYSHARE: applyLockingClause(subquery, 1, LCS_FORKEYSHARE, - rc->noWait, false); + rc->waitPolicy, false); break; case ROW_MARK_REFERENCE: case ROW_MARK_COPY: diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c index 1cec511e0f0..2fefc7d4f60 100644 --- a/src/backend/optimizer/prep/prepunion.c +++ b/src/backend/optimizer/prep/prepunion.c @@ -1389,7 +1389,7 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) newrc->prti = rti; newrc->rowmarkId = oldrc->rowmarkId; newrc->markType = oldrc->markType; - newrc->noWait = oldrc->noWait; + newrc->waitPolicy = oldrc->waitPolicy; newrc->isParent = false; root->rowMarks = lappend(root->rowMarks, newrc); diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index fb6c44c11c8..bd78e949853 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -2358,7 +2358,7 @@ transformLockingClause(ParseState *pstate, Query *qry, LockingClause *lc, allrels = makeNode(LockingClause); allrels->lockedRels = NIL; /* indicates all rels */ allrels->strength = lc->strength; - allrels->noWait = lc->noWait; + allrels->waitPolicy = lc->waitPolicy; if (lockedRels == NIL) { @@ -2372,13 +2372,13 @@ transformLockingClause(ParseState *pstate, Query *qry, LockingClause *lc, switch (rte->rtekind) { case RTE_RELATION: - applyLockingClause(qry, i, - lc->strength, lc->noWait, pushedDown); + applyLockingClause(qry, i, lc->strength, lc->waitPolicy, + pushedDown); rte->requiredPerms |= ACL_SELECT_FOR_UPDATE; break; case RTE_SUBQUERY: - applyLockingClause(qry, i, - lc->strength, lc->noWait, pushedDown); + applyLockingClause(qry, i, lc->strength, lc->waitPolicy, + pushedDown); /* * FOR UPDATE/SHARE of subquery is propagated to all of @@ -2424,15 +2424,13 @@ transformLockingClause(ParseState *pstate, Query *qry, LockingClause *lc, switch (rte->rtekind) { case RTE_RELATION: - applyLockingClause(qry, i, - lc->strength, lc->noWait, - pushedDown); + applyLockingClause(qry, i, lc->strength, + lc->waitPolicy, pushedDown); rte->requiredPerms |= ACL_SELECT_FOR_UPDATE; break; case RTE_SUBQUERY: - applyLockingClause(qry, i, - lc->strength, lc->noWait, - pushedDown); + applyLockingClause(qry, i, lc->strength, + lc->waitPolicy, pushedDown); /* see comment above */ transformLockingClause(pstate, rte->subquery, allrels, true); @@ -2499,7 +2497,8 @@ transformLockingClause(ParseState *pstate, Query *qry, LockingClause *lc, */ void applyLockingClause(Query *qry, Index rtindex, - LockClauseStrength strength, bool noWait, bool pushedDown) + LockClauseStrength strength, LockWaitPolicy waitPolicy, + bool pushedDown) { RowMarkClause *rc; @@ -2516,15 +2515,20 @@ applyLockingClause(Query *qry, Index rtindex, * a shared and exclusive lock at the same time; it'll end up being * exclusive anyway.) * - * We also consider that NOWAIT wins if it's specified both ways. This - * is a bit more debatable but raising an error doesn't seem helpful. - * (Consider for instance SELECT FOR UPDATE NOWAIT from a view that - * internally contains a plain FOR UPDATE spec.) + * Similarly, if the same RTE is specified with more than one lock wait + * policy, consider that NOWAIT wins over SKIP LOCKED, which in turn + * wins over waiting for the lock (the default). This is a bit more + * debatable but raising an error doesn't seem helpful. (Consider for + * instance SELECT FOR UPDATE NOWAIT from a view that internally + * contains a plain FOR UPDATE spec.) Having NOWAIT win over SKIP + * LOCKED is reasonable since the former throws an error in case of + * coming across a locked tuple, which may be undesirable in some cases + * but it seems better than silently returning inconsistent results. * * And of course pushedDown becomes false if any clause is explicit. */ rc->strength = Max(rc->strength, strength); - rc->noWait |= noWait; + rc->waitPolicy = Max(rc->waitPolicy, waitPolicy); rc->pushedDown &= pushedDown; return; } @@ -2533,7 +2537,7 @@ applyLockingClause(Query *qry, Index rtindex, rc = makeNode(RowMarkClause); rc->rti = rtindex; rc->strength = strength; - rc->noWait = noWait; + rc->waitPolicy = waitPolicy; rc->pushedDown = pushedDown; qry->rowMarks = lappend(qry->rowMarks, rc); } diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 77d2f29fc7a..c98c27a16d9 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -284,6 +284,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <boolean> opt_force opt_or_replace opt_grant_grant_option opt_grant_admin_option opt_nowait opt_if_exists opt_with_data +%type <ival> opt_nowait_or_skip %type <list> OptRoleList AlterOptRoleList %type <defelt> CreateOptRoleElem AlterOptRoleElem @@ -582,7 +583,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); LABEL LANGUAGE LARGE_P LAST_P LATERAL_P LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL - LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOGGED + LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOCKED LOGGED MAPPING MATCH MATERIALIZED MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE @@ -606,7 +607,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE - SHOW SIMILAR SIMPLE SMALLINT SNAPSHOT SOME STABLE STANDALONE_P START + SHOW SIMILAR SIMPLE SKIP SMALLINT SNAPSHOT SOME STABLE STANDALONE_P START STATEMENT STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING SYMMETRIC SYSID SYSTEM_P @@ -9370,6 +9371,12 @@ opt_nowait: NOWAIT { $$ = TRUE; } | /*EMPTY*/ { $$ = FALSE; } ; +opt_nowait_or_skip: + NOWAIT { $$ = LockWaitError; } + | SKIP LOCKED { $$ = LockWaitSkip; } + | /*EMPTY*/ { $$ = LockWaitBlock; } + ; + /***************************************************************************** * @@ -10011,12 +10018,12 @@ for_locking_items: ; for_locking_item: - for_locking_strength locked_rels_list opt_nowait + for_locking_strength locked_rels_list opt_nowait_or_skip { LockingClause *n = makeNode(LockingClause); n->lockedRels = $2; n->strength = $1; - n->noWait = $3; + n->waitPolicy = $3; $$ = (Node *) n; } ; @@ -13145,6 +13152,7 @@ unreserved_keyword: | LOCAL | LOCATION | LOCK_P + | LOCKED | LOGGED | MAPPING | MATCH @@ -13229,6 +13237,7 @@ unreserved_keyword: | SHARE | SHOW | SIMPLE + | SKIP | SNAPSHOT | STABLE | STANDALONE_P diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index 10d12cba179..e7021509017 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -63,7 +63,8 @@ static void rewriteValuesRTE(RangeTblEntry *rte, Relation target_relation, static void rewriteTargetListUD(Query *parsetree, RangeTblEntry *target_rte, Relation target_relation); static void markQueryForLocking(Query *qry, Node *jtnode, - LockClauseStrength strength, bool noWait, bool pushedDown); + LockClauseStrength strength, LockWaitPolicy waitPolicy, + bool pushedDown); static List *matchLocks(CmdType event, RuleLock *rulelocks, int varno, Query *parsetree); static Query *fireRIRrules(Query *parsetree, List *activeRIRs, @@ -1482,7 +1483,7 @@ ApplyRetrieveRule(Query *parsetree, */ if (rc != NULL) markQueryForLocking(rule_action, (Node *) rule_action->jointree, - rc->strength, rc->noWait, true); + rc->strength, rc->waitPolicy, true); return parsetree; } @@ -1500,7 +1501,8 @@ ApplyRetrieveRule(Query *parsetree, */ static void markQueryForLocking(Query *qry, Node *jtnode, - LockClauseStrength strength, bool noWait, bool pushedDown) + LockClauseStrength strength, LockWaitPolicy waitPolicy, + bool pushedDown) { if (jtnode == NULL) return; @@ -1511,15 +1513,15 @@ markQueryForLocking(Query *qry, Node *jtnode, if (rte->rtekind == RTE_RELATION) { - applyLockingClause(qry, rti, strength, noWait, pushedDown); + applyLockingClause(qry, rti, strength, waitPolicy, pushedDown); rte->requiredPerms |= ACL_SELECT_FOR_UPDATE; } else if (rte->rtekind == RTE_SUBQUERY) { - applyLockingClause(qry, rti, strength, noWait, pushedDown); + applyLockingClause(qry, rti, strength, waitPolicy, pushedDown); /* FOR UPDATE/SHARE of subquery is propagated to subquery's rels */ markQueryForLocking(rte->subquery, (Node *) rte->subquery->jointree, - strength, noWait, true); + strength, waitPolicy, true); } /* other RTE types are unaffected by FOR UPDATE */ } @@ -1529,14 +1531,14 @@ markQueryForLocking(Query *qry, Node *jtnode, ListCell *l; foreach(l, f->fromlist) - markQueryForLocking(qry, lfirst(l), strength, noWait, pushedDown); + markQueryForLocking(qry, lfirst(l), strength, waitPolicy, pushedDown); } else if (IsA(jtnode, JoinExpr)) { JoinExpr *j = (JoinExpr *) jtnode; - markQueryForLocking(qry, j->larg, strength, noWait, pushedDown); - markQueryForLocking(qry, j->rarg, strength, noWait, pushedDown); + markQueryForLocking(qry, j->larg, strength, waitPolicy, pushedDown); + markQueryForLocking(qry, j->rarg, strength, waitPolicy, pushedDown); } else elog(ERROR, "unrecognized node type: %d", diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 7237e5de839..6e41cbd142f 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -4446,8 +4446,10 @@ get_select_query_def(Query *query, deparse_context *context, appendStringInfo(buf, " OF %s", quote_identifier(get_rtable_name(rc->rti, context))); - if (rc->noWait) + if (rc->waitPolicy == LockWaitError) appendStringInfoString(buf, " NOWAIT"); + else if (rc->waitPolicy == LockWaitSkip) + appendStringInfoString(buf, " SKIP LOCKED"); } } diff --git a/src/include/access/heapam.h b/src/include/access/heapam.h index 493839f60e9..7f7166d832e 100644 --- a/src/include/access/heapam.h +++ b/src/include/access/heapam.h @@ -19,6 +19,7 @@ #include "nodes/primnodes.h" #include "storage/bufpage.h" #include "storage/lock.h" +#include "utils/lockwaitpolicy.h" #include "utils/relcache.h" #include "utils/snapshot.h" @@ -144,7 +145,7 @@ extern HTSU_Result heap_update(Relation relation, ItemPointer otid, CommandId cid, Snapshot crosscheck, bool wait, HeapUpdateFailureData *hufd, LockTupleMode *lockmode); extern HTSU_Result heap_lock_tuple(Relation relation, HeapTuple tuple, - CommandId cid, LockTupleMode mode, bool nowait, + CommandId cid, LockTupleMode mode, LockWaitPolicy wait_policy, bool follow_update, Buffer *buffer, HeapUpdateFailureData *hufd); extern void heap_inplace_update(Relation relation, HeapTuple tuple); diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index a3464a5df90..69ef30a3596 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201409293 +#define CATALOG_VERSION_NO 201410071 #endif diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h index 02661350d94..d167b496fce 100644 --- a/src/include/executor/executor.h +++ b/src/include/executor/executor.h @@ -16,6 +16,7 @@ #include "executor/execdesc.h" #include "nodes/parsenodes.h" +#include "utils/lockwaitpolicy.h" /* @@ -199,7 +200,8 @@ extern TupleTableSlot *EvalPlanQual(EState *estate, EPQState *epqstate, Relation relation, Index rti, int lockmode, ItemPointer tid, TransactionId priorXmax); extern HeapTuple EvalPlanQualFetch(EState *estate, Relation relation, - int lockmode, bool noWait, ItemPointer tid, TransactionId priorXmax); + int lockmode, LockWaitPolicy wait_policy, ItemPointer tid, + TransactionId priorXmax); extern void EvalPlanQualInit(EPQState *epqstate, EState *estate, Plan *subplan, List *auxrowmarks, int epqParam); extern void EvalPlanQualSetPlan(EPQState *epqstate, diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index b271f213f59..39d2c10bdfe 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -429,7 +429,7 @@ typedef struct ExecRowMark Index prti; /* parent range table index, if child */ Index rowmarkId; /* unique identifier for resjunk columns */ RowMarkType markType; /* see enum in nodes/plannodes.h */ - bool noWait; /* NOWAIT option */ + LockWaitPolicy waitPolicy; /* NOWAIT and SKIP LOCKED */ ItemPointerData curCtid; /* ctid of currently locked tuple, if any */ } ExecRowMark; diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index f3aa69e4a1b..cef95446984 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -23,6 +23,7 @@ #include "nodes/bitmapset.h" #include "nodes/primnodes.h" #include "nodes/value.h" +#include "utils/lockwaitpolicy.h" /* Possible sources of a Query */ typedef enum QuerySource @@ -631,7 +632,7 @@ typedef struct LockingClause NodeTag type; List *lockedRels; /* FOR [KEY] UPDATE/SHARE relations */ LockClauseStrength strength; - bool noWait; /* NOWAIT option */ + LockWaitPolicy waitPolicy; /* NOWAIT and SKIP LOCKED */ } LockingClause; /* @@ -976,7 +977,7 @@ typedef struct RowMarkClause NodeTag type; Index rti; /* range table index of target relation */ LockClauseStrength strength; - bool noWait; /* NOWAIT option */ + LockWaitPolicy waitPolicy; /* NOWAIT and SKIP LOCKED */ bool pushedDown; /* pushed down from higher query level? */ } RowMarkClause; diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index 18394946f86..fb02390da51 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -17,6 +17,7 @@ #include "access/sdir.h" #include "nodes/bitmapset.h" #include "nodes/primnodes.h" +#include "utils/lockwaitpolicy.h" /* ---------------------------------------------------------------- @@ -834,7 +835,7 @@ typedef struct PlanRowMark Index prti; /* range table index of parent relation */ Index rowmarkId; /* unique identifier for resjunk columns */ RowMarkType markType; /* see enum above */ - bool noWait; /* NOWAIT option */ + LockWaitPolicy waitPolicy; /* NOWAIT and SKIP LOCKED options */ bool isParent; /* true if this is a "dummy" parent entry */ } PlanRowMark; diff --git a/src/include/parser/analyze.h b/src/include/parser/analyze.h index 370a445b7fc..f5da6bfecef 100644 --- a/src/include/parser/analyze.h +++ b/src/include/parser/analyze.h @@ -39,6 +39,7 @@ extern bool analyze_requires_snapshot(Node *parseTree); extern char *LCS_asString(LockClauseStrength strength); extern void CheckSelectLocking(Query *qry, LockClauseStrength strength); extern void applyLockingClause(Query *qry, Index rtindex, - LockClauseStrength strength, bool noWait, bool pushedDown); + LockClauseStrength strength, + LockWaitPolicy waitPolicy, bool pushedDown); #endif /* ANALYZE_H */ diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 3c8c1b9e252..e14dc9a1395 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -230,6 +230,7 @@ PG_KEYWORD("localtime", LOCALTIME, RESERVED_KEYWORD) PG_KEYWORD("localtimestamp", LOCALTIMESTAMP, RESERVED_KEYWORD) PG_KEYWORD("location", LOCATION, UNRESERVED_KEYWORD) PG_KEYWORD("lock", LOCK_P, UNRESERVED_KEYWORD) +PG_KEYWORD("locked", LOCKED, UNRESERVED_KEYWORD) PG_KEYWORD("logged", LOGGED, UNRESERVED_KEYWORD) PG_KEYWORD("mapping", MAPPING, UNRESERVED_KEYWORD) PG_KEYWORD("match", MATCH, UNRESERVED_KEYWORD) @@ -345,6 +346,7 @@ PG_KEYWORD("share", SHARE, UNRESERVED_KEYWORD) PG_KEYWORD("show", SHOW, UNRESERVED_KEYWORD) PG_KEYWORD("similar", SIMILAR, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("simple", SIMPLE, UNRESERVED_KEYWORD) +PG_KEYWORD("skip", SKIP, UNRESERVED_KEYWORD) PG_KEYWORD("smallint", SMALLINT, COL_NAME_KEYWORD) PG_KEYWORD("snapshot", SNAPSHOT, UNRESERVED_KEYWORD) PG_KEYWORD("some", SOME, RESERVED_KEYWORD) diff --git a/src/include/utils/lockwaitpolicy.h b/src/include/utils/lockwaitpolicy.h new file mode 100644 index 00000000000..7ec9c26717d --- /dev/null +++ b/src/include/utils/lockwaitpolicy.h @@ -0,0 +1,31 @@ +/*------------------------------------------------------------------------- + * lockwaitpolicy.h + * Header file for LockWaitPolicy enum. + * + * Copyright (c) 2014, PostgreSQL Global Development Group + * + * src/include/utils/lockwaitpolicy.h + *------------------------------------------------------------------------- + */ +#ifndef LOCKWAITPOLICY_H +#define LOCKWAITPOLICY_H + +/* + * This enum controls how to deal with rows being locked by FOR UPDATE/SHARE + * clauses (i.e., NOWAIT and SKIP LOCKED clauses). The ordering here is + * important, because the highest numerical value takes precedence when a + * RTE is specified multiple ways. See applyLockingClause. + */ +typedef enum +{ + /* Wait for the lock to become available (default behavior) */ + LockWaitBlock, + + /* Skip rows that can't be locked (SKIP LOCKED) */ + LockWaitSkip, + + /* Raise an error if a row cannot be locked (NOWAIT) */ + LockWaitError +} LockWaitPolicy; + +#endif /* LOCKWAITPOLICY_H */ diff --git a/src/include/utils/snapshot.h b/src/include/utils/snapshot.h index d8e8b351ed2..53e474fbb29 100644 --- a/src/include/utils/snapshot.h +++ b/src/include/utils/snapshot.h @@ -104,7 +104,8 @@ typedef enum HeapTupleInvisible, HeapTupleSelfUpdated, HeapTupleUpdated, - HeapTupleBeingUpdated + HeapTupleBeingUpdated, + HeapTupleWouldBlock /* can be returned by heap_tuple_lock */ } HTSU_Result; #endif /* SNAPSHOT_H */ diff --git a/src/test/isolation/expected/skip-locked-2.out b/src/test/isolation/expected/skip-locked-2.out new file mode 100644 index 00000000000..9240543f3f4 --- /dev/null +++ b/src/test/isolation/expected/skip-locked-2.out @@ -0,0 +1,49 @@ +Parsed test spec with 2 sessions + +starting permutation: s1a s2a s2b s1b s2c +step s1a: SELECT * FROM queue ORDER BY id FOR SHARE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2a: SELECT * FROM queue ORDER BY id FOR SHARE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s1b: COMMIT; +step s2c: COMMIT; + +starting permutation: s2a s1a s2b s1b s2c +step s2a: SELECT * FROM queue ORDER BY id FOR SHARE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1a: SELECT * FROM queue ORDER BY id FOR SHARE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s1b: COMMIT; +step s2c: COMMIT; + +starting permutation: s2a s2b s1a s1b s2c +step s2a: SELECT * FROM queue ORDER BY id FOR SHARE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1a: SELECT * FROM queue ORDER BY id FOR SHARE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s1b: COMMIT; +step s2c: COMMIT; diff --git a/src/test/isolation/expected/skip-locked-3.out b/src/test/isolation/expected/skip-locked-3.out new file mode 100644 index 00000000000..fa8fe87d8a3 --- /dev/null +++ b/src/test/isolation/expected/skip-locked-3.out @@ -0,0 +1,19 @@ +Parsed test spec with 3 sessions + +starting permutation: s1a s2a s3a s1b s2b s3b +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE LIMIT 1; +id data status + +1 foo NEW +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE LIMIT 1; <waiting ...> +step s3a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s1b: COMMIT; +step s2a: <... completed> +id data status + +1 foo NEW +step s2b: COMMIT; +step s3b: COMMIT; diff --git a/src/test/isolation/expected/skip-locked-4.out b/src/test/isolation/expected/skip-locked-4.out new file mode 100644 index 00000000000..2c9cfe895e6 --- /dev/null +++ b/src/test/isolation/expected/skip-locked-4.out @@ -0,0 +1,21 @@ +Parsed test spec with 2 sessions + +starting permutation: s2a s1a s2b s2c s2d s2e s1b s2f +step s2a: SELECT pg_advisory_lock(0); +pg_advisory_lock + + +step s1a: SELECT * FROM foo WHERE pg_advisory_lock(0) IS NOT NULL ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; <waiting ...> +step s2b: UPDATE foo SET data = data WHERE id = 1; +step s2c: BEGIN; +step s2d: UPDATE foo SET data = data WHERE id = 1; +step s2e: SELECT pg_advisory_unlock(0); +pg_advisory_unlock + +t +step s1a: <... completed> +id data + +2 x +step s1b: COMMIT; +step s2f: COMMIT; diff --git a/src/test/isolation/expected/skip-locked-4_1.out b/src/test/isolation/expected/skip-locked-4_1.out new file mode 100644 index 00000000000..552429ae891 --- /dev/null +++ b/src/test/isolation/expected/skip-locked-4_1.out @@ -0,0 +1,19 @@ +Parsed test spec with 2 sessions + +starting permutation: s2a s1a s2b s2c s2d s2e s1b s2f +step s2a: SELECT pg_advisory_lock(0); +pg_advisory_lock + + +step s1a: SELECT * FROM foo WHERE pg_advisory_lock(0) IS NOT NULL ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; <waiting ...> +step s2b: UPDATE foo SET data = data WHERE id = 1; +step s2c: BEGIN; +step s2d: UPDATE foo SET data = data WHERE id = 1; +step s2e: SELECT pg_advisory_unlock(0); +pg_advisory_unlock + +t +step s1a: <... completed> +error in steps s2e s1a: ERROR: could not serialize access due to concurrent update +step s1b: COMMIT; +step s2f: COMMIT; diff --git a/src/test/isolation/expected/skip-locked.out b/src/test/isolation/expected/skip-locked.out new file mode 100644 index 00000000000..f9b9cf28be6 --- /dev/null +++ b/src/test/isolation/expected/skip-locked.out @@ -0,0 +1,401 @@ +Parsed test spec with 2 sessions + +starting permutation: s1a s1b s1c s2a s2b s2c +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1c: COMMIT; +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2c: COMMIT; + +starting permutation: s1a s1b s2a s1c s2b s2c +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s1c: COMMIT; +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2c: COMMIT; + +starting permutation: s1a s1b s2a s2b s1c s2c +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s1c: COMMIT; +step s2c: COMMIT; + +starting permutation: s1a s1b s2a s2b s2c s1c +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s2c: COMMIT; +step s1c: COMMIT; + +starting permutation: s1a s2a s1b s1c s2b s2c +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1c: COMMIT; +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2c: COMMIT; + +starting permutation: s1a s2a s1b s2b s1c s2c +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s1c: COMMIT; +step s2c: COMMIT; + +starting permutation: s1a s2a s1b s2b s2c s1c +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s2c: COMMIT; +step s1c: COMMIT; + +starting permutation: s1a s2a s2b s1b s1c s2c +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1c: COMMIT; +step s2c: COMMIT; + +starting permutation: s1a s2a s2b s1b s2c s1c +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2c: COMMIT; +step s1c: COMMIT; + +starting permutation: s1a s2a s2b s2c s1b s1c +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s2c: COMMIT; +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1c: COMMIT; + +starting permutation: s2a s1a s1b s1c s2b s2c +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s1c: COMMIT; +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2c: COMMIT; + +starting permutation: s2a s1a s1b s2b s1c s2c +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1c: COMMIT; +step s2c: COMMIT; + +starting permutation: s2a s1a s1b s2b s2c s1c +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2c: COMMIT; +step s1c: COMMIT; + +starting permutation: s2a s1a s2b s1b s1c s2c +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s1c: COMMIT; +step s2c: COMMIT; + +starting permutation: s2a s1a s2b s1b s2c s1c +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s2c: COMMIT; +step s1c: COMMIT; + +starting permutation: s2a s1a s2b s2c s1b s1c +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2c: COMMIT; +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1c: COMMIT; + +starting permutation: s2a s2b s1a s1b s1c s2c +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s1c: COMMIT; +step s2c: COMMIT; + +starting permutation: s2a s2b s1a s1b s2c s1c +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s2c: COMMIT; +step s1c: COMMIT; + +starting permutation: s2a s2b s1a s2c s1b s1c +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +2 bar NEW +step s2c: COMMIT; +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1c: COMMIT; + +starting permutation: s2a s2b s2c s1a s1b s1c +step s2a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s2c: COMMIT; +step s1a: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1b: SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; +id data status + +1 foo NEW +step s1c: COMMIT; diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule index 3241a91505c..79a79568143 100644 --- a/src/test/isolation/isolation_schedule +++ b/src/test/isolation/isolation_schedule @@ -27,6 +27,10 @@ test: nowait-2 test: nowait-3 test: nowait-4 test: nowait-5 +test: skip-locked +test: skip-locked-2 +test: skip-locked-3 +test: skip-locked-4 test: drop-index-concurrently-1 test: alter-table-1 test: timeouts diff --git a/src/test/isolation/specs/nowait-4.spec b/src/test/isolation/specs/nowait-4.spec index 5dbebcabb03..48ac777d78a 100644 --- a/src/test/isolation/specs/nowait-4.spec +++ b/src/test/isolation/specs/nowait-4.spec @@ -27,4 +27,9 @@ step "s2d" { UPDATE foo SET data = data; } step "s2e" { SELECT pg_advisory_unlock(0); } step "s2f" { COMMIT; } +# s1 takes a snapshot but then waits on an advisory lock, then s2 +# updates the row in one transaction, then again in another without +# committing, before allowing s1 to proceed to try to lock a row; +# because it has a snapshot that sees the older version, we reach the +# waiting code in EvalPlanQualFetch which ereports when in NOWAIT mode. permutation "s2a" "s1a" "s2b" "s2c" "s2d" "s2e" "s1b" "s2f" diff --git a/src/test/isolation/specs/skip-locked-2.spec b/src/test/isolation/specs/skip-locked-2.spec new file mode 100644 index 00000000000..a179d343534 --- /dev/null +++ b/src/test/isolation/specs/skip-locked-2.spec @@ -0,0 +1,41 @@ +# Test SKIP LOCKED with multixact locks. + +setup +{ + CREATE TABLE queue ( + id int PRIMARY KEY, + data text NOT NULL, + status text NOT NULL + ); + INSERT INTO queue VALUES (1, 'foo', 'NEW'), (2, 'bar', 'NEW'); +} + +teardown +{ + DROP TABLE queue; +} + +session "s1" +setup { BEGIN; } +step "s1a" { SELECT * FROM queue ORDER BY id FOR SHARE SKIP LOCKED LIMIT 1; } +step "s1b" { COMMIT; } + +session "s2" +setup { BEGIN; } +step "s2a" { SELECT * FROM queue ORDER BY id FOR SHARE SKIP LOCKED LIMIT 1; } +step "s2b" { SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; } +step "s2c" { COMMIT; } + +# s1 and s2 both get SHARE lock, creating a multixact lock, then s2 +# tries to update to UPDATE but skips the record because it can't +# acquire a multixact lock +permutation "s1a" "s2a" "s2b" "s1b" "s2c" + +# the same but with the SHARE locks acquired in a different order, so +# s2 again skips because it can't acquired a multixact lock +permutation "s2a" "s1a" "s2b" "s1b" "s2c" + +# s2 acquires SHARE then UPDATE, then s1 tries to acquire SHARE but +# can't so skips the first record because it can't acquire a regular +# lock +permutation "s2a" "s2b" "s1a" "s1b" "s2c" diff --git a/src/test/isolation/specs/skip-locked-3.spec b/src/test/isolation/specs/skip-locked-3.spec new file mode 100644 index 00000000000..30bf4c6b1a9 --- /dev/null +++ b/src/test/isolation/specs/skip-locked-3.spec @@ -0,0 +1,36 @@ +# Test SKIP LOCKED with tuple locks. + +setup +{ + CREATE TABLE queue ( + id int PRIMARY KEY, + data text NOT NULL, + status text NOT NULL + ); + INSERT INTO queue VALUES (1, 'foo', 'NEW'), (2, 'bar', 'NEW'); +} + +teardown +{ + DROP TABLE queue; +} + +session "s1" +setup { BEGIN; } +step "s1a" { SELECT * FROM queue ORDER BY id FOR UPDATE LIMIT 1; } +step "s1b" { COMMIT; } + +session "s2" +setup { BEGIN; } +step "s2a" { SELECT * FROM queue ORDER BY id FOR UPDATE LIMIT 1; } +step "s2b" { COMMIT; } + +session "s3" +setup { BEGIN; } +step "s3a" { SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; } +step "s3b" { COMMIT; } + +# s3 skips to the second record because it can't obtain the tuple lock +# (s2 holds the tuple lock because it is next in line to obtain the +# row lock, and s1 holds the row lock) +permutation "s1a" "s2a" "s3a" "s1b" "s2b" "s3b" diff --git a/src/test/isolation/specs/skip-locked-4.spec b/src/test/isolation/specs/skip-locked-4.spec new file mode 100644 index 00000000000..458e2837edf --- /dev/null +++ b/src/test/isolation/specs/skip-locked-4.spec @@ -0,0 +1,36 @@ +# Test SKIP LOCKED with an updated tuple chain. + +setup +{ + CREATE TABLE foo ( + id int PRIMARY KEY, + data text NOT NULL + ); + INSERT INTO foo VALUES (1, 'x'), (2, 'x'); +} + +teardown +{ + DROP TABLE foo; +} + +session "s1" +setup { BEGIN; } +step "s1a" { SELECT * FROM foo WHERE pg_advisory_lock(0) IS NOT NULL ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; } +step "s1b" { COMMIT; } + +session "s2" +step "s2a" { SELECT pg_advisory_lock(0); } +step "s2b" { UPDATE foo SET data = data WHERE id = 1; } +step "s2c" { BEGIN; } +step "s2d" { UPDATE foo SET data = data WHERE id = 1; } +step "s2e" { SELECT pg_advisory_unlock(0); } +step "s2f" { COMMIT; } + +# s1 takes a snapshot but then waits on an advisory lock, then s2 +# updates the row in one transaction, then again in another without +# committing, before allowing s1 to proceed to try to lock a row; +# because it has a snapshot that sees the older version, we reach the +# waiting code in EvalPlanQualFetch which skips rows when in SKIP +# LOCKED mode, so s1 sees the second row +permutation "s2a" "s1a" "s2b" "s2c" "s2d" "s2e" "s1b" "s2f"
\ No newline at end of file diff --git a/src/test/isolation/specs/skip-locked.spec b/src/test/isolation/specs/skip-locked.spec new file mode 100644 index 00000000000..3565963c455 --- /dev/null +++ b/src/test/isolation/specs/skip-locked.spec @@ -0,0 +1,28 @@ +# Test SKIP LOCKED when regular row locks can't be acquired. + +setup +{ + CREATE TABLE queue ( + id int PRIMARY KEY, + data text NOT NULL, + status text NOT NULL + ); + INSERT INTO queue VALUES (1, 'foo', 'NEW'), (2, 'bar', 'NEW'); +} + +teardown +{ + DROP TABLE queue; +} + +session "s1" +setup { BEGIN; } +step "s1a" { SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; } +step "s1b" { SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; } +step "s1c" { COMMIT; } + +session "s2" +setup { BEGIN; } +step "s2a" { SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; } +step "s2b" { SELECT * FROM queue ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; } +step "s2c" { COMMIT; } |