From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Len Walter <len(dot)walter(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Commit every N rows in PL/pgsql |
Date: | 2010-05-26 08:47:03 |
Message-ID: | AANLkTiligaKIeNWFkhAFGTUNchl-TMV51QnyRJuTjOIE@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello
it is useless in PostgreSQL - it isn't important if you commit one or
billion updated rows. PostgreSQL has different implementation of
transactions, so some Oracle's issues are not here.
Regards
Pavel Stehule
2010/5/26 Len Walter <len(dot)walter(at)gmail(dot)com>:
> 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2010-05-26 08:56:45 | Re: Commit every N rows in PL/pgsql |
Previous Message | Or Kroyzer | 2010-05-26 08:24:07 | recovery after interrupt in the middle of a previous recovery |