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((unsignedchar) *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?
