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

Shane Ambler <pgsql@Sheeky.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