I'm working on an upgrade of PostgreSQL embedded in a product from
version 8.1.x to 9.1.x. One particular PL/pgSQL function is giving us an
issue as there seems to be a rather severe regression in memory usage --
a query that finishes in 8.1 causes an out of memory exception on 9.1.
Using the same data on the same machine I see memory use stay steady at
a reasonably low value on the 8.1 installation but steadily climb on 9.1
(I watched it go over 2 GB and canceled the query -- the production
machines are 32 bit)
The attached standalone script seems to reproduce the effect. On 8.1
memory usage remains steady and low, on 9.1 I watched it climb past 1.1
GB and canceled the query.
I suspect the append node to be the culprit because if I skip the "UNION
ALL", i.e. if I use one generate_series with 20 million rows instead of
10 with 2 million each, then I do not see the memory leak. The real
function is actually selecting over many inherited tables (i.e. a
partitioned table).
Thoughts?
Thanks,
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
CREATE OR REPLACE FUNCTION selectDetailed ()
RETURNS setof record AS $_$
DECLARE
result_rec record;
sql text;
BEGIN
sql := $$
--EXPLAIN
SELECT * FROM (
SELECT now(),
ss.id, ss.id, ss.id, ss.id, ss.id, ss.id,
'a'::text, 'bbbbbbbb'::text,
'cccccccccccccccccc'::text,
'ddddddddddddddddddddddddddddddddddd'::text,
'eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee'::text,
'fffffffffffffffffffffffffffffffffff'::text
FROM (SELECT id::bigint FROM generate_series(1,2000000) as t(id)) ss
UNION ALL
SELECT now(),
ss.id, ss.id, ss.id, ss.id, ss.id, ss.id,
'a'::text, 'bbbbbbbb'::text,
'cccccccccccccccccc'::text,
'ddddddddddddddddddddddddddddddddddd'::text,
'eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee'::text,
'fffffffffffffffffffffffffffffffffff'::text
FROM (SELECT id::bigint FROM generate_series(1,2000000) as t(id)) ss
UNION ALL
SELECT now(),
ss.id, ss.id, ss.id, ss.id, ss.id, ss.id,
'a'::text, 'bbbbbbbb'::text,
'cccccccccccccccccc'::text,
'ddddddddddddddddddddddddddddddddddd'::text,
'eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee'::text,
'fffffffffffffffffffffffffffffffffff'::text
FROM (SELECT id::bigint FROM generate_series(1,2000000) as t(id)) ss
UNION ALL
SELECT now(),
ss.id, ss.id, ss.id, ss.id, ss.id, ss.id,
'a'::text, 'bbbbbbbb'::text,
'cccccccccccccccccc'::text,
'ddddddddddddddddddddddddddddddddddd'::text,
'eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee'::text,
'fffffffffffffffffffffffffffffffffff'::text
FROM (SELECT id::bigint FROM generate_series(1,2000000) as t(id)) ss
UNION ALL
SELECT now(),
ss.id, ss.id, ss.id, ss.id, ss.id, ss.id,
'a'::text, 'bbbbbbbb'::text,
'cccccccccccccccccc'::text,
'ddddddddddddddddddddddddddddddddddd'::text,
'eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee'::text,
'fffffffffffffffffffffffffffffffffff'::text
FROM (SELECT id::bigint FROM generate_series(1,2000000) as t(id)) ss
UNION ALL
SELECT now(),
ss.id, ss.id, ss.id, ss.id, ss.id, ss.id,
'a'::text, 'bbbbbbbb'::text,
'cccccccccccccccccc'::text,
'ddddddddddddddddddddddddddddddddddd'::text,
'eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee'::text,
'fffffffffffffffffffffffffffffffffff'::text
FROM (SELECT id::bigint FROM generate_series(1,2000000) as t(id)) ss
UNION ALL
SELECT now(),
ss.id, ss.id, ss.id, ss.id, ss.id, ss.id,
'a'::text, 'bbbbbbbb'::text,
'cccccccccccccccccc'::text,
'ddddddddddddddddddddddddddddddddddd'::text,
'eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee'::text,
'fffffffffffffffffffffffffffffffffff'::text
FROM (SELECT id::bigint FROM generate_series(1,2000000) as t(id)) ss
UNION ALL
SELECT now(),
ss.id, ss.id, ss.id, ss.id, ss.id, ss.id,
'a'::text, 'bbbbbbbb'::text,
'cccccccccccccccccc'::text,
'ddddddddddddddddddddddddddddddddddd'::text,
'eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee'::text,
'fffffffffffffffffffffffffffffffffff'::text
FROM (SELECT id::bigint FROM generate_series(1,2000000) as t(id)) ss
UNION ALL
SELECT now(),
ss.id, ss.id, ss.id, ss.id, ss.id, ss.id,
'a'::text, 'bbbbbbbb'::text,
'cccccccccccccccccc'::text,
'ddddddddddddddddddddddddddddddddddd'::text,
'eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee'::text,
'fffffffffffffffffffffffffffffffffff'::text
FROM (SELECT id::bigint FROM generate_series(1,2000000) as t(id)) ss
UNION ALL
SELECT now(),
ss.id, ss.id, ss.id, ss.id, ss.id, ss.id,
'a'::text, 'bbbbbbbb'::text,
'cccccccccccccccccc'::text,
'ddddddddddddddddddddddddddddddddddd'::text,
'eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee'::text,
'fffffffffffffffffffffffffffffffffff'::text
FROM (SELECT id::bigint FROM generate_series(1,2000000) as t(id)) ss
) as t_inner order by 1
$$;
FOR result_rec IN EXECUTE sql LOOP
RETURN NEXT result_rec;
END LOOP;
RETURN;
END;
$_$ LANGUAGE plpgsql STABLE;
select count(*) from selectDetailed() AS t(
ts timestamp with time zone,
d1 bigint, d2 bigint, d3 bigint,
d4 bigint, d5 bigint, d6 bigint,
s1 text, s2 text, s3 text,
s4 text, s5 text, s6 text
);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
