From bd02b48ba44c6e0593320a9c3e071ba78efe1c04 Mon Sep 17 00:00:00 2001
From: Tom Lane
Date: Tue, 27 Oct 2009 17:11:30 +0000
Subject: Make FOR UPDATE/SHARE in the primary query not propagate into WITH
 queries; for example in   WITH w AS (SELECT * FROM foo) SELECT * FROM w, bar
 ... FOR UPDATE the FOR UPDATE will now affect bar but not foo.  This is more
 useful and consistent than the original 8.4 behavior, which tried to
 propagate FOR UPDATE into the WITH query but always failed due to assorted
 implementation restrictions.  Even though we are in process of removing those
 restrictions, it seems correct on philosophical grounds to not let the outer
 query's FOR UPDATE affect the WITH query.

In passing, fix isLockedRel which frequently got things wrong in
nested-subquery cases: "FOR UPDATE OF foo" applies to an alias foo in the
current query level, not subqueries.  This has been broken for a long time,
but it doesn't seem worth back-patching further than 8.4 because the actual
consequences are minimal.  At worst the parser would sometimes get
RowShareLock on a relation when it should be AccessShareLock or vice versa.
That would only make a difference if someone were using ExclusiveLock
concurrently, which no standard operation does, and anyway FOR UPDATE
doesn't result in visible changes so it's not clear that the someone would
notice any problem.  Between that and the fact that FOR UPDATE barely works
with subqueries at all in existing releases, I'm not excited about worrying
about it.
---
 doc/src/sgml/ref/select.sgml | 7 ++++++-
 1 file changed, 6 insertions(+), 1 deletion(-)

(limited to 'doc/src')

diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 96442a93084..7c7756808ea 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.122.2.1 2009/08/27 20:08:12 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.122.2.2 2009/10/27 17:11:30 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -1126,6 +1126,11 @@ FOR SHARE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ]
     If <literal>FOR UPDATE</literal> or <literal>FOR SHARE</literal> is
     applied to a view or sub-query, it affects all tables used in
     the view or sub-query.
+    However, <literal>FOR UPDATE</literal>/<literal>FOR SHARE</literal>
+    do not apply to <literal>WITH</> queries referenced by the primary query.
+    If you want row locking to occur within a <literal>WITH</> query, specify
+    <literal>FOR UPDATE</literal> or <literal>FOR SHARE</literal> within the
+    <literal>WITH</> query.
    </para>
 
    <para>
-- 
cgit v1.2.3