Re: Commit within a PL/PGSQL procedure

>Now I could use Perl or similar to do this, but I was under the
>impression that doing it on "the backend" in PL/PGSQL was the most
efficient.

About 2 years ago, I was under the same impression. We "took out" a lot of
our stored procedures and functions and converted it -- first into PERL
modules and then later on into C modules. The improvements were remarkable,
for each of the conversion stages ("db backend" to PERL and then PERL to C).

I have no idea about the memory problem.

HTH.

--
Mel

-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org]On Behalf Of Josh Berkus
Sent: Tuesday, June 17, 2003 11:41 PM
To: Harry Broomhall; pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Commit within a PL/PGSQL procedure

Harry,

> The problem I have come up against is that such a function is treated
> as a single transaction, so if the database is large the memory gets
> eaten up before it finishes.
>
> I'm told that in the Oracle equivalent system one can insert COMMIT
> statements to aleviate the problem.
>
> Is there any way under PgSQL to do the same? Or is there some other
> 'trick' to achieve this?

No. PostgreSQL's design strategy is that each function is "atomic", or its
own transaction.

Also, is is unlikely that your problem is running out of memory ... far more
likely, later steps in your procedure are suffering from the lack of VACUUM
after earlier steps. And VACUUM may not be done inside a function.

I suggest that you break up the complicated rules into 5-20 seperate
PL/pgSQL
functions, and then call them with a Perl DBI script, with VACUUMs in
between.

I agree, it would be nice to be able to encapsulate this all in the
database,
but PL/pgSQL and our procedureal language functionality needs some more work
... (volunteers?)

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster