Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i0Lgt-0001X7-EQ for pgsql-bugs@arkaria.postgresql.org; Wed, 21 Aug 2019 08:10:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1i0Lgr-0001Es-TR for pgsql-bugs@arkaria.postgresql.org; Wed, 21 Aug 2019 08:09:57 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i0IuE-0004MN-4x for pgsql-bugs@lists.postgresql.org; Wed, 21 Aug 2019 05:11:34 +0000 Received: from mout.web.de ([212.227.17.11]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i0Iu6-0005Nl-FB for pgsql-bugs@lists.postgresql.org; Wed, 21 Aug 2019 05:11:33 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=web.de; s=dbaedf251592; t=1566364279; bh=RmXw6rjAkyOK3meOqIsAv7a/r8oGlUCiVJ8XjWEYlgY=; h=X-UI-Sender-Class:Date:In-Reply-To:References:Subject:To:CC:From; b=qUVCirpA1XmLtBOGuOl6+1npJf0vN9U6E3CchSSG/jKmTd5oTq40oo67bdFEM7evi Zb1dunGaWnlGidsqGjirFfOnLKfw2IL0o7YDoltUNO1wdJ7toD/5rp+275BOaQFPKO zhE2S8bEf784VoC+2UdJKRnDTnyKsPoJXb1YSdGs= X-UI-Sender-Class: c548c8c5-30a9-4db5-a2e7-cb6cb037b8f9 Received: from [192.168.100.225] ([195.243.192.178]) by smtp.web.de (mrweb101 [213.165.67.124]) with ESMTPSA (Nemesis) id 0MF3nL-1i3Lxc0vQB-00GJJA; Wed, 21 Aug 2019 07:11:19 +0200 Date: Wed, 21 Aug 2019 07:11:16 +0200 User-Agent: K-9 Mail for Android In-Reply-To: References: <15967-ae7fd939200f4547@postgresql.org> <6F9C0D52-A45B-4D2E-A558-F15170301C0A@web.de> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----YKTKFCN6O4F5B3QSD7O3GY0KDTO9J2" Content-Transfer-Encoding: 7bit Subject: Re: BUG #15967: Sequence generation using NEXTVAL() fails on 64bit systems To: Merlin Moncure ,Christoph Ziegenberg CC: Jeff Janes ,pgsql-bugs From: Christoph Ziegenberg Message-ID: <1591F404-9363-4333-AC6C-0C85795DEC2F@web.de> X-Provags-ID: V03:K1:eV/41MH47UZc+w9bk9Ef3PuEGyg5E5AznTD2lqkJ/tzowHwnPqn 7qsrMS0XDpXmWNnmdkQUTPPxr0UELgNUkZke4K5+8h4WHE05WoLACww7cvXXeqax2hYePe/ yVIBzAOT6eaVBCgdS8NAByDUGDQpVIQyRonDKw42Qxd50qS9Z001f1RueZdxruN2dpAI1OL oPdv5uIciboIv6gi4FICQ== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:CzW20D7wFa0=:YbBERVkLmWRmzjd1mb84V6 hBi/xFZ2oXIVPGr6UTPAHcir/G6MXq6y/GO1Ja6FjGT7REnwFRBjtSNSPiS/K0SrwXQFVuuzf oWZCMS8bZQCjwE35WqWF5465ZE6Np0QDG03OAKCGWdIYRdLeWH1RG1Jdlb0ZBt5Ow7HuWRI9T X5GK+qTwPN6y4vRzuZVLoM90qKHRoKV4paie2yxiOUUtUDPSO9iHJojxISpJJodJzybQDDUyA UMc4PGdmMTsMYkeB9wihd86izxdMA7cA95VEyMBvgV07XY5pcFe5WEBdmaFYyBcISSgOKPczG novPaFtLzwwTjkoIdHkikr8hsAYxnZPv8f3cmpzuG4t4mWZxZfjzuhktEIIyCAyuL5/k5E2iQ 4W4X/qiw7OEjOxygfM0dDag/LMC33+ecjXvBCGUGGam7qLKe+MN7yYxxRufppm7WTHJue/6eq NKWHZ3+gttVEqAEjhtbqLWX1HtNGUwHY/AG2G6MCKrCjUdoGTu9L9SPMBfalZVD71GZNqXSfg T32LMe0DguGn+KEuAbgv9dhD2pL/AKe4D6IdiydgF47FmBPAPU7MK42Lc+v/u5ZI92DQV2iAs p/0T8AAg780HcBdc3RUwagwA3OZ9V1TdnwRe/LR1J245Xx5AebR9w/kuAvCbuE+K7BAkq7drK OAAbJekbqNWrGP1LVC5L3fDsqJscf5urbH+G7DJFb9Banf+jopBFHCJAcja8Kx4YN/RnWSUAG uxfoLOaLasUKnj+OS6/BLEUHj6obRo/3PUwf4W91PvXclePddhA1hLvVkE2csiTvOhsxPUo9f TkWk2bfbVhiB6Bh+sRYqLyBoK0Lseifdy5DZPZ6iyAP/rN45HCEpY46S161LosjX9YPSmjn3F Jin1rpACKUstPlsq7qCTTMmyVhDHdwp2xwrroMsCCI2hVPwDLOq1ONNF11REPUL/aLQ0iEYwV HBCe4wOKrz85yAISw4OcavnBHC4z58YLAnUvMY4YCDl/jTZyMPkUCzD8F1zZ2bKm5iZzKL2lZ 6iGMRSOJR5RDCgAVRlzh3CGbQfZktar8S7n/jKud3eWA9XkaSaUSsr0+iWBVPEnQqsXXCZm+p vdBNp/GouwcTwdjmyXH685Dlxt4TITdBTdjleQnrLwAb+ExJuXW4TpFbOtX4g9SD2ZJwnI6pB +rhuTV/2xq/gK50nugQX82bYAYYCgYClQD34donPRozi0LfA== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------YKTKFCN6O4F5B3QSD7O3GY0KDTO9J2 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Good idea with the replacement of the nextval function=2E This confirms, th= at the problem is not an application or PHP problem=2E Here an extract from the logs, showing the error: --- 2019-08-21 06:57:38=2E812 CEST [5824] WARNUNG:=C2=A0 Got value 2799 from p= ublic=2Ee2_ws_log_login_seq 2019-08-21 06:57:38=2E812 CEST [5824] ZUSAMMENHANG:=C2=A0 PL/pgSQL-Funktio= n log_nextval(text) Zeile 6 bei RAISE 2019-08-21 06:57:38=2E872 CEST [16236] WARNUNG:=C2=A0 Got value 2800 from = public=2Ee2_ws_log_login_seq 2019-08-21 06:57:38=2E872 CEST [16236] ZUSAMMENHANG:=C2=A0 PL/pgSQL-Funkti= on log_nextval(text) Zeile 6 bei RAISE 2019-08-21 06:57:38=2E873 CEST [1436] WARNUNG:=C2=A0 Got value 2801 from p= ublic=2Ee2_ws_log_login_seq 2019-08-21 06:57:38=2E873 CEST [1436] ZUSAMMENHANG:=C2=A0 PL/pgSQL-Funktio= n log_nextval(text) Zeile 6 bei RAISE 2019-08-21 06:57:38=2E910 CEST [12480] WARNUNG:=C2=A0 Got value 2801 from = public=2Ee2_ws_log_login_seq 2019-08-21 06:57:38=2E910 CEST [12480] ZUSAMMENHANG:=C2=A0 PL/pgSQL-Funkti= on log_nextval(text) Zeile 6 bei RAISE 2019-08-21 06:57:38=2E915 CEST [12480] FEHLER:=C2=A0 doppelter Schl=C3=83= =C2=BCsselwert verletzt Unique-Constraint =C3=82=C2=BBp_e2_ws_log_login=C3= =82=C2=AB 2019-08-21 06:57:38=2E915 CEST [12480] DETAIL:=C2=A0 Schl=C3=83=C2=BCssel = =C3=82=C2=BB(log_login_id)=3D(2801)=C3=82=C2=AB existiert bereits=2E 2019-08-21 06:57:38=2E915 CEST [12480] ANWEISUNG:=C2=A0 INSERT INTO e2_ws_= log_login (log_login_id, client_id, log_action, log_datetime, log_ip_addres= s, log_data) VALUES ($1, $2, $3, $4, $5, $6) 2019-08-21 06:57:39=2E031 CEST [10672] WARNUNG:=C2=A0 Got value 2802 from = public=2Ee2_ws_log_login_seq 2019-08-21 06:57:39=2E031 CEST [10672] ZUSAMMENHANG:=C2=A0 PL/pgSQL-Funkti= on log_nextval(text) Zeile 6 bei RAISE --- BTW: I had to change the function a little bit: CREATE OR REPLACE FUNCTION log_nextval(_Sequence TEXT) RETURNS BIGINT AS $$ DECLARE =C2=A0=C2=A0=C2=A0 s BIGINT; BEGIN =C2=A0 s :=3D nextval(_Sequence); =C2=A0 RAISE WARNING 'Got value % from %', s, _Sequence; =C2=A0 RETURN s; END; $$ LANGUAGE 'plpgsql'; Christoph Am 20=2E August 2019 18:06:42 MESZ schrieb Merlin Moncure : >On Tue, Aug 20, 2019 at 9:56 AM Christoph Ziegenberg > wrote: >> New info: >> We could also reproduce it with Postgres 10=2E10 32bit on Windows >Server 2016 and 9=2E6=2E8 64bit on Ubuntu (18=2Ex)=2E So also the 32bit v= ersion >is affected, as well as 9=2Ex versions=2E > >Ok, that makes sense; if we don't have arch specific reproduction I'd >say that lowers the likelihood of having a serious problem within >postgres itself (just a hunch)=2E > >> Current test state: >> We (2 of my colleagues and me) are currently trying to create an >independent test, but at the moment it only occurrs within the specific >application=2E > >This of course is key=2E Most people reading this list are going to be >suspicious of your application rather than postgres internals causing >the issue=2E Having said that, let's see if we can isolate the problem= =2E > >> Of course we search for an error in the application first, checked >the connection settings, tracked all statements=2E=2E=2E there is nothing >special, only a handful of SELECTS and UPDATES, then the sequence >generation and the (sometimes failing) INSERT, and another INSERT (no >sequence generate here) and UPDATE following=2E In all cases we tested >with the Postgres default configuration=2E > >> We could see that wrapping the selection of NEXTVAL() in a >transaction (which shouldn't have any effect?) reduced the problem in >one case, but it was still possible to reproduce the error with an >increased number of requests=2E > >Being in a transaction makes shouldn't make any difference with >regards to behavior=2E > >> At the moment we don't have a real idea how to proceed=2E Next step >planned is to simulate all of the aforementioned requests done by the >application, because they seem to influence the behavior=2E > >Isolating in a separate test would be proof=2E Sans that, we need to be >suspicious of your test environment=2E Hm, how about this: perhaps we >can tease the problem out with logging=2E One possible trick is to do >this: >CREATE OR REPLACE FUNCTION log_nextval(_Sequence TEXT, s OUT) RETURNS >BIGINT AS >$$ >BEGIN > s :=3D nextval(_Sequence); > RAISE WARNING 'Got value % from %', s, _Sequence; > RETURN s; >END; >$$ LANGUAGE PLGSQL; > >Then, as an experiment, you can move all your nextval() generation to >this function (note: there will be significant performance hit) and >attempt to reproduce the problem in your application=2E If when you >did, we could then interrogate the database log to see if the same >value >1 times, this would be a smoking gun=2E If you did not see the >value returned >1 times, maybe turn on full statement logging (also a >big performance hit) and see where your application might be jacking >things up=2E > >> Background: >> The application is written in PHP (different versions used in the >tests, 7=2E2 - 7=2E4 I guess), runs on Apache/IIS (FastCGI) and uses the >PDO extension for the communication with Postgres=2E > >merlin ------YKTKFCN6O4F5B3QSD7O3GY0KDTO9J2 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Good idea with the replacement of the nextval func= tion=2E This confirms, that the problem is not an application or PHP proble= m=2E

Here an extract from the logs, showing the error:
2019-08-21= 06:57:38=2E812 CEST [5824] WARNUNG:=C2=A0 Got value 2799 from public=2Ee2_= ws_log_login_seq
2019-08-21 06:57:38=2E812 CEST [5824] ZUSAMMENHANG:=C2= =A0 PL/pgSQL-Funktion log_nextval(text) Zeile 6 bei RAISE
2019-08-21 06:= 57:38=2E872 CEST [16236] WARNUNG:=C2=A0 Got value 2800 from public=2Ee2_ws_= log_login_seq
2019-08-21 06:57:38=2E872 CEST [16236] ZUSAMMENHANG:=C2=A0= PL/pgSQL-Funktion log_nextval(text) Zeile 6 bei RAISE
2019-08-21 06:57:= 38=2E873 CEST [1436] WARNUNG:=C2=A0 Got value 2801 from public=2Ee2_ws_log_= login_seq
2019-08-21 06:57:38=2E873 CEST [1436] ZUSAMMENHANG:=C2=A0 PL/p= gSQL-Funktion log_nextval(text) Zeile 6 bei RAISE
2019-08-21 06:57:38=2E= 910 CEST [12480] WARNUNG:=C2=A0 Got value 2801 from public=2Ee2_ws_log_logi= n_seq
2019-08-21 06:57:38=2E910 CEST [12480] ZUSAMMENHANG:=C2=A0 PL/pgSQ= L-Funktion log_nextval(text) Zeile 6 bei RAISE
2019-08-21 06:57:38=2E915= CEST [12480] FEHLER:=C2=A0 doppelter Schl=C3=83=C2=BCsselwert verletzt Uni= que-Constraint =C3=82=C2=BBp_e2_ws_log_login=C3=82=C2=AB
2019-08-21 06:5= 7:38=2E915 CEST [12480] DETAIL:=C2=A0 Schl=C3=83=C2=BCssel =C3=82=C2=BB(log= _login_id)=3D(2801)=C3=82=C2=AB existiert bereits=2E
2019-08-21 06:57:38= =2E915 CEST [12480] ANWEISUNG:=C2=A0 INSERT INTO e2_ws_log_login (log_login= _id, client_id, log_action, log_datetime, log_ip_address, log_data) VALUES = ($1, $2, $3, $4, $5, $6)
2019-08-21 06:57:39=2E031 CEST [10672] WARNUNG:= =C2=A0 Got value 2802 from public=2Ee2_ws_log_login_seq
2019-08-21 06:57= :39=2E031 CEST [10672] ZUSAMMENHANG:=C2=A0 PL/pgSQL-Funktion log_nextval(te= xt) Zeile 6 bei RAISE
BTW: I had to change the function a little bit:
CREATE OR REPLACE FUNCTION log_nextval(_Sequence TEXT) RETURNS BIGINT = AS
$$
DECLARE
=C2=A0=C2=A0=C2=A0 s BIGINT;
BEGIN
=C2=A0 s := =3D nextval(_Sequence);
=C2=A0 RAISE WARNING 'Got value % from %', s, _S= equence;
=C2=A0 RETURN s;
END;
$$ LANGUAGE 'plpgsql';


C= hristoph


Am 20=2E August 2019 18:06:4= 2 MESZ schrieb Merlin Moncure <mmoncure@gmail=2Ecom>:
On Tue, Aug 20, 2019 at 9:56 AM Christoph Ziegenberg=
 <ziegenberg@web=2Ede> wrote:
New info:
We could also reproduce it with Postgres 10=2E10 = 32bit on Windows Server 2016 and 9=2E6=2E8 64bit on Ubuntu (18=2Ex)=2E So a= lso the 32bit version is affected, as well as 9=2Ex versions=2E

Ok, that makes sense; if we don't have arch specific reproduction = I'd
say that lowers the likelihood of having a serious problem withinpostgres itself (just a hunch)=2E

Current test state:
We (2 of my colleagues and me) are c= urrently trying to create an independent test, but at the moment it only oc= currs within the specific application=2E

This of course= is key=2E Most people reading this list are going to be
suspicious of = your application rather than postgres internals causing
the issue=2E Ha= ving said that, let's see if we can isolate the problem=2E

Of course we search for an error in= the application first, checked the connection settings, tracked all statem= ents=2E=2E=2E there is nothing special, only a handful of SELECTS and UPDAT= ES, then the sequence generation and the (sometimes failing) INSERT, and an= other INSERT (no sequence generate here) and UPDATE following=2E In all cas= es we tested with the Postgres default configuration=2E
We could see that wrappin= g the selection of NEXTVAL() in a transaction (which shouldn't have any eff= ect?) reduced the problem in one case, but it was still possible to reprodu= ce the error with an increased number of requests=2E

Be= ing in a transaction makes shouldn't make any difference with
regards to= behavior=2E

At the= moment we don't have a real idea how to proceed=2E Next step planned is to= simulate all of the aforementioned requests done by the application, becau= se they seem to influence the behavior=2E

Isolating in = a separate test would be proof=2E Sans that, we need to be
suspicious o= f your test environment=2E Hm, how about this: perhaps we
can tease the= problem out with logging=2E One possible trick is to do
this:
CREATE= OR REPLACE FUNCTION log_nextval(_Sequence TEXT, s OUT) RETURNS BIGINT AS$$
BEGIN
s :=3D nextval(_Sequence);
RAISE WARNING 'Got value= % from %', s, _Sequence;
RETURN s;
END;
$$ LANGUAGE PLGSQL;
=
Then, as an experiment, you can move all your nextval() generation tothis function (note: there will be significant performance hit) and
a= ttempt to reproduce the problem in your application=2E If when you
did,= we could then interrogate the database log to see if the same
value >= ;1 times, this would be a smoking gun=2E If you did not see the
value r= eturned >1 times, maybe turn on full statement logging (also a
big pe= rformance hit) and see where your application might be jacking
things u= p=2E

Background:The application is written in PHP (different versions used in the tests, 7= =2E2 - 7=2E4 I guess), runs on Apache/IIS (FastCGI) and uses the PDO extens= ion for the communication with Postgres=2E

merlin
------YKTKFCN6O4F5B3QSD7O3GY0KDTO9J2--