Re: out of memory with INSERT INTO... SELECT...

Shane Ambler <pgsql(at)Sheeky(dot)Biz> writes:
> SELECT generate_series(1,1000000) AS idx
> , substring('abcdefghijklmnopqrstuvwxyz0123456789' from
> cast((random()*36)as integer) for 1)
> ||substring('abcdefghijklmnopqrstuvwxyz0123456789' from
> cast((random()*36)as integer) for 1)
> ||substring('abcdefghijklmnopqrstuvwxyz0123456789' from
> cast((random()*36)as integer) for 1)
> ||substring('abcdefghijklmnopqrstuvwxyz0123456789' from
> cast((random()*36)as integer) for 1)
> ||substring('abcdefghijklmnopqrstuvwxyz0123456789' from
> cast((random()*36)as integer) for 1)
> ||substring('abcdefghijklmnopqrstuvwxyz0123456789' from
> cast((random()*36)as integer) for 1)
> ||substring('abcdefghijklmnopqrstuvwxyz0123456789' from
> cast((random()*36)as integer) for 1)
> AS pincode

> If I change the generate_series to 10M rows it gets an out of memory
> error at about 3.5GB (VSZ) and a bit under 300MB(RSS)

Seems to be the same issue recently discussed here:
http://archives.postgresql.org/pgsql-performance/2008-01/msg00031.php

For the moment I'd suggest recasting it to avoid having the SRF in the
SELECT target list (which is pretty darn weird anyway, in this usage
--- I don't see any very good SQL-semantics argument why the substring
expression would get evaluated more than once here). Something like

INSERT INTO codes
SELECT
substring('abcdefghijklmnopqrstuvwxyz0123456789' from
cast((random()*36)as integer) for 1)
||substring('abcdefghijklmnopqrstuvwxyz0123456789' from
cast((random()*36)as integer) for 1)
||substring('abcdefghijklmnopqrstuvwxyz0123456789' from
cast((random()*36)as integer) for 1)
||substring('abcdefghijklmnopqrstuvwxyz0123456789' from
cast((random()*36)as integer) for 1)
||substring('abcdefghijklmnopqrstuvwxyz0123456789' from
cast((random()*36)as integer) for 1)
||substring('abcdefghijklmnopqrstuvwxyz0123456789' from
cast((random()*36)as integer) for 1)
||substring('abcdefghijklmnopqrstuvwxyz0123456789' from
cast((random()*36)as integer) for 1)
FROM generate_series(1,1000000) AS idx;

regards, tom lane