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.