Thread: BUG #8279: Apparent memory leak with use of INTO STRICT in plpgsql?

The following bug has been logged on the website:

Bug reference:      8279
Logged by:          Chad
Email address:      chad.wagner@gmail.com
PostgreSQL version: 9.2.4
Operating system:   Ubuntu 10.04
Description:

If I use the "SELECT ... INTO STRICT" clause with plpgsql it appears to leak
memory, until I disconnect my session and the backend dies.  If I remove the
"INTO STRICT" clause then there is no apparent memory leak.


Below is a test case to reproduce on 9.2.4, and the pmap diff of
before/after on the backend process.


Pmap on backend process (diff of before/after), about a 400MB leak:
--- x1.out    2013-07-03 21:50:02.472438435 -0400
+++ x2.out    2013-07-03 21:51:02.462435759 -0400
@@ -1,5 +1,9 @@
 11945:   postgres: wagnerch wagnerch [local] idle

 Address           Kbytes     RSS    Anon  Locked Mode   Mapping
+00007f4aae074000     148       -       -       - r-x--  plpgsql.so
+00007f4aae099000    2048       -       -       - -----  plpgsql.so
+00007f4aae299000       8       -       -       - r----  plpgsql.so
+00007f4aae29b000       4       -       -       - rw---  plpgsql.so
 00007f4aae29c000     252       -       -       - r----  LC_CTYPE
 00007f4aae2db000  560320       -       -       - rw-s-    [ shmid=0xa9c8000
]
 00007f4ad060b000      48       -       -       - r-x--
libnss_files-2.11.1.so
@@ -129,9 +133,9 @@
 00007f4ad4c36000     116       -       -       - r----  postgres
 00007f4ad4c53000      52       -       -       - rw---  postgres
 00007f4ad4c60000     364       -       -       - rw---    [ anon ]
-00007f4ad5f8b000    1192       -       -       - rw---    [ anon ]
+00007f4ad5f8b000  415820       -       -       - rw---    [ anon ]
 00007ffff3396000     144       -       -       - rw---    [ stack ]
 00007ffff33c6000       4       -       -       - r-x--    [ anon ]
 ffffffffff600000       4       -       -       - r-x--    [ anon ]
 ----------------  ------  ------  ------  ------
-total kB          632108       -       -       -
+total kB         1048944       -       -       -






Test case:


/*


CREATE TABLE head_tb AS
SELECT generate_series (1, 30) AS id;


CREATE TABLE det_tb AS
SELECT a.a header_id, b.b detail_id, rpad ('X', 1000, 'X') AS data
  FROM generate_series (1,30) AS a, generate_series (1, 50000) AS b;


CREATE INDEX det_tb_n1 ON det_tb (header_id);
CREATE UNIQUE INDEX det_tb_u1 ON det_tb (header_id, detail_id);


*/


DO LANGUAGE plpgsql
$$
DECLARE
   l_head_rec RECORD;
   l_det_rec RECORD;
   l_next_det_rec RECORD;
BEGIN
   FOR l_head_rec IN
      SELECT id,
             LEAD (id) OVER (
                ORDER BY id
                RANGE BETWEEN UNBOUNDED PRECEDING
                          AND UNBOUNDED FOLLOWING
             ) next_id
        FROM head_tb
      ORDER BY id
   LOOP
      FOR l_det_rec IN
         SELECT *
           FROM det_tb
          WHERE header_id = l_head_rec.id
      LOOP
         BEGIN
            SELECT *
              INTO STRICT l_next_det_rec
              FROM det_tb
             WHERE detail_id = l_det_rec.detail_id
               AND header_id = l_head_rec.next_id;


         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               NULL;
         END;
      END LOOP;
   END LOOP;
END;
$$;