When FOR UPDATE/SHARE is used with LIMIT, put the LockRows plan node
Tom Lane <tgl@sss.pgh.pa.us>
When FOR UPDATE/SHARE is used with LIMIT, put the LockRows plan node underneath the Limit node, not atop it. This fixes the old problem that such a query might unexpectedly return fewer rows than the LIMIT says, due to LockRows discarding updated rows. There is a related problem that LockRows might destroy the sort ordering produced by earlier steps; but fixing that by pushing LockRows below Sort would create serious performance problems that are unjustified in many real-world applications, as well as potential deadlock problems from locking many more rows than expected. Instead, keep the present semantics of applying FOR UPDATE after ORDER BY within a single query level; but allow the user to specify the other way by writing FOR UPDATE in a sub-select. To make that work, track whether FOR UPDATE appeared explicitly in sub-selects or got pushed down from the parent, and don't flatten a sub-select that contained an explicit FOR UPDATE.
Files
| Path | Change | +/− |
|---|---|---|
| doc/src/sgml/ref/select.sgml | modified | +83 −41 |
| src/backend/nodes/copyfuncs.c | modified | +3 −1 |
| src/backend/nodes/equalfuncs.c | modified | +3 −1 |
| src/backend/nodes/outfuncs.c | modified | +3 −1 |
| src/backend/nodes/readfuncs.c | modified | +3 −1 |
| src/backend/optimizer/plan/planner.c | modified | +37 −20 |
| src/backend/optimizer/prep/prepjointree.c | modified | +8 −1 |
| src/backend/parser/analyze.c | modified | +37 −18 |
| src/backend/rewrite/rewriteHandler.c | modified | +14 −22 |
| src/backend/utils/adt/ruleutils.c | modified | +21 −14 |
| src/include/catalog/catversion.h | modified | +2 −2 |
| src/include/nodes/parsenodes.h | modified | +9 −2 |
| src/include/parser/analyze.h | modified | +2 −2 |