I've been using Postgres 8.3 in a production environment for some time now, and have noticed a difference in long-term non-shared memory use by Postgres since upgrading the system from 8.1. After initially making the switch to 8.3 and making only one schema change (changed a text column to xml) the server ran and used memory more or less the same as it had in 8.1. Later, I added a large set of plpgsql trigger functions that operate on that new xml column data, using the xpath() function to extract bits of XML and populate them into normal tables. The server has been running in this fashion for many months now, and there is a noticeable difference in how Postgres is using memory now, in that over time it's non-shared memory use is climbing higher and higher. Right now I'm tracking this from data captured by Munin on the system. The memory creep is very slight, but over many months is easy to discern. I was wondering if anyone else out there is making heavy use of xpath() and related functions in plpgsql functions, and if they've noticed a trend like this? Is this normal for Postgres to accumulate non-shared memory over time, up to some configured maximum? A second question I have about is about plpgsql and memory use in general. If I run some of these plpgsql functions using a lot of xpath() calls on large sets of data, huge amounts of memory are consumed (gigabytes) and the memory seems to accumulate until the transaction the functions are running in completes. Do invocations of plpgsql functions retain memory they used until the end of the transaction? -- m@
