Good day,
I have a client that was using a PL/pgSQL function called html_linebreaks
to translate newlines into (X)HTML <br/> tags, and he ran into a serious
memory issue today which actually brought down his Linux server. It looked
like this:
DECLARE
formatted_string text := '''';
BEGIN
IF $1 IS NULL THEN
RETURN '''';
END IF;
FOR i IN 0 .. length($1) LOOP
IF substr($1, i, 1) = ''\\n'' THEN
formatted_string := formatted_string || ''<br/>'';
ELSE
formatted_string := formatted_string || substr($1, i, 1);
END IF;
END LOOP;
RETURN formatted_string;
END;
Now, this obviously isn't the most efficient thing in the world, but on a
28k text field it quickly ate up his entire system's memory (over 300
megabytes) in a PostgreSQL 7.1.3 postmaster instance, and required a
reboot of the system to clean up after it.
Troubleshooting it a bit, it seemed that either the substr() or the
concat operator was never giving back the memory it was allocating for its
task.
I re-wrote the function for him in C as a shared object to avoid the
problem, but how exactly does PL/pgSQL manage the memory it requires for
calls to functions and operators? Is there any way to explicitly free
bytes you're done with before asking for more?
Also, does 7.2's version of PL/pgSQL behave the same way?
Regards,
Jw.
--
jlx@commandprompt.com, by way of pgsql-general@commandprompt.com
http://www.postgresql.info/
http://www.commandprompt.com/
