Re: Commit every N rows in PL/pgsql

On 06/03/2010 08:26 AM, Chris Browne wrote:
> len(dot)walter(at)gmail(dot)com (Len Walter) writes:
>> 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.
>
> Unnecessary. On Oracle, the typical scenario is "ORA-1562 FAILED TO
> EXTEND ROLLBACK SEGMENT."
>
> PostgreSQL doesn't have a rollback segment, so there's nothing to run
> out of here. Where Oracle would tend to encourage you to keep your
> transactions rather small, PostgreSQL doesn't require you to care about
> that.
>
> Big transactions, on PostgreSQL, are really no big deal.

Oracle would most certainly NOT tend to encourage you to keep your
transactions rather small! As in any other relational database, they
would -I suggest- encourage you to commit when it is "transactionally
appropriate".

Do you care if your load fails part-way through and the rows inserted up
to that point remain behind? If not, commit frequently. But if it has to
be 'all 110 million or none', then you have one large transaction on
your hands, and you'll have to resource for that -no matter which
database you're using.

Oracle may run out of rollback segment space (for the past 7 years,
they're called undo segments, but no real matter). But other databases
will run out of transaction log space, or have a problem handling
mutli-version concurrency control issues for an extended length of time.
No matter what a particular RDBMS may call it, all would have resource
issues to deal with when coping with a long-running, multi-million row
transaction.