Re: psql connection being reset during function?

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
>

In response to

Browse pgsql-sql by date

  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?