Excellent! The function you suggest is certainly a better approach... 5
minutes of processing and it was done. Thanks Tom.
Cheers,
Randall
On Tue, 14 Aug 2001, Tom Lane wrote:
> Randall Skelton <rhskelto@atm.ox.ac.uk> writes:
> > problem #2: While this works perfectly for a small table of 10 entries, it
> > crashes the database connection when I try to update 311537 rows
>
> In present releases, plpgsql tends to leak a lot of memory intra-call;
> you're probably just running out of memory. (I think we have cleaned up
> the leak problems in the CVS-tip code, but that won't help you unless
> you're brave enough to run a snapshot version.) Consider restructuring
> your approach so that the plpgsql function just does a single conversion
> and is invoked separately at each row:
>
> UPDATE atlas3_path SET sgmt_timestamp = mydatetime(sgmty,sgmtmo,sgmtd,...);
>
> where mydatetime takes five integers and a float and returns a timestamp.
> This way, any memory leaked during plpgsql function execution is
> reclaimed when the function exits, so it doesn't build up across rows.
>
> This will probably be much faster than your other approach anyway, since
> it doesn't require re-finding each row with a fresh UPDATE. A function
> call is a whole lot cheaper than parsing, planning, and executing a new
> query.
>
> regards, tom lane
>