From 46e3a16b050a23b924e5d8a75c8bb7068c26aa96 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 28 Oct 2009 14:55:47 +0000 Subject: 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. --- src/include/nodes/parsenodes.h | 11 +++++++++-- 1 file changed, 9 insertions(+), 2 deletions(-) (limited to 'src/include/nodes/parsenodes.h') diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 450a89fe85b..2078526092b 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -13,7 +13,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.411 2009/10/26 02:26:41 tgl Exp $ + * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.412 2009/10/28 14:55:46 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -118,6 +118,7 @@ typedef struct Query bool hasSubLinks; /* has subquery SubLink */ bool hasDistinctOn; /* distinctClause is from DISTINCT ON */ bool hasRecursive; /* WITH RECURSIVE was specified */ + bool hasForUpdate; /* FOR UPDATE or FOR SHARE was specified */ List *cteList; /* WITH list (of CommonTableExpr's) */ @@ -803,7 +804,12 @@ typedef struct WindowClause * parser output representation of FOR UPDATE/SHARE clauses * * Query.rowMarks contains a separate RowMarkClause node for each relation - * identified as a FOR UPDATE/SHARE target. + * identified as a FOR UPDATE/SHARE target. If FOR UPDATE/SHARE is applied + * to a subquery, we generate RowMarkClauses for all normal and subquery rels + * in the subquery, but they are marked pushedDown = true to distinguish them + * from clauses that were explicitly written at this query level. Also, + * Query.hasForUpdate tells whether there were explicit FOR UPDATE/SHARE + * clauses in the current query level. */ typedef struct RowMarkClause { @@ -811,6 +817,7 @@ typedef struct RowMarkClause Index rti; /* range table index of target relation */ bool forUpdate; /* true = FOR UPDATE, false = FOR SHARE */ bool noWait; /* NOWAIT option */ + bool pushedDown; /* pushed down from higher query level? */ } RowMarkClause; /* -- cgit v1.2.3