BUG #8318: memory leak during CREATE TEMPORARY TABLE - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      8318
Logged by:          G.Bakalarski
Email address:      g.bakalarski@icm.edu.pl
PostgreSQL version: 9.1.3
Operating system:   Linux Debian 6 (squeezy)
Description:

When doing these kind of statements (this is from log file):


2013-07-21 02:29:26 UTC [16683] composite LOG:  duration: 630631.234 ms
execute <unnamed>: CREATE TEMPORARY TABLE
tmp2_4da9ce5d_bf86_4331_a18d_4c918cdd71de AS SELECT contributorMD5,
contributorFirstName, contributorFirstName_sortkey, contributorLastName,
contributorLastName_sortkey, contributorTitle, contributorTitle_sortkey,
contributorLastFirstName_sortkey, contributorLastFirstName_plain,
contributorFirstLastName, contributorFirstLastName_plain, COUNT(*) AS
_aggregated FROM rel_2_repContributorView AS _main WHERE _tick > $1 AND
_tick <= $2 AND contributorType = $3 GROUP BY contributorMD5,
contributorFirstName, contributorFirstName_sortkey, contributorLastName,
contributorLastName_sortkey, contributorTitle, contributorTitle_sortkey,
contributorLastFirstName_sortkey, contributorLastFirstName_plain,
contributorFirstLastName, contributorFirstLastName_plain UNION ALL SELECT
contributorMD5, contributorFirstName, contributorFirstName_sortkey,
contributorLastName, contributorLastName_sortkey, contributorTitle,
contributorTitle_sortkey, contributorLastFirstName_sortkey,
contributorLastFirstName_plain, contributorFirstLastName,
contributorFirstLastName_plain, -COUNT(*) AS _aggregated FROM
del_2_repContributorView AS _main WHERE _tick > $4 AND _tick <= $5 AND
_origtick <= $6 AND (contributorType = $7) GROUP BY contributorMD5,
contributorFirstName, contributorFirstName_sortkey, contributorLastName,
contributorLastName_sortkey, contributorTitle, contributorTitle_sortkey,
contributorLastFirstName_sortkey, contributorLastFirstName_plain,
contributorFirstLastName, contributorFirstLastName_plain
2013-07-21 02:29:26 UTC [16683] composite DETAIL:  parameters: $1 = '6', $2
= '1160', $3 = 'PERSON', $4 = '6', $5 = '1160', $6 = '6', $7 = 'PERSON'


a commited system memory measured
with sar -r , goes up by 2-20GBytes and never goes down eg:


00:00:01    kbmemfree kbmemused  %memused kbbuffers  kbcached  kbcommit
%commit
01:55:01     76347732 122133736     61.53         0  29816912 116970332
58.93
02:05:01     75886088 122595380     61.77         0  30289504 116960972
58.93
02:15:01     75317968 123163500     62.05         0  30814996 117007696
58.95
02:25:01     31505704 166975764     84.13         0  73817200 117399024
59.15
02:35:01       439584 198041884     99.78         0  90340732 131819000
66.41
02:45:01       436480 198044988     99.78         0  89510732 131750320
66.38
02:55:01     14986680 183494788     92.45         0  75110884 132450980
66.73
03:05:01     15881024 182600444     92.00         0  74202256 132465840
66.74
03:15:01     45572732 152908736     77.04         0  45209796 132472464
66.74
03:25:01     44059268 154422200     77.80         0  46752888 132480808
66.75
03:35:01     42515568 155965900     78.58         0  48230784 132536984
66.78
03:45:01     40578860 157902608     79.56         0  50224608 132477148
66.75
03:55:01     38876772 159604696     80.41         0  51858264 132495080
66.75
04:05:01     72974428 125507040     63.23         0  18578900 132527424
66.77




This happens (noticeably) when statement last long enough (short lasting
statements don't pull up memory).


After few days (such long lasting Create temp table happens few time a day),
my huge server (192GB of RAM), gets stucked due to no memory available
(usually %commit is around 103-105%), linux OOM killer goes into action - it
usually kills one of postgres processes - postmaster restart all databases)
and after all linux  has again some 100GB of not commited memory (--> sar
-r).


Looking in rel notes of PG 9.1.4+ I did not see any fixes on this ...


GB