Re: 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