Implement SKIP LOCKED for row-level locks

Enterprise / PostgreSQL - Alvaro Herrera [alvh.no-ip.org] - 7 October 2014 15:23 UTC

This clause changes the behavior of SELECT locking clauses in the presence of locked rows: instead of causing a process to block waiting for the locks held by other processes (or raise an error, with NOWAIT), SKIP LOCKED makes the new reader skip over such rows. While this is not appropriate behavior for general purposes, there are some cases in which it is useful, such as queue-like tables.

Catalog version bumped because this patch changes the representation of stored rules.

Reviewed by Craig Ringer (based on a previous attempt at an implementation by Simon Riggs, who also provided input on the syntax used in the current patch), David Rowley, and Álvaro Herrera.

Author: Thomas Munro

df630b0 Implement SKIP LOCKED for row-level locks
doc/src/sgml/ref/select.sgml | 26 +-
doc/src/sgml/sql.sgml | 2 +-
src/backend/access/heap/heapam.c | 108 ++++--
src/backend/commands/trigger.c | 2 +-
src/backend/executor/execMain.c | 45 ++-
src/backend/executor/nodeLockRows.c | 15 +-
src/backend/nodes/copyfuncs.c | 6 +-
src/backend/nodes/equalfuncs.c | 4 +-
src/backend/nodes/outfuncs.c | 6 +-
src/backend/nodes/readfuncs.c | 2 +-
src/backend/optimizer/plan/planner.c | 4 +-
src/backend/optimizer/prep/prepsecurity.c | 8 +-
src/backend/optimizer/prep/prepunion.c | 2 +-
src/backend/parser/analyze.c | 40 ++-
src/backend/parser/gram.y | 17 +-
src/backend/rewrite/rewriteHandler.c | 20 +-
src/backend/utils/adt/ruleutils.c | 4 +-
src/include/access/heapam.h | 3 +-
src/include/catalog/catversion.h | 2 +-
src/include/executor/executor.h | 4 +-
src/include/nodes/execnodes.h | 2 +-
src/include/nodes/parsenodes.h | 5 +-
src/include/nodes/plannodes.h | 3 +-
src/include/parser/analyze.h | 3 +-
src/include/parser/kwlist.h | 2 +
src/include/utils/lockwaitpolicy.h | 31 ++
src/include/utils/snapshot.h | 3 +-
src/test/isolation/expected/skip-locked-2.out | 49 +++
src/test/isolation/expected/skip-locked-3.out | 19 ++
src/test/isolation/expected/skip-locked-4.out | 21 ++
src/test/isolation/expected/skip-locked-4_1.out | 19 ++
src/test/isolation/expected/skip-locked.out | 401 +++++++++++++++++++++++
src/test/isolation/isolation_schedule | 4 +
src/test/isolation/specs/nowait-4.spec | 5 +
src/test/isolation/specs/skip-locked-2.spec | 41 +++
src/test/isolation/specs/skip-locked-3.spec | 36 ++
src/test/isolation/specs/skip-locked-4.spec | 36 ++
src/test/isolation/specs/skip-locked.spec | 28 ++
38 files changed, 907 insertions(+), 121 deletions(-)

Upstream: git.postgresql.org

  • Share