From: Tom Lane <tgl@sss.pgh.pa.us>
To: Randall Skelton <rhskelto@atm.ox.ac.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: psql connection being reset during function?
Date: Tue, 14 Aug 2001 18:43:26 -0400
Message-ID: <23106.997829006@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.33.0108142031540.10412-100000@mulligan.atm.ox.ac.uk>
References: <Pine.LNX.4.33.0108142031540.10412-100000@mulligan.atm.ox.ac.uk>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, rhskelto@atm.ox.ac.uk
Subject: Re: psql connection being reset during function?
In-Reply-To: <23106.997829006@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox