From: David Christensen <david@endpoint.com>
Content-Type: multipart/signed;
	boundary="Apple-Mail=_3654762C-5BBB-4CF1-B302-D9F5E82F3F2F";
	protocol="application/pgp-signature";
	micalg=pgp-sha256
Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.4\))
Subject: Re: BUG #16676: SELECT ... FETCH FIRST ROW WITH TIES FOR UPDATE SKIP
 LOCKED locks rows it doesn't return
Date: Mon, 19 Oct 2020 17:02:04 -0500
References: <16676-fd62c3c835880da6@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
In-Reply-To: <16676-fd62c3c835880da6@postgresql.org>
Message-Id: <4143F72C-37BA-43B3-A768-259907CBA985@endpoint.com>
Precedence: bulk


--Apple-Mail=_3654762C-5BBB-4CF1-B302-D9F5E82F3F2F
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii

Proposed fix:


Reorder Limit/LockRows nodes to prevent locking extra tuples in FETCH =
FIRST WITH TIES

Reference bug report: 16676

1 file changed, 12 insertions(+), 12 deletions(-)
src/backend/optimizer/plan/planner.c | 24 ++++++++++++------------

modified   src/backend/optimizer/plan/planner.c
@@ -2293,6 +2293,18 @@ grouping_planner(PlannerInfo *root, bool =
inheritance_update,
 	{
 		Path	   *path =3D (Path *) lfirst(lc);

+		/*
+		 * If there is a LIMIT/OFFSET clause, add the LIMIT =
node.
+		 */
+		if (limit_needed(parse))
+		{
+			path =3D (Path *) create_limit_path(root, =
final_rel, path,
+										=
	  parse->limitOffset,
+										=
	  parse->limitCount,
+										=
	  parse->limitOption,
+										=
	  offset_est, count_est);
+		}
+
 		/*
 		 * If there is a FOR [KEY] UPDATE/SHARE clause, add the =
LockRows node.
 		 * (Note: we intentionally test parse->rowMarks not =
root->rowMarks
@@ -2307,18 +2319,6 @@ grouping_planner(PlannerInfo *root, bool =
inheritance_update,
 										=
		 assign_special_exec_param(root));
 		}

-		/*
-		 * If there is a LIMIT/OFFSET clause, add the LIMIT =
node.
-		 */
-		if (limit_needed(parse))
-		{
-			path =3D (Path *) create_limit_path(root, =
final_rel, path,
-										=
	  parse->limitOffset,
-										=
	  parse->limitCount,
-										=
	  parse->limitOption,
-										=
	  offset_est, count_est);
-		}
-
 		/*
 		 * If this is an INSERT/UPDATE/DELETE, and we're not =
being called from
 		 * inheritance_planner, add the ModifyTable node.


--Apple-Mail=_3654762C-5BBB-4CF1-B302-D9F5E82F3F2F
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
	filename=signature.asc
Content-Type: application/pgp-signature;
	name=signature.asc
Content-Description: Message signed with OpenPGP

-----BEGIN PGP SIGNATURE-----

iQIzBAEBCAAdFiEECKA1l4YEfF4eK4/eN0ojyd+bZbgFAl+ODNwACgkQN0ojyd+b
ZbjgExAAmN8JpHF4vxSS171w3LNiQP2QDemmkoD5jEE68jMt4KFuVbXJBlIsksgu
mlvhGyiEhhRtuQuc0Qqh0Ds4T42QvyQ3oNrS5wSrIR+r3XPRexzH2PrHoHG2kihS
jTIIlHxgeOMnw6H/ajsQBS/TWHyjXxvj6EoQziyByMO/fYvFh3dP+OPecZ9raAiB
v5u934c+a55oFxrZor5iL+GFgNSx9i3b6QvRmescqRkx3y3J4ADpGFxH7u1BSIDw
WgUHYKPzjS5xYGqUEpXtZujHmVsbDg+kdj2n0Ka2zi53ZnP2//xKpU0EQYwQQ+n0
vfFaowcCxNkn7j2/R7mOadiqTSKPKDqTwKZz/i0+HKeDbaxavZhEdHJuvGMwdwOf
WAQ6lj0xHdpV3BnGe1v/bzhqaxmlfxtz3dMoDIUqdkRztJHQz9YIC+qnQupS090B
GIKrlFgFo4jYFhE3/VQ8Vs7APzgMqhuAnVbixOO+SRWqIlAbV12g9HMhUe2FaLuP
iizU2dwLE4UjwNKQ73gOTDUiHUuG6lQOm5IMn7QuJeIaOCulRT6X5b+tMcpiAG62
VPDOhspIjW61G3+L2ygzxSRCNQqFksqPhaHvQIwIU8yvQq2HY+Ty5MrnwSrc00YV
woGsjM6pH7Q6p3ow1veWIpvWXKS1lmcpwo+eTVVo2EREYBN4pB4=
=8mhs
-----END PGP SIGNATURE-----

--Apple-Mail=_3654762C-5BBB-4CF1-B302-D9F5E82F3F2F--