Clive Page wrote:
> On Wed, 19 Jan 2005, Richard Huxton wrote:
>
>
>>Nothing wrong with what you're doing, however, you are running a
>>transaction that touches 142 million rows (expiring the old rows and
>>adding new ones). Still, unless you are particularly short of memory, or
>>haven't tuned PostgreSQL it should be fine.
>>
>>Some questions:
>>1. Is the table particularly wide (i.e. number/size of columns)?
>
> Well 28 columns, one varchar, one box, rest 4-byte real/integer.
Nothing terribly dramatic.
>>2. Do you have any foreign keys/triggers on the table?
>
>
> No triggers, but there's one R-tree on the box, and one B-tree on an
> integer column. I guess I should have dropped those and re-created
> afterwards, but the recreation takes some time, so I hoped to avoid the
> need to do that.
Hmm - wonder if there might be some memory leak in updates to the R-tree
- it gets used less than B-tree, so it could be. If you reply to this,
make sure you mention your version of PG - one of the developers might
know more. Probably also worth looking in the documentation for 8.0 and
check the release notes section to see if any changes look applicable to
your problem.
Perhaps a quick search of the -bugs mailing list archives too.
--
Richard Huxton
Archonet Ltd