Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7EMhkP52480 for ; Tue, 14 Aug 2001 18:43:46 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f7EMhQY23109; Tue, 14 Aug 2001 18:43:26 -0400 (EDT) To: Randall Skelton cc: pgsql-sql@postgresql.org Subject: Re: psql connection being reset during function? In-reply-to: References: Comments: In-reply-to Randall Skelton message dated "Tue, 14 Aug 2001 21:35:29 +0100" Date: Tue, 14 Aug 2001 18:43:26 -0400 Message-ID: <23106.997829006@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200108/196 X-Sequence-Number: 4428 Randall Skelton 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