I think I know what the problem is:

I have a couple indexes created with a "lower" function to index on
lowercase.

To return a lowercase text object, I use the "lower" function, as copied
from the postgres source (Oddly enough varchar does not work):

text * lower(text *string)
{
        text       *ret;
        char       *ptr,
                           *ptr_ret;
        int                     m;
 
        if ((string == (text *) NULL) || ((m = VARSIZE(string) -
VARHDRSZ) <= 0))
                return string;
 
        ret = (text *) palloc(VARSIZE(string));
        VARSIZE(ret) = VARSIZE(string);
 
        ptr = VARDATA(string);
        ptr_ret = VARDATA(ret);
 
        while (m--)
                *ptr_ret++ = tolower((unsigned char) *ptr++);
 
        return ret;
}  

During a long update, the indexes must also be updated.

I bet, the memory is not freed until after the update is completed, and
that during the update all the previous results of "lower" remain in
RAM. This explains why it is slow, because I begin to hit swap. This
explains why it crashes, can't get memory.

Is this a reasonable conclusion given the source and the circumstances?
If so, if I alter the text* passed to me, would/could it affect the
system? i.e. will it affect the disk image or other processes currently
accessing the record? If I return the text pointer passed to me after
modification, will postgres attempt to free it twice?

Thu, 18 Jan 2001 09:18:34 -0500
mlw <markw@mohawksoft.com>
palloc? (re: What's with update?)
mlw <markw@mohawksoft.com> writes:
> I think I know what the problem is:
> I have a couple indexes created with a "lower" function to index on
> lowercase.

Ah.  You're correct, functional indexes leak memory in existing PG
releases.  The memory is reclaimed at end of statement, which is not
soon enough if you insert/update a large number of rows.

I think this is fixed in 7.1 though, if you want to try it...

			regards, tom lane

Thu, 18 Jan 2001 13:28:15 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: palloc? (re: What's with update?) 