Re: Commit within a PL/PGSQL procedure

Josh Berkus writes:
> Harry,
>
> >    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!
>
> Hmmm ... this is highly unlikely to be becuase of the transaction.  When
> Postgres runs out of available RAM, it starts swapping to disk ... which can
> take hours, but will *not* produce that error.

  At the above mentioned point the swap was about 34% used (normaly it
is about 3%).  It takes about an hour for it to get to this point.

>
> What did you set your shared_buffers and sort_mem to?  What OS are you on?

   FreeBSD 5.0-RELEASE with PostgreSQL 7.3.3

shared_buffers not set - so presumably default of 64
sort_mem not set - so presumably default of 1024

   I haven't fiddled with the conf file on the grounds that I have no real
idea of how to do so - and take the view that I would probably make it worse!

>
> >   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.
>
> Postgresql should be able to handle this regardless of table size, it's just a
> question of speed.

   The source file is about 6.4 ^6 records, and the system dies at about
the 1 million mark.

   Regards,
      Harry.