Re: Commit every N rows in PL/pgsql

On 26 May 2010, at 8:27, Len Walter wrote:

> 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.

That's unusual, what is the error you get?

Your table will grow (on disk) to twice the size it had previously, as new rows will be created for the transaction you're running the update from, but it will shrink again with vacuuming and usage. So you may run out of disk space, but /never/ out of memory. If you do then you probably have configured Postgres to use more memory than you have.

> 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?

Nested transactions wouldn't solve the problem, as the rows you "commit" here still aren't allowed to be visible to other transactions and so both versions of the rows need to be kept around until the outer transaction commits. It's not going to save you any space.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4bfce26010413711619512!