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