pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Hazuki Amamiya <hatsuki_a-ie@yahoo.com.hk>
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Possible memory leak in PostgreSQL 12.2 on truncate, insert or delete when type interval is involved
Date: Mon, 17 Feb 2020 16:04:40 +0000 (UTC)
Message-ID: <26594723.3545403.1581955480512@mail.yahoo.com> (raw)
References: <26594723.3545403.1581955480512.ref@mail.yahoo.com>

Hi Developers,
Recently, there is a memory leak in the game server I hosted. After checking 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 12.2 (Debian 12.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 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 won't disconnect form PostgreSQL after connected.
As my original script is difficult to reproduce for you to debug, here I attached a test PHP CLI script which I guess it can simulate the problematic behaviour found in my script:
================================== <?php
$conn=pg_connect("host=127.0.0.1 dbname=fgtracker user=fgtracker password=fgtracker11 connect_timeout=5",PGSQL_CONNECT_FORCE_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_bugreproduction (callsign text,flighttime interval,effective_flight_time interval,rank integer DEFAULT nextval('temp_bugreproduction_seq') NOT NULL) WITH (oids = false);");
$j=0;$sql="";
for ($i=0;$i<50;$i++)
    $sql.="Insert into temp_bugreproduction (callsign ,flighttime ,effective_flight_time) VALUES ('abc', interval '00:00:01', interval '00:00:02');";
while (1)
{    $j++;
    pg_query ("select setval('temp_bugreproduction_seq',1);");
    pg_query ("Truncate table temp_bugreproduction");
    pg_query ($sql);
    for ($i=0;$i<10;$i++)
    {    pg_query ("Insert into temp_bugreproduction select callsign ,flighttime +'00:00:01' ,effective_flight_time +'00:00:01' from temp_bugreproduction");
        pg_query ("delete from temp_bugreproduction where flighttime > effective_flight_time");
    }
    print "Iterate $j\n";
}
?>====================================
While the script continuing to run, the RES (shown in command “top”) 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 else.

Hazuki

view thread (3+ messages)  latest in thread

Message-ID: <26594723.3545403.1581955480512@mail.yahoo.com>
Permalink:  ../26594723.3545403.1581955480512@mail.yahoo.com/
Also on:    postgresql.org/message-id/26594723.3545403.1581955480512@mail.yahoo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: hatsuki_a-ie@yahoo.com.hk
  Subject: Re: Possible memory leak in PostgreSQL 12.2 on truncate, insert or delete when type interval is involved
  In-Reply-To: <26594723.3545403.1581955480512@mail.yahoo.com>
  References: <26594723.3545403.1581955480512.ref@mail.yahoo.com> <26594723.3545403.1581955480512@mail.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox