Re: Commit within a PL/PGSQL procedure

Josh Berkus writes:
> Harry,
>
[ SNIP]

Many thanks for your reply...

> 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.

The reason I say it runs out of memory is the message:
ERROR: Memory exhausted in AllocSetAlloc(36)
in the log file, and I am running 'top' and see the process hit 512MB at
about this point!

>
> 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.

In fact my *test* function consists of a simple scan through a database,
and 3 INSERTs into a new table for each record in the original, where
the values inserted are derived from values in the original.

>
> 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?)

I was begining to realise that I would have to code it up in Perl and
run it from the "front-end".

And I'm a long way away in expertese to the point that I could
contribute to the functionality! <grin>

Regards,
Harry.