Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wTzcx-000sfD-3A for pgsql-bugs@arkaria.postgresql.org; Mon, 01 Jun 2026 10:12:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wTzcw-009lIs-2L for pgsql-bugs@arkaria.postgresql.org; Mon, 01 Jun 2026 10:12:10 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wTzcw-009lIk-0G for pgsql-bugs@lists.postgresql.org; Mon, 01 Jun 2026 10:12:10 +0000 Received: from forwardcorp1a.mail.yandex.net ([2a02:6b8:c0e:500:1:45:d181:df01]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1wTzcs-00000000YlI-2nN8 for pgsql-bugs@lists.postgresql.org; Mon, 01 Jun 2026 10:12:09 +0000 Received: from mail-nwsmtp-smtp-corp-main-83.vla.yp-c.yandex.net (mail-nwsmtp-smtp-corp-main-83.vla.yp-c.yandex.net [IPv6:2a02:6b8:c2d:3530:0:640:eca4:0]) by forwardcorp1a.mail.yandex.net (Yandex) with ESMTPS id 3E913C0239; Mon, 01 Jun 2026 13:11:58 +0300 (MSK) Received: from smtpclient.apple (unknown [2a02:6bf:8080:c63::1:2a]) by mail-nwsmtp-smtp-corp-main-83.vla.yp-c.yandex.net (smtpcorp) with ESMTPSA id vBbau32a9Os0-imDBoGX0; Mon, 01 Jun 2026 13:11:57 +0300 X-Yandex-Fwd: 1 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yandex-team.ru; s=default; t=1780308717; bh=810ogOV9STrwMOllJF6BAvCpwSGH1fduwzkB5r/nVY4=; h=References:To:Cc:In-Reply-To:Date:From:Message-Id:Subject; b=qjhuMEKo0NpVaQOW448Kx/SRrF6V5/qL2o5UkT73sY8ARDnzmbkWfSUiXDhfOr4Z3 Kq5sMWR5YuPEZbOYXEAFIn2vOoK3TiO3JrqZx7St0Ho/C+vHb7O7AbgjtKWGsH67pL 0ypNmFZD2UgQFkpwh5uPqIbpcDrsLwLzEjCxIlBE= Authentication-Results: mail-nwsmtp-smtp-corp-main-83.vla.yp-c.yandex.net; dkim=pass header.i=@yandex-team.ru From: Andrey Borodin Message-Id: <787936C5-4155-4CF9-939D-39DC0EC1C892@yandex-team.ru> Content-Type: multipart/mixed; boundary="Apple-Mail=_8A3668DC-7AF1-499A-B70E-E8F1305B8EC0" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.600.51.1.1\)) Subject: Re: Possible G2-item at SERIALIZABLE Date: Mon, 1 Jun 2026 15:11:46 +0500 In-Reply-To: <802C6ECE-8D49-4E1A-83CB-A45C8E4EBFAD@yandex-team.ru> Cc: PostgreSQL mailing lists To: Kyle Kingsbury References: <165342c0-0c75-461e-b334-b997639ad48d@aphyr.com> <84AC98E5-3387-40A5-A258-C6E354349154@yandex-team.ru> <802C6ECE-8D49-4E1A-83CB-A45C8E4EBFAD@yandex-team.ru> X-Mailer: Apple Mail (2.3864.600.51.1.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_8A3668DC-7AF1-499A-B70E-E8F1305B8EC0 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On 1 Jun 2026, at 11:18, Andrey Borodin wrote: >=20 > There's a separate case Two more SSI false-negatives in the same area, found by Nik's machines and Mark-bot while triaging original report. INSERT ... ON CONFLICT reads the conflicting ("arbiter") row to decide what to do, but doesn't take an SIREAD predicate lock on it. So when the statement ultimately writes no tuple, that read leaves no trace for SSI and a concurrent modification of the same row can produce a non-serializable all-commit result: * ON CONFLICT DO UPDATE ... WHERE =E2=80=94 the no-op update = branch; the conflict row is observed but not updated. * ON CONFLICT DO NOTHING with a concurrent DELETE of the conflict row. Replacing the ON CONFLICT with a plain SELECT of the same row aborts correctly, which is what convinced me the schedules are genuinely non-serializable and the gap is in the ON CONFLICT probe path. Both come from the same place - check_exclusion_or_unique_constraint() = in execIndexing.c finds the arbiter tuple but never calls = PredicateLockTID(). Adding that lock there fixes both. There's also one more false negative, but it is in = -DTEST_SUMMARIZE_SERIAL and IMO worth working only when we deal with what we have in production cases. Best regards, Andrey Borodin. --Apple-Mail=_8A3668DC-7AF1-499A-B70E-E8F1305B8EC0 Content-Disposition: attachment; filename=0001-Add-isolation-tests-for-ON-CONFLICT-no-op-SSI-false-.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="0001-Add-isolation-tests-for-ON-CONFLICT-no-op-SSI-false-.patch" Content-Transfer-Encoding: quoted-printable =46rom=20fd43ff3f82d4edb5df6ca59e13aeeaa12c296da9=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20Andrey=20Borodin=20=0ADate:=20= Mon,=201=20Jun=202026=2012:31:34=20+0500=0ASubject:=20[PATCH=201/2]=20= Add=20isolation=20tests=20for=20ON=20CONFLICT=20no-op=20SSI=20false=0A=20= negatives=0A=0AINSERT=20...=20ON=20CONFLICT=20DO=20NOTHING,=20and=20DO=20= UPDATE=20with=20an=20unsatisfied=0AWHERE,=20read=20the=20conflicting=20= row=20but=20take=20no=20SIREAD=20lock,=20so=20a=20concurrent=0A= UPDATE/DELETE=20of=20it=20can=20escape=20write-skew=20detection=20under=20= SERIALIZABLE.=0AEach=20spec=20contrasts=20a=20plain-SELECT=20control=20= with=20the=20ON=20CONFLICT=20variant.=0A=0AThe=20expected=20output=20= encodes=20the=20serializable=20result=20(one=20transaction=0Acancelled),=20= so=20these=20tests=20fail=20until=20the=20accompanying=20fix=20is=20= applied.=0A---=0A=20.../serializable-onconflict-do-nothing.out=20=20=20=20= |=2068=20++++++++++++++++++=0A=20= .../serializable-onconflict-noop-update.out=20=20=20|=2069=20= +++++++++++++++++++=0A=20src/test/isolation/isolation_schedule=20=20=20=20= =20=20=20=20=20|=20=203=20+=0A=20= .../serializable-onconflict-do-nothing.spec=20=20=20|=2042=20+++++++++++=0A= =20.../serializable-onconflict-noop-update.spec=20=20|=2042=20= +++++++++++=0A=205=20files=20changed,=20224=20insertions(+)=0A=20create=20= mode=20100644=20= src/test/isolation/expected/serializable-onconflict-do-nothing.out=0A=20= create=20mode=20100644=20= src/test/isolation/expected/serializable-onconflict-noop-update.out=0A=20= create=20mode=20100644=20= src/test/isolation/specs/serializable-onconflict-do-nothing.spec=0A=20= create=20mode=20100644=20= src/test/isolation/specs/serializable-onconflict-noop-update.spec=0A=0A= diff=20--git=20= a/src/test/isolation/expected/serializable-onconflict-do-nothing.out=20= b/src/test/isolation/expected/serializable-onconflict-do-nothing.out=0A= new=20file=20mode=20100644=0Aindex=2000000000000..45ef582465d=0A---=20= /dev/null=0A+++=20= b/src/test/isolation/expected/serializable-onconflict-do-nothing.out=0A= @@=20-0,0=20+1,68=20@@=0A+Parsed=20test=20spec=20with=203=20sessions=0A+=0A= +starting=20permutation:=20reset=20s1read=20s2r2=20s1w2=20s2w1=20s1c=20= s2c=20ck=0A+step=20reset:=20DELETE=20FROM=20noc;=20INSERT=20INTO=20noc=20= VALUES=20(1,=200),=20(2,=200);=0A+step=20s1read:=20SELECT=20v=20FROM=20= noc=20WHERE=20k=20=3D=201;=0A+v=0A+-=0A+0=0A+(1=20row)=0A+=0A+step=20= s2r2:=20SELECT=20v=20FROM=20noc=20WHERE=20k=20=3D=202;=0A+v=0A+-=0A+0=0A= +(1=20row)=0A+=0A+step=20s1w2:=20UPDATE=20noc=20SET=20v=20=3D=201=20= WHERE=20k=20=3D=202;=0A+step=20s2w1:=20DELETE=20FROM=20noc=20WHERE=20k=20= =3D=201;=0A+step=20s1c:=20COMMIT;=0A+step=20s2c:=20COMMIT;=0A+ERROR:=20=20= could=20not=20serialize=20access=20due=20to=20read/write=20dependencies=20= among=20transactions=0A+step=20ck:=20SELECT=20k,=20v=20FROM=20noc=20= ORDER=20BY=20k;=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20SELECT=20CASE=20= WHEN=20NOT=20EXISTS=20(SELECT=201=20FROM=20noc=20WHERE=20k=20=3D=201)=0A= +=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20AND=20(SELECT=20v=20FROM=20noc=20WHERE=20k=20=3D=202)=20=3D=201=0A= +=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20THEN=20'bad_both_committed'=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20ELSE=20'ok_aborted_or_serial'=20END=20= AS=20invariant;=0A+k|v=0A+-+-=0A+1|0=0A+2|1=0A+(2=20rows)=0A+=0A= +invariant=20=20=20=20=20=20=20=20=20=20=20=0A+--------------------=0A= +ok_aborted_or_serial=0A+(1=20row)=0A+=0A+=0A+starting=20permutation:=20= reset=20s1noop=20s2r2=20s1w2=20s2w1=20s1c=20s2c=20ck=0A+step=20reset:=20= DELETE=20FROM=20noc;=20INSERT=20INTO=20noc=20VALUES=20(1,=200),=20(2,=20= 0);=0A+step=20s1noop:=20INSERT=20INTO=20noc(k,=20v)=20VALUES=20(1,=2099)=20= ON=20CONFLICT=20(k)=20DO=20NOTHING;=0A+step=20s2r2:=20SELECT=20v=20FROM=20= noc=20WHERE=20k=20=3D=202;=0A+v=0A+-=0A+0=0A+(1=20row)=0A+=0A+step=20= s1w2:=20UPDATE=20noc=20SET=20v=20=3D=201=20WHERE=20k=20=3D=202;=0A+step=20= s2w1:=20DELETE=20FROM=20noc=20WHERE=20k=20=3D=201;=0A+step=20s1c:=20= COMMIT;=0A+step=20s2c:=20COMMIT;=0A+ERROR:=20=20could=20not=20serialize=20= access=20due=20to=20read/write=20dependencies=20among=20transactions=0A= +step=20ck:=20SELECT=20k,=20v=20FROM=20noc=20ORDER=20BY=20k;=0A+=20=20=20= =20=20=20=20=20=20=20=20=20=20SELECT=20CASE=20WHEN=20NOT=20EXISTS=20= (SELECT=201=20FROM=20noc=20WHERE=20k=20=3D=201)=0A+=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20AND=20(SELECT=20v=20= FROM=20noc=20WHERE=20k=20=3D=202)=20=3D=201=0A+=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20THEN=20= 'bad_both_committed'=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20ELSE=20'ok_aborted_or_serial'=20END=20AS=20= invariant;=0A+k|v=0A+-+-=0A+1|0=0A+2|1=0A+(2=20rows)=0A+=0A+invariant=20=20= =20=20=20=20=20=20=20=20=20=0A+--------------------=0A= +ok_aborted_or_serial=0A+(1=20row)=0A+=0Adiff=20--git=20= a/src/test/isolation/expected/serializable-onconflict-noop-update.out=20= b/src/test/isolation/expected/serializable-onconflict-noop-update.out=0A= new=20file=20mode=20100644=0Aindex=2000000000000..af8ce068cc3=0A---=20= /dev/null=0A+++=20= b/src/test/isolation/expected/serializable-onconflict-noop-update.out=0A= @@=20-0,0=20+1,69=20@@=0A+Parsed=20test=20spec=20with=203=20sessions=0A+=0A= +starting=20permutation:=20reset=20s1read=20s2r2=20s1w2=20s2w1=20s1c=20= s2c=20ck=0A+step=20reset:=20DELETE=20FROM=20noc;=20INSERT=20INTO=20noc=20= VALUES=20(1,=200),=20(2,=200);=0A+step=20s1read:=20SELECT=20v=20FROM=20= noc=20WHERE=20k=20=3D=201;=0A+v=0A+-=0A+0=0A+(1=20row)=0A+=0A+step=20= s2r2:=20SELECT=20v=20FROM=20noc=20WHERE=20k=20=3D=202;=0A+v=0A+-=0A+0=0A= +(1=20row)=0A+=0A+step=20s1w2:=20UPDATE=20noc=20SET=20v=20=3D=201=20= WHERE=20k=20=3D=202;=0A+step=20s2w1:=20UPDATE=20noc=20SET=20v=20=3D=2042=20= WHERE=20k=20=3D=201;=0A+step=20s1c:=20COMMIT;=0A+step=20s2c:=20COMMIT;=0A= +ERROR:=20=20could=20not=20serialize=20access=20due=20to=20read/write=20= dependencies=20among=20transactions=0A+step=20ck:=20SELECT=20k,=20v=20= FROM=20noc=20ORDER=20BY=20k;=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20= SELECT=20CASE=20WHEN=20(SELECT=20v=20FROM=20noc=20WHERE=20k=20=3D=201)=20= =3D=2042=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20AND=20(SELECT=20v=20FROM=20noc=20WHERE=20k=20=3D=202)=20=3D= =201=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20THEN=20'bad_both_committed'=0A+=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20ELSE=20'ok_aborted_or_serial'=20= END=20AS=20invariant;=0A+k|v=0A+-+-=0A+1|0=0A+2|1=0A+(2=20rows)=0A+=0A= +invariant=20=20=20=20=20=20=20=20=20=20=20=0A+--------------------=0A= +ok_aborted_or_serial=0A+(1=20row)=0A+=0A+=0A+starting=20permutation:=20= reset=20s1noop=20s2r2=20s1w2=20s2w1=20s1c=20s2c=20ck=0A+step=20reset:=20= DELETE=20FROM=20noc;=20INSERT=20INTO=20noc=20VALUES=20(1,=200),=20(2,=20= 0);=0A+step=20s1noop:=20INSERT=20INTO=20noc(k,=20v)=20VALUES=20(1,=2099)=20= ON=20CONFLICT=20(k)=20DO=20UPDATE=20SET=20v=20=3D=20100=20WHERE=20noc.v=20= =3D=2042;=0A+step=20s2r2:=20SELECT=20v=20FROM=20noc=20WHERE=20k=20=3D=20= 2;=0A+v=0A+-=0A+0=0A+(1=20row)=0A+=0A+step=20s1w2:=20UPDATE=20noc=20SET=20= v=20=3D=201=20WHERE=20k=20=3D=202;=0A+step=20s2w1:=20UPDATE=20noc=20SET=20= v=20=3D=2042=20WHERE=20k=20=3D=201;=20=0A+step=20s1c:=20= COMMIT;=0A+step=20s2w1:=20<...=20completed>=0A+ERROR:=20=20could=20not=20= serialize=20access=20due=20to=20read/write=20dependencies=20among=20= transactions=0A+step=20s2c:=20COMMIT;=0A+step=20ck:=20SELECT=20k,=20v=20= FROM=20noc=20ORDER=20BY=20k;=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20= SELECT=20CASE=20WHEN=20(SELECT=20v=20FROM=20noc=20WHERE=20k=20=3D=201)=20= =3D=2042=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20AND=20(SELECT=20v=20FROM=20noc=20WHERE=20k=20=3D=202)=20=3D= =201=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20THEN=20'bad_both_committed'=0A+=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20ELSE=20'ok_aborted_or_serial'=20= END=20AS=20invariant;=0A+k|v=0A+-+-=0A+1|0=0A+2|1=0A+(2=20rows)=0A+=0A= +invariant=20=20=20=20=20=20=20=20=20=20=20=0A+--------------------=0A= +ok_aborted_or_serial=0A+(1=20row)=0A+=0Adiff=20--git=20= a/src/test/isolation/isolation_schedule=20= b/src/test/isolation/isolation_schedule=0Aindex=20= 15c33fad4c5..313e8abba6f=20100644=0A---=20= a/src/test/isolation/isolation_schedule=0A+++=20= b/src/test/isolation/isolation_schedule=0A@@=20-127,3=20+127,6=20@@=20= test:=20matview-write-skew=0A=20test:=20lock-nowait=0A=20test:=20= for-portion-of=0A=20test:=20ddl-dependency-locking=0A+=0A+test:=20= serializable-onconflict-noop-update=0A+test:=20= serializable-onconflict-do-nothing=0Adiff=20--git=20= a/src/test/isolation/specs/serializable-onconflict-do-nothing.spec=20= b/src/test/isolation/specs/serializable-onconflict-do-nothing.spec=0Anew=20= file=20mode=20100644=0Aindex=2000000000000..da8cd10d3f5=0A---=20= /dev/null=0A+++=20= b/src/test/isolation/specs/serializable-onconflict-do-nothing.spec=0A@@=20= -0,0=20+1,42=20@@=0A+#=20Write-skew=20via=20INSERT=20...=20ON=20CONFLICT=20= DO=20NOTHING=20with=20a=20concurrent=20delete=20of=0A+#=20the=20= conflicting=20row=20under=20SERIALIZABLE.=0A+#=0A+#=20=20=20s1=20= examines=20k=3D1=20through=20the=20ON=20CONFLICT=20arbiter=20and=20= writes=20k=3D2=0A+#=20=20=20s2=20reads=20k=3D2=20and=20deletes=20k=3D1=0A= +#=0A+#=20This=20is=20a=20dangerous=20structure,=20so=20SSI=20must=20= cancel=20one=20transaction.=0A+#=0A+#=20The=20question=20this=20= exercises:=20the=20arbiter's=20conflict=20probe=20is=20a=20read=20that=0A= +#=20decides=20the=20statement's=20outcome=20--=20exactly=20like=20the=20= plain=20SELECT=20in=20the=0A+#=20control=20permutation=20--=20so=20it=20= must=20participate=20in=20SSI=20and=20take=20an=20SIREAD=20lock.=0A+#=20= Until=20that=20read=20was=20predicate-locked,=20only=20the=20control=20= was=20cancelled=20while=0A+#=20the=20ON=20CONFLICT=20DO=20NOTHING=20= variant=20committed=20a=20non-serializable=20result.=0A+=0A+setup=20=20=20= =20=20{=20CREATE=20TABLE=20noc=20(k=20int=20PRIMARY=20KEY,=20v=20int=20= NOT=20NULL);=20}=0A+teardown=20=20{=20DROP=20TABLE=20noc;=20}=0A+=0A= +session=20s1=0A+setup=20{=20BEGIN=20ISOLATION=20LEVEL=20SERIALIZABLE;=20= }=0A+step=20s1read=20{=20SELECT=20v=20FROM=20noc=20WHERE=20k=20=3D=201;=20= }=0A+step=20s1noop=20{=20INSERT=20INTO=20noc(k,=20v)=20VALUES=20(1,=20= 99)=20ON=20CONFLICT=20(k)=20DO=20NOTHING;=20}=0A+step=20s1w2=20=20=20{=20= UPDATE=20noc=20SET=20v=20=3D=201=20WHERE=20k=20=3D=202;=20}=0A+step=20= s1c=20=20=20=20{=20COMMIT;=20}=0A+=0A+session=20s2=0A+setup=20{=20BEGIN=20= ISOLATION=20LEVEL=20SERIALIZABLE;=20}=0A+step=20s2r2=20{=20SELECT=20v=20= FROM=20noc=20WHERE=20k=20=3D=202;=20}=0A+step=20s2w1=20{=20DELETE=20FROM=20= noc=20WHERE=20k=20=3D=201;=20}=0A+step=20s2c=20=20{=20COMMIT;=20}=0A+=0A= +#=20Non-transactional=20helper:=20resets=20the=20rows=20before=20each=20= permutation=20and=20reports=0A+#=20the=20final=20committed=20state=20= plus=20a=20serializability=20invariant.=0A+session=20ctl=0A+step=20reset=20= {=20DELETE=20FROM=20noc;=20INSERT=20INTO=20noc=20VALUES=20(1,=200),=20= (2,=200);=20}=0A+step=20ck=20=20=20=20{=20SELECT=20k,=20v=20FROM=20noc=20= ORDER=20BY=20k;=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20SELECT=20CASE=20= WHEN=20NOT=20EXISTS=20(SELECT=201=20FROM=20noc=20WHERE=20k=20=3D=201)=0A= +=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20AND=20(SELECT=20v=20FROM=20noc=20WHERE=20k=20=3D=202)=20=3D=201=0A= +=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20THEN=20'bad_both_committed'=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20ELSE=20'ok_aborted_or_serial'=20END=20= AS=20invariant;=20}=0A+=0A+permutation=20reset=20s1read=20s2r2=20s1w2=20= s2w1=20s1c=20s2c=20ck=0A+permutation=20reset=20s1noop=20s2r2=20s1w2=20= s2w1=20s1c=20s2c=20ck=0Adiff=20--git=20= a/src/test/isolation/specs/serializable-onconflict-noop-update.spec=20= b/src/test/isolation/specs/serializable-onconflict-noop-update.spec=0A= new=20file=20mode=20100644=0Aindex=2000000000000..44a52f4953c=0A---=20= /dev/null=0A+++=20= b/src/test/isolation/specs/serializable-onconflict-noop-update.spec=0A@@=20= -0,0=20+1,42=20@@=0A+#=20Write-skew=20via=20INSERT=20...=20ON=20CONFLICT=20= DO=20UPDATE=20with=20an=20unsatisfied=20WHERE=0A+#=20(a=20no-op=20= update)=20under=20SERIALIZABLE.=0A+#=0A+#=20=20=20s1=20examines=20k=3D1=20= through=20the=20ON=20CONFLICT=20arbiter=20and=20writes=20k=3D2=0A+#=20=20= =20s2=20reads=20k=3D2=20and=20writes=20k=3D1=0A+#=0A+#=20This=20is=20a=20= dangerous=20structure,=20so=20SSI=20must=20cancel=20one=20transaction.=0A= +#=0A+#=20The=20question=20this=20exercises:=20the=20arbiter's=20= conflict=20probe=20is=20a=20read=20that=0A+#=20decides=20the=20= statement's=20outcome=20--=20exactly=20like=20the=20plain=20SELECT=20in=20= the=0A+#=20control=20permutation=20--=20so=20it=20must=20participate=20= in=20SSI=20and=20take=20an=20SIREAD=20lock.=0A+#=20Until=20that=20read=20= was=20predicate-locked,=20only=20the=20control=20was=20cancelled=20while=0A= +#=20the=20ON=20CONFLICT=20no-op=20variant=20committed=20a=20= non-serializable=20result.=0A+=0A+setup=20=20=20=20=20{=20CREATE=20TABLE=20= noc=20(k=20int=20PRIMARY=20KEY,=20v=20int=20NOT=20NULL);=20}=0A+teardown=20= =20{=20DROP=20TABLE=20noc;=20}=0A+=0A+session=20s1=0A+setup=20{=20BEGIN=20= ISOLATION=20LEVEL=20SERIALIZABLE;=20}=0A+step=20s1read=20{=20SELECT=20v=20= FROM=20noc=20WHERE=20k=20=3D=201;=20}=0A+step=20s1noop=20{=20INSERT=20= INTO=20noc(k,=20v)=20VALUES=20(1,=2099)=20ON=20CONFLICT=20(k)=20DO=20= UPDATE=20SET=20v=20=3D=20100=20WHERE=20noc.v=20=3D=2042;=20}=0A+step=20= s1w2=20=20=20{=20UPDATE=20noc=20SET=20v=20=3D=201=20WHERE=20k=20=3D=202;=20= }=0A+step=20s1c=20=20=20=20{=20COMMIT;=20}=0A+=0A+session=20s2=0A+setup=20= {=20BEGIN=20ISOLATION=20LEVEL=20SERIALIZABLE;=20}=0A+step=20s2r2=20{=20= SELECT=20v=20FROM=20noc=20WHERE=20k=20=3D=202;=20}=0A+step=20s2w1=20{=20= UPDATE=20noc=20SET=20v=20=3D=2042=20WHERE=20k=20=3D=201;=20}=0A+step=20= s2c=20=20{=20COMMIT;=20}=0A+=0A+#=20Non-transactional=20helper:=20resets=20= the=20rows=20before=20each=20permutation=20and=20reports=0A+#=20the=20= final=20committed=20state=20plus=20a=20serializability=20invariant.=0A= +session=20ctl=0A+step=20reset=20{=20DELETE=20FROM=20noc;=20INSERT=20= INTO=20noc=20VALUES=20(1,=200),=20(2,=200);=20}=0A+step=20ck=20=20=20=20= {=20SELECT=20k,=20v=20FROM=20noc=20ORDER=20BY=20k;=0A+=20=20=20=20=20=20=20= =20=20=20=20=20=20SELECT=20CASE=20WHEN=20(SELECT=20v=20FROM=20noc=20= WHERE=20k=20=3D=201)=20=3D=2042=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20AND=20(SELECT=20v=20FROM=20noc=20= WHERE=20k=20=3D=202)=20=3D=201=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20THEN=20'bad_both_committed'=0A+=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= ELSE=20'ok_aborted_or_serial'=20END=20AS=20invariant;=20}=0A+=0A= +permutation=20reset=20s1read=20s2r2=20s1w2=20s2w1=20s1c=20s2c=20ck=0A= +permutation=20reset=20s1noop=20s2r2=20s1w2=20s2w1=20s1c=20s2c=20ck=0A--=20= =0A2.50.1=20(Apple=20Git-155)=0A=0A= --Apple-Mail=_8A3668DC-7AF1-499A-B70E-E8F1305B8EC0 Content-Disposition: attachment; filename=0002-Predicate-lock-the-conflicting-row-in-INSERT-.-ON-CO.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="0002-Predicate-lock-the-conflicting-row-in-INSERT-.-ON-CO.patch" Content-Transfer-Encoding: quoted-printable =46rom=2017c2e40d2c2bb8a65fa9e2ca69de8db8cc68ba93=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20Andrey=20Borodin=20=0ADate:=20= Mon,=201=20Jun=202026=2012:31:34=20+0500=0ASubject:=20[PATCH=202/2]=20= Predicate-lock=20the=20conflicting=20row=20in=20INSERT=20...=20ON=0A=20= CONFLICT=0A=0AUnder=20SERIALIZABLE=20the=20arbiter=20probe=20reads=20the=20= conflicting=20row=20to=20decide=0Athe=20statement's=20outcome=20but=20= took=20no=20SIREAD=20lock.=20=20When=20ON=20CONFLICT=20then=0Awrites=20= no=20tuple=20(DO=20NOTHING,=20or=20DO=20UPDATE=20with=20an=20unsatisfied=20= WHERE),=20a=0Aconcurrent=20UPDATE/DELETE=20of=20that=20row=20missed=20= the=20rw-antidependency=20and=20a=0Awrite=20skew=20could=20commit.=20=20= Lock=20the=20tuple=20where=20the=20arbiter=20finds=20it.=0A---=0A=20= src/backend/executor/execIndexing.c=20|=2032=20= +++++++++++++++++++++++++++++=0A=201=20file=20changed,=2032=20= insertions(+)=0A=0Adiff=20--git=20a/src/backend/executor/execIndexing.c=20= b/src/backend/executor/execIndexing.c=0Aindex=20eb383812901..d49e87a0eda=20= 100644=0A---=20a/src/backend/executor/execIndexing.c=0A+++=20= b/src/backend/executor/execIndexing.c=0A@@=20-108,12=20+108,14=20@@=0A=20= =0A=20#include=20"access/genam.h"=0A=20#include=20"access/relscan.h"=0A= +#include=20"access/sysattr.h"=0A=20#include=20"access/tableam.h"=0A=20= #include=20"access/xact.h"=0A=20#include=20"catalog/index.h"=0A=20= #include=20"executor/executor.h"=0A=20#include=20"nodes/nodeFuncs.h"=0A=20= #include=20"storage/lmgr.h"=0A+#include=20"storage/predicate.h"=0A=20= #include=20"utils/injection_point.h"=0A=20#include=20"utils/lsyscache.h"=0A= =20#include=20"utils/multirangetypes.h"=0A@@=20-906,9=20+908,39=20@@=20= retry:=0A=20=09=09=20*/=0A=20=09=09if=20(violationOK)=0A=20=09=09{=0A+=09= =09=09Datum=09=09xminDatum;=0A+=09=09=09bool=09=09xminIsnull;=0A+=09=09=09= TransactionId=20xmin;=0A+=0A=20=09=09=09conflict=20=3D=20true;=0A=20=09=09= =09if=20(conflictTid)=0A=20=09=09=09=09*conflictTid=20=3D=20= existing_slot->tts_tid;=0A+=0A+=09=09=09/*=0A+=09=09=09=20*=20This=20= conflicting=20row=20determined=20the=20outcome=20of=20the=20INSERT=20...=0A= +=09=09=09=20*=20ON=20CONFLICT,=20so=20for=20serializability=20it=20was=20= read=20just=20as=20a=20SELECT=0A+=09=09=09=20*=20of=20it=20would=20be.=20= =20Record=20an=20SIREAD=20lock=20so=20that=20a=20concurrent=0A+=09=09=09=20= *=20modification=20of=20the=20row=20creates=20the=20necessary=20= rw-antidependency,=0A+=09=09=09=20*=20even=20when=20ON=20CONFLICT=20= writes=20no=20tuple=20(DO=20NOTHING,=20or=20DO=20UPDATE=0A+=09=09=09=20*=20= with=20an=20unsatisfied=20WHERE).=20=20A=20no-op=20outside=20= SERIALIZABLE.=0A+=09=09=09=20*=0A+=09=09=09=20*=20XXX=20A=20reviewer=20= familiar=20with=20predicate.c=20should=20confirm=20the=0A+=09=09=09=20*=20= xmin=20handling=20here.=20=20slot_getsysattr()=20returns=20the=20raw=20= xmin=0A+=09=09=09=20*=20(HeapTupleHeaderGetRawXmin),=20while=20the=20= other=20PredicateLockTID()=0A+=09=09=09=20*=20call=20sites=20pass=20the=20= frozen-aware=20HeapTupleHeaderGetXmin().=20=20This=0A+=09=09=09=20*=20= value=20only=20feeds=20the=20"did=20this=20xact=20write=20the=20tuple"=20= early-out=0A+=09=09=09=20*=20in=20PredicateLockTID(),=20and=20a=20frozen=20= tuple=20can=20never=20belong=20to=20the=0A+=09=09=09=20*=20current=20= transaction,=20so=20the=20raw=20value=20should=20be=20equivalent=20here=0A= +=09=09=09=20*=20--=20but=20the=20inconsistency=20with=20the=20other=20= call=20sites=20is=20worth=20a=0A+=09=09=09=20*=20second=20look.=0A+=09=09= =09=20*/=0A+=09=09=09xminDatum=20=3D=20slot_getsysattr(existing_slot,=0A= +=09=09=09=09=09=09=09=09=09=09MinTransactionIdAttributeNumber,=0A+=09=09= =09=09=09=09=09=09=09=09&xminIsnull);=0A+=09=09=09Assert(!xminIsnull);=0A= +=09=09=09xmin=20=3D=20DatumGetTransactionId(xminDatum);=0A+=09=09=09= PredicateLockTID(heap,=20&existing_slot->tts_tid,=0A+=09=09=09=09=09=09=09= =20estate->es_snapshot,=20xmin);=0A=20=09=09=09break;=0A=20=09=09}=0A=20=0A= --=20=0A2.50.1=20(Apple=20Git-155)=0A=0A= --Apple-Mail=_8A3668DC-7AF1-499A-B70E-E8F1305B8EC0--