From: | Randall Skelton <rhskelto(at)atm(dot)ox(dot)ac(dot)uk> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: psql connection being reset during function? |
Date: | 2001-08-15 08:57:08 |
Message-ID: | Pine.LNX.4.33.0108150953370.11997-100000@mulligan.atm.ox.ac.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)atm(dot)ox(dot)ac(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Mister ics | 2001-08-15 10:16:46 | Re: on update restrict |
Previous Message | Paul McGarry | 2001-08-15 06:49:58 | RE: Temp tables being written to disk. Avoidable? |