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