From: David Christensen <david@endpoint.com>
Message-Id: <C0CF9D56-0392-43D2-8577-5F8A01AF2B56@endpoint.com>
Content-Type: multipart/signed;
	boundary="Apple-Mail=_EA24B22B-41AC-48DD-B181-D4252F4F6E0F";
	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: Tue, 20 Oct 2020 11:18:20 -0500
In-Reply-To: <05226762-2C0B-44DE-A584-D9FCCFF06B5F@endpoint.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>,
 pgsql-bugs@lists.postgresql.org
To: Tom Lane <tgl@sss.pgh.pa.us>
References: <20201020134114.GA11045@alvherre.pgsql>
 <604509.1603203416@sss.pgh.pa.us>
 <05226762-2C0B-44DE-A584-D9FCCFF06B5F@endpoint.com>
Precedence: bulk


--Apple-Mail=_EA24B22B-41AC-48DD-B181-D4252F4F6E0F
Content-Type: multipart/mixed;
	boundary="Apple-Mail=_C27606DC-B4E7-4A58-A5FB-01213B576E49"


--Apple-Mail=_C27606DC-B4E7-4A58-A5FB-01213B576E49
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

> On Oct 20, 2020, at 9:23 AM, David Christensen <david@endpoint.com> =
wrote:
>=20
>> On Oct 20, 2020, at 9:16 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>=20
>> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
>>> On 2020-Oct-19, David Christensen wrote:
>>>> Maybe splitting LockRows into two nodes, one for locking and one =
for
>>>> emitting unlocked nodes then interleaving Limit in between? (Or =
only
>>>> doing something along these lines for this admittedly narrow use =
case.)
>>=20
>>> I was having a similar idea, but the main reason I don't think it's =
a
>>> good fix is that we can't backpatch such a change to pg13.
>>=20
>> Um, why not?  I don't have a position yet on whether this is a good =
way
>> to fix it; but if we did do it, AFAICS the only thing we'd have to be
>> careful of in v13 is not renumbering existing NodeTag values.
>>=20
>> If your concern is just that EXPLAIN plans will look different, the =
same
>> could be said of David's other proposal.
>=20
> If we can determine the scope to which these different nodes might be =
used and make it so it still uses the existing node structure for most =
existing cases (i.e., maybe an additional parameter to =
create_lockrows_plan or similar) then that might work; AFAIK, this is =
the first time we=E2=80=99ve had to consider this sort of thing (though =
I do wonder if there might be something in window functions which might =
bump into this sort of issue).
>=20
> I=E2=80=99m working on an isolation test to formalize this failure, =
which I can do as a separate patch if we want to have this prior to =
implementing a fix.

Enclosed is the (currently failing) isolation test for the expected =
behavior.


--Apple-Mail=_C27606DC-B4E7-4A58-A5FB-01213B576E49
Content-Disposition: attachment;
	filename=0001-Add-isolation-test-for-FETCH-FIRST-ROW-WITH-TIES-FOR.patch
Content-Type: application/octet-stream;
	x-unix-mode=0644;
	name="0001-Add-isolation-test-for-FETCH-FIRST-ROW-WITH-TIES-FOR.patch"
Content-Transfer-Encoding: quoted-printable

=46rom=20ce874d9dd8afee20e5bd547b9cc17d1803168aa0=20Mon=20Sep=2017=20=
00:00:00=202001=0AFrom:=20David=20Christensen=20<david@endpoint.com>=0A=
Date:=20Tue,=2020=20Oct=202020=2010:17:58=20-0500=0ASubject:=20[PATCH]=20=
Add=20isolation=20test=20for=20FETCH=20FIRST=20ROW=20WITH=20TIES=20FOR=20=
UPDATE=0A=20SKIP=20LOCKED=0A=0A---=0A=20=
src/test/isolation/expected/skip-locked-5.out=20|=2014=20+++++++++=0A=20=
src/test/isolation/isolation_schedule=20=20=20=20=20=20=20=20=20|=20=201=20=
+=0A=20src/test/isolation/specs/skip-locked-5.spec=20=20=20|=2029=20=
+++++++++++++++++++=0A=203=20files=20changed,=2044=20insertions(+)=0A=20=
create=20mode=20100644=20src/test/isolation/expected/skip-locked-5.out=0A=
=20create=20mode=20100644=20src/test/isolation/specs/skip-locked-5.spec=0A=
=0Adiff=20--git=20a/src/test/isolation/expected/skip-locked-5.out=20=
b/src/test/isolation/expected/skip-locked-5.out=0Anew=20file=20mode=20=
100644=0Aindex=200000000000..87fc2d8aa1=0A---=20/dev/null=0A+++=20=
b/src/test/isolation/expected/skip-locked-5.out=0A@@=20-0,0=20+1,14=20@@=0A=
+Parsed=20test=20spec=20with=202=20sessions=0A+=0A+starting=20=
permutation:=20s1a=20s2a=20s1b=20s2b=0A+step=20s1a:=20SELECT=20*=20FROM=20=
foo=20ORDER=20BY=20batch=20FETCH=20FIRST=20ROW=20WITH=20TIES=20FOR=20=
UPDATE=20SKIP=20LOCKED;=0A+id=20=20=20=20=20=20=20=20=20=20=20=20=20=
batch=20=20=20=20=20=20=20=20=20=20=0A+=0A+1=20=20=20=20=20=20=20=20=20=20=
=20=20=20=201=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A+2=20=20=20=20=20=
=20=20=20=20=20=20=20=20=201=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A=
+step=20s2a:=20SELECT=20*=20FROM=20foo=20ORDER=20BY=20batch=20FETCH=20=
FIRST=20ROW=20WITH=20TIES=20FOR=20UPDATE=20SKIP=20LOCKED;=0A+id=20=20=20=20=
=20=20=20=20=20=20=20=20=20batch=20=20=20=20=20=20=20=20=20=20=0A+=0A+3=20=
=20=20=20=20=20=20=20=20=20=20=20=20=202=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=0A+step=20s1b:=20COMMIT;=0A+step=20s2b:=20COMMIT;=0Adiff=20=
--git=20a/src/test/isolation/isolation_schedule=20=
b/src/test/isolation/isolation_schedule=0Aindex=20aa386ab1a2..9e32b00149=20=
100644=0A---=20a/src/test/isolation/isolation_schedule=0A+++=20=
b/src/test/isolation/isolation_schedule=0A@@=20-64,6=20+64,7=20@@=20=
test:=20skip-locked=0A=20test:=20skip-locked-2=0A=20test:=20=
skip-locked-3=0A=20test:=20skip-locked-4=0A+test:=20skip-locked-5=0A=20=
test:=20drop-index-concurrently-1=0A=20test:=20multiple-cic=0A=20test:=20=
alter-table-1=0Adiff=20--git=20=
a/src/test/isolation/specs/skip-locked-5.spec=20=
b/src/test/isolation/specs/skip-locked-5.spec=0Anew=20file=20mode=20=
100644=0Aindex=200000000000..83bb3de00f=0A---=20/dev/null=0A+++=20=
b/src/test/isolation/specs/skip-locked-5.spec=0A@@=20-0,0=20+1,29=20@@=0A=
+#=20Test=20SKIP=20LOCKED=20with=20FETCH=20FIRST=20ROW=20WITH=20TIES=0A+=0A=
+setup=0A+{=0A+=20=20CREATE=20TABLE=20foo=20(=0A+=09id=20int=20PRIMARY=20=
KEY,=0A+=09batch=20int=20NOT=20NULL=0A+=20=20);=0A+=20=20INSERT=20INTO=20=
foo=20VALUES=20(1,=201),=20(2,=201),=20(3,=202),=20(4,=203);=0A+}=0A+=0A=
+teardown=0A+{=0A+=20=20DROP=20TABLE=20foo;=0A+}=0A+=0A+session=20"s1"=0A=
+setup=09=09{=20BEGIN;=20}=0A+step=20"s1a"=20=20{=20SELECT=20*=20FROM=20=
foo=20ORDER=20BY=20batch=20FETCH=20FIRST=20ROW=20WITH=20TIES=20FOR=20=
UPDATE=20SKIP=20LOCKED;=20}=0A+step=20"s1b"=09{=20COMMIT;=20}=0A+=0A=
+session=20"s2"=0A+setup=09=09{=20BEGIN;=20}=0A+step=20"s2a"=20=20{=20=
SELECT=20*=20FROM=20foo=20ORDER=20BY=20batch=20FETCH=20FIRST=20ROW=20=
WITH=20TIES=20FOR=20UPDATE=20SKIP=20LOCKED;=20}=0A+step=20"s2b"=09{=20=
COMMIT;=20}=0A+=0A+#=20s1=20returns=20the=20two=20rows=20associated=20=
with=20batch=201=20and=20takes=20the=20lock;=20s2=20should=20skip=20=
those=20two=20rows=20due=20to=20being=20locked=20and=20should=20return=20=
the=20batch=202=20row.=0A+permutation=20"s1a"=20"s2a"=20"s1b"=20"s2b"=0A=
+=0A--=20=0A2.24.3=20(Apple=20Git-128)=0A=0A=

--Apple-Mail=_C27606DC-B4E7-4A58-A5FB-01213B576E49
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
	charset=us-ascii


--
David Christensen
Senior Software and Database Engineer
End Point Corporation
david@endpoint.com
785-727-1171


--Apple-Mail=_C27606DC-B4E7-4A58-A5FB-01213B576E49--

--Apple-Mail=_EA24B22B-41AC-48DD-B181-D4252F4F6E0F
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+PDcwACgkQN0ojyd+b
ZbgK8g/+MpmmLemZMkEMlbmWfPYv6Mu8HNQZYRw+Vo5x3nQCkbB1PpGASyl1Gb1a
AGRIKtfp6k9bajbpz2gYguzQ47ne1MFhQVAFlBVOLfUfRw+JaR1FwKxfs3A8N2S2
IMco7rV1pcOZOA4+pZuriKRsyaBjs4iHZJY+GVNcoGtjBaMjjgVc4xxpAasIg/gT
jxTSKYqAb9LkyVJHnc4KZxAxDeQmM9ioBrX/u+8kUZwjvnOBoe0WEw+K+sg+Rcc+
/Pi/ObzLZM6EXOt6FPfcrQZ0Ysvn/JpX0Oix52Aols/Kyj3k5brOTeJVO9//OdCz
FWi8xw0Lp8OgBXR5g8AwZarSkbB2ldL4RDkAozeF53Kvk3CIcBVzNbg0JOJGhmtA
Gqg9dXWoemW6JunefA8//E1PT5AHF6F/+b4fzYC/bbK0BNqeaeGeN3YBcRIKRwuh
CHWaAafaVfUErh+rVtOfltbQeZndMGa6zk5+tZn92sfEDGdBt/JC8qZdKxC02ubD
8KR3zdSBwnOrvWKHun5a+i0mVDfk2AISyFYkfZk7cv1J6lfDvHHgzkug6eUIeHsN
5xxZzeNWT4vutz5/0kRuQBgISFsKnZ37lpt2y7QSg69+t6N6BK6sG6abBK+jSDcv
Tpu80A4pBx2LknSSYCPJ5QRFXLtmLul9gDUcdi+jljhtHF21Yd0=
=Cahl
-----END PGP SIGNATURE-----

--Apple-Mail=_EA24B22B-41AC-48DD-B181-D4252F4F6E0F--