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 1j3iyI-0007Cz-Si for pgsql-bugs@arkaria.postgresql.org; Mon, 17 Feb 2020 16:10:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j3iyG-0003bH-Jl for pgsql-bugs@arkaria.postgresql.org; Mon, 17 Feb 2020 16:10:08 +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 1j3iyF-0003b6-Pj for pgsql-bugs@lists.postgresql.org; Mon, 17 Feb 2020 16:10:08 +0000 Received: from sonic308-20.consmr.mail.sg3.yahoo.com ([106.10.241.210]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1j3iyB-0000UV-Sg for pgsql-bugs@lists.postgresql.org; Mon, 17 Feb 2020 16:10:06 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com.hk; s=s2048; t=1581955796; bh=j01i0gm6sQe1urrpuboy6ZqRzAs83P5rXz5unG6XYXM=; h=Date:From:To:Subject:References:From:Subject; b=iVDylOeY/sN8MxOSs0lZFdz/9Es9rAV9vSXhMG4eY9GqUKzix61vh7rxm68QYdkvI/1axMyxTmaX4F1D7gVzxDTsttImHRwcjMvxgUyh1DfjOxWZB6x5EffcJpP9tKlF/bPnjnvCJl3xRs/fRrjJUbZA+kMxtKgM9YOHHPRsbVy0xiBvmfoE4qbK27jQTw9a6mo9zqIfbfrm2nqVDtihOlK41dbeC6+EttL5u/cvV3Wv8UeZLIMHIeDa2SgSl24LqyZzVBxaOW53m4to1dRpYBC8ZKlyF+IkjNCOyq6j86vkR+4RzNzQwHxs7guhHPehj0ATFAi9+Eq7eVs846Qafg== X-YMail-OSG: STS3ezsVM1l93mb8boLQzPrfkJWyd1o2pv8Bj4remb7nqTQmWEAf2a4nBKE1qmg h5WKeD0Vleixzw8_GxqrITEbRZZQzKi2EZjakHd4or6n8qMOYG2J9eb9xyot97mAqCn5BU5zcq4q usi_HSJWP9EE9qNxHr_xxm92ZNJP3RpiR7J2iPHlRLm2otmiyNBusk47urTecjIRrtZyAimbrA8u vWHDd3l4mDwNNhzaKWKyfbuVu3MyVSTIbCayb9L8GfNmb2Y5tdkgNcallZvoWr.jkO34b2QFNPxS HAYAt7_6sg7OIa4GhvwWzSAtMpYIKf0VGWAOqQvWxanlwrvjl83zExovbKVY5VBz.ptZXvpJEt9W IQvODZE4D06oD6CpcqixtMIeSwFM287qtusRzaXX9YnPWttCb5j8SpW51Fa0yl5QHYaJB0Fi2pvF 8faq7bH1l6uLC0o37wCoESlf_a5dZfsSxiyPs5ZEE2Z6EAjH3sIfDTIB2pr1v01KQtjZvsg3JCNi qa1OYaIn3MEn0m4sjVZh09I4Cxq9HfQUZdWi6cfOSmPLSQCBl98yyZ8u_ZUuYSunpvPO1Xajt5DC v15CKSkSnidfB8x.1M9KAaM_NnsPVMewoebr9ZphfTByqx5QVehUdyNXVAALD_CLVN_rD4No4auI raz6hEY9GlsHAXQZPaJ3ntUVGQZihfhj8TcuiaIckdfivgdlKWmlEWjkX7FChvVf30jHdr3Rubj6 PsS7UZhzuK1T1B0DeYLucwAsqYMsrupKeVEvSyJM096pY0U5HuOsTP1A6KOjuHQenCOVKBFJT2zH 7e.3.UGITrGnYHcQq3TGBKKaMV94W.ZRvjqsZB0DIRXHjMRdB4ISQYpzolVA2oNizf0ofGfxTN51 0QkO_IR0KSAym2rG4sAGS9Hs6GMoA3rU5ycEpXNSQwGLRDM6nP5ymYOahc_m7tFpHC_X6K6KTPfS 3HcLMQ0pI4qMrOaJXixjKFDHQdzfPHNCnh1p1kG5VLpPs6yDxwOuiMB0xbEtJEG9oNt8XBN6_wZN k7mWz_WzfZUtlQC0LzUmFhB_HQgQFPqg2BHS6inWEBmAwlgW7sReHyM.XsIyDbIUtf1xzfPpscBa ZPKBydVugOBZjRtHiPaFzK2AILyslq1NLqWTFBiqcnXW8eSbjIDsfF9g4S_4NdhiIqqGNDvfPFFV htJkZ2TovrxZkLlTooZyeO9GDYPwvu_p.QLaMzppscF0SNNNX5je3AbP.xfSRjreSOUEQfJssXIl oNIOL_8tx1Fy4fz7ARIrC2twBD5zrpVVZUDDMT2A87HXYsDtrrIXVGfHl49oy0gsu81_Ff3onBhE U Received: from sonic.gate.mail.ne1.yahoo.com by sonic308.consmr.mail.sg3.yahoo.com with HTTP; Mon, 17 Feb 2020 16:09:56 +0000 Date: Mon, 17 Feb 2020 16:04:40 +0000 (UTC) From: Hazuki Amamiya To: "pgsql-bugs@lists.postgresql.org" Message-ID: <26594723.3545403.1581955480512@mail.yahoo.com> Subject: Possible memory leak in PostgreSQL 12.2 on truncate, insert or delete when type interval is involved MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3545402_16223361.1581955480510" References: <26594723.3545403.1581955480512.ref@mail.yahoo.com> X-Mailer: WebService/1.1.15199 YMailNorrin Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:73.0) Gecko/20100101 Firefox/73.0 Content-Length: 7277 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_3545402_16223361.1581955480510 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi Developers, Recently, there is a memory leak in the game server I hosted. After checkin= g the log, the leak seems appeared after I upgrade PostgreSQL from 11 to 12= .1, and I have no luck even I upgraded the PostgreSQL to 12.2 [PostgreSQL 1= 2.2 (Debian 12.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debi= an 8.3.0-6) 8.3.0, 64-bit] in Debian 10. I ran a CLI php script which stays forever like a service, and the script w= on't disconnect form PostgreSQL after connected. As my original script is difficult to reproduce for you to debug, here I at= tached a test PHP CLI script which I guess it can simulate the problematic = behaviour found in my script: =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D effective_flight_time"); =C2=A0=C2=A0 =C2=A0} =C2=A0=C2=A0 =C2=A0print "Iterate $j\n"; } ?>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D While the script continuing to run, the RES (shown in command =E2=80=9Ctop= =E2=80=9D) will keep increasing forever. As this is my first bug report here, please accept my apologies if anything= not clear. In avoidance of doubt, although I mentioned in title that type interval may= be the culprit, this is merely my guess and the culprit may be something e= lse. Hazuki ------=_Part_3545402_16223361.1581955480510 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi Developers,

Recent= ly, there is a memory leak in the game server I hosted. After checkin= g the log, the leak seems appeared after I upgrade PostgreSQL from 11= to 12.1, and I have no luck even I upgraded the PostgreS= QL to 12.2 [PostgreSQL 12.2 (Debian 12.2-1.pgdg1= 00+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bi= t] in Debian 10.

I ran a CLI php script which stays forever like a servic= e, and the script won't disconnect form PostgreSQL after connected.<= /span>

<= /span>
As my origin= al script is difficult to reproduce for you to debug, here I attached a tes= t PHP CLI script which I guess it can simulate the problematic behaviour fo= und in my script:

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
<?php
$conn=3Dpg_connect("host=3D127.0.0.1 dbname=3Dfgtracker user= =3Dfgtracker password=3Dfgtracker11 connect_timeout=3D5",PGSQL_CONNECT_FORC= E_NEW);

pg_query ("DROP TABLE IF EXISTS temp_bugreproduction;");
= pg_query ("DROP SEQUENCE IF EXISTS temp_bugreproduction_seq;");
pg_query= ("CREATE SEQUENCE temp_bugreproduction_seq INCREMENT 1 MINVALUE 1 MAXVALUE= 9223372036854775807 START 1 CACHE 1;");
pg_query ("CREATE TABLE temp_bu= greproduction (callsign text,flighttime interval,effective_flight_time inte= rval,rank integer DEFAULT nextval('temp_bugreproduction_seq') NOT NULL) WIT= H (oids =3D false);");
$j=3D0;$sql=3D"";
for ($i=3D0;$i<50;$i++)    $sql.=3D"Insert into temp_bugreproduction (callsign ,f= lighttime ,effective_flight_time) VALUES ('abc', interval '00:00:01', inter= val '00:00:02');";
while (1)
{    $j++;
  = ;  pg_query ("select setval('temp_bugreproduction_seq',1);");
 = ;   pg_query ("Truncate table temp_bugreproduction");
 &n= bsp;  pg_query ($sql);
    for ($i=3D0;$i<10;$i++= )
    {    pg_query ("Insert into temp_bug= reproduction select callsign ,flighttime +'00:00:01' ,effective_flight_time= +'00:00:01' from temp_bugreproduction");
      = ;  pg_query ("delete from temp_bugreproduction where flighttime > e= ffective_flight_time");
    }
    print= "Iterate $j\n";
}
?>
=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D

Whi= le the script continuing to run, the RES (shown in command =E2=80=9Ctop=E2= =80=9D) will keep increasing forever.

As this is my first= bug report here, please accept my apologies if anything not clear.

In avoidance of doubt, although I mentioned in title that type i= nterval may be the culprit, this is merely my guess and the culprit may be = something else.

<= div dir=3D"ltr" data-setdir=3D"false">Hazuki


= ------=_Part_3545402_16223361.1581955480510--