From: | Len Walter <len(dot)walter(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Commit every N rows in PL/pgsql |
Date: | 2010-05-26 06:27:22 |
Message-ID: | AANLkTinrqm3pXn-AOoUSKY7s_Xb4If6ajgpQ1bcXATF-@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I need to populate a new column in a Postgres 8.3 table. The SQL would be
something like "update t set col_c = col_a + col_b". Unfortunately, this
table has 110 million rows, so running that query runs out of memory.
In Oracle, I'd turn auto-commit off and write a pl/sql procedure that keeps
a counter and commits every 10000 rows (pseudocode):
define cursor curs as select col_a from t
while fetch_from_cursor(curs) into a
update t set col_c = col_a + col_b where col_a = a
i++
if i > 10000
commit; i=0;
end if;
commit;
PL/pgsql doesn't allow that because it doesn't support nested transactions.
Is there an equivalent Postgres way of doing this?
cheers,
Len
--
len(dot)walter(at)gmail(dot)com skype:lenwalter
msn:len(dot)walter(at)gmail(dot)com<msn%3Alen(dot)walter(at)gmail(dot)com>
From | Date | Subject | |
---|---|---|---|
Next Message | Ravi Katkar | 2010-05-26 06:52:53 | Re: Transaction with in function |
Previous Message | A. Kretschmer | 2010-05-26 05:14:08 | Re: Transaction with in function |