On 05/09/2012 03:36 PM, Joe Conway wrote:
> Good call -- of course that just means my contrived example fails to
> duplicate the real issue :-(
> In the real example, even with work_mem = 1 MB I see the same behavior
> on 9.1.
OK, new script. This more faithfully represents the real life scenario,
and reproduces the issue on HEAD with out-of-the-box config settings,
versus 8.1 which completes the query having never exceeded a very modest
memory usage:
---------------
On pg 8.1 with out of the box config:
VIRT RES SHR
199m 11m 3032
---------------
On pg head with out of the box config:
VIRT RES SHR
1671m 1.5g 16m
---------------
I have not tried your ulimit suggestion yet but will do that next.
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
CREATE OR REPLACE FUNCTION create_parts ()
RETURNS text AS $_$
DECLARE
i int;
sql text;
BEGIN
sql := 'CREATE TABLE foo (
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)';
EXECUTE sql;
FOR i IN 1..100 LOOP
sql := 'CREATE TABLE foo_' || i || ' () INHERITS (foo)';
EXECUTE sql;
sql := $$
INSERT INTO foo_$$ || i || $$ 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,200000) as t(id)) ss
$$;
EXECUTE sql;
END LOOP;
RETURN 'OK';
END;
$_$ LANGUAGE plpgsql VOLATILE;
SELECT create_parts ();
CREATE OR REPLACE FUNCTION selectDetailed ()
RETURNS setof foo AS $_$
DECLARE
result_rec record;
sql text;
BEGIN
sql := 'SELECT * FROM foo';
FOR result_rec IN EXECUTE sql LOOP
RETURN NEXT result_rec;
END LOOP;
RETURN;
END;
$_$ LANGUAGE plpgsql STABLE;
select count(*) from selectDetailed();
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
