From: David Christensen <david@endpoint.com>
Message-Id: <05226762-2C0B-44DE-A584-D9FCCFF06B5F@endpoint.com>
Content-Type: multipart/signed;
	boundary="Apple-Mail=_182897DA-F6AF-473E-B06F-5DF3EB87054A";
	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 09:23:06 -0500
In-Reply-To: <604509.1603203416@sss.pgh.pa.us>
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>
Precedence: bulk


--Apple-Mail=_182897DA-F6AF-473E-B06F-5DF3EB87054A
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

> 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.

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).

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.

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


--Apple-Mail=_182897DA-F6AF-473E-B06F-5DF3EB87054A
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+O8soACgkQN0ojyd+b
Zbivxw/7BlKBx7qiGAmlcIMggCaaUqXU1fuaR0MfBGi4IjrSCw078hHP2rax/R2F
6qFmbG/fYcq4GvbssR3MPm5NoSKX/AWy4OB4712xYTuZhqnNTEYB8ZQPpzx8Z9Rh
U7s94/FqHm19ekUgR3GwAq4BhhWXjMjFqEJuzFo/mSly7fKjacVsJ7oSAp67aNIA
a5GkYwOyaQsMJPMSBgiWrqeNL/O/clnEn4NCPFITovp6jPVuVGTSbsEiJWIqh0ae
QORyDT7e4IxvvDe793KOzripKamSRGpV6BXb0TMzAfaK6RWgr6g0AZLUTNFFzSyA
14D5hC8O5AlTb4BtDxJFmPqR/ozeU0RBtwgbWs+BQ0EmDQwg08D8+6jWqYHdssD4
eys2g89KatQRsTKHqMuzeiVhdgCHWXDOh9CzNkdwIaKG3JT+btpyza71JpF12npd
kdyBgCM7zyMzYcLawhmyQDnfeIRExBA5mk6Y+1TTP1IGy3cwMCP2411WrUmo5n9H
xk0kwQILs8dYXZmtTyRhXCUwiNi5OcvpzZxhBUF6KUfFrUI0Kdql4dpmWejCs/1W
P70SFa3hNVNv63KlaPepEHm7kiUUXJ/09A7/psPBmv+BAKp3RPWTtLsRZIhghnsw
p1gTKvG1wYBiIoxlHt8ePoW9fMnoeoLoEW2+XAUYdI4aNWhb6lQ=
=weRY
-----END PGP SIGNATURE-----

--Apple-Mail=_182897DA-F6AF-473E-B06F-5DF3EB87054A--