Re: Newbie questions relating to transactions

On Sunday, March 08, 2009, Gregory Stark wrote:
> Carl Sopchak <carl(dot)sopchak(at)cegis123(dot)com> writes:
> > Well, the upgrade to 8.3 seemed to rid me of the command limit, but now
> > I'm running out of memory. I have 2Gb physical and 8Gb swap (after
> > adding 4Gb).
>
> What do you mean you're running out of memory? For most part of Postgres
> that's only a problem if you've configured it to use more memory than your
> system can handle -- such as setting work_mem or shared_buffers too large.
"ERROR: Out of Memory" is what I meant when I said I was running out of
memory! :-) This is returned by psql, but it is the postmaster process that
is hitting the wall.

I haven't touched the configuration, so whatever the default in 8.3 is, is
what these are set at. I'll look and bring the numbers down if necessary.
Thanks for the pointer.

>
> One area that can cause problems is having too many trigger executions
> queued up. I don't know if that's what you're running into though.
There are no triggers on any of the tables in the database...

>
> > Is there a way for me to run this outside of one huge transaction? This
> > really shouldn't be using more than a few hundred megs of RAM (assuming
> > cursor records are all stored in memory)...
>
> Personally I find it much more flexible to implement these types of jobs as
> external scripts connecting as a client. That lets you stop/start
> transactions freely. It also allows you to open multiple connections or run
> the client-side code on a separate machine which can have different
> resources available.
I suppose I could go that route... I was just trying to keep things simple,
and all in the database. This was supposed to be a "quick and dirty" way to
calculate, store and access these numbers... So much for "quick"... If there
were stored procedures as in other databases that I've used, or a way to run
a function outside an implicit transaction, then I'd be all set. Guess I'll
have to add a layer to the setup. Got any suggestions as to a good, fast,
language to code the external script in?

Thanks for the help,

Carl