commiting in/between functions

From: Magnus Månsson <ganja(at)0x63(dot)nu>
To: pgsql-general(at)postgresql(dot)org
Subject: commiting in/between functions
Date: 2003-03-11 13:10:16
Message-ID: 20030311131015.GA9820@freija
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hey ppl,
I am trying to convert some huge tables in a quite huge database (about
50gigs). The convertion requires me to loop through 30 tables with 30
million rows each with a script an I chose plpgsql to do the job though I
have worked with oracles pl/sql before.

Making the script wasnt any big problem, the problem is the amount of memory
it wants to use, if I had a terabyte of memory I do not think that would be
enough, if I try to convert a 100th of the database at a time it uses all
the swap, 1G and the 512M memory.
Though all tables are statistics and static I know that noone changes in
them, so I would like to commit from time to time, but what I have found
commits are not allowed in a function, it has to be one transaction.

Is there any way to pass my problem besides making my plpgsql take arguments
and call the function about 500 times with different arguments? (that would
work if I could commit between to functions, one function to call and one to
do the work, but not even that is allowed)

And if this still isnt supported, is it planned in some how?

thanks in advance..

--
Magnus Månsson

Responses

Browse pgsql-general by date

  From Date Subject
Next Message btober 2003-03-11 13:16:46 Re: mapping user name to group name
Previous Message Martin Paulďuro 2003-03-11 12:53:48 Create Stored procedure