Обсуждение: Commit every N rows in PL/pgsql

On Wed, May 26, 2010 at 1:27 AM, Len Walter <len.walter@gmail.com> wrote:
>
> PL/pgsql doesn't allow that because it doesn't support nested transactions.
> Is there an equivalent Postgres way of doing this?

what about this?

create function f() returns void as $$
declare
    r record;
begin
   for r in select col_a from t loop
       update t set col_c = col_a + col_b where col_a = r.a;
   end loop;
   return;
end;
$$ language plpgsql;

--
Jaime Casanova         www.2ndQuadrant.com
Soporte y capacitación de PostgreSQL

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.walter@gmail.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.walter@gmail.com skype:lenwalter  msn:len.walter@gmail.com
>

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'rerunning 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
othertransactions and so both versions of the rows need to be kept around until the outer transaction commits. It's not
goingto 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!

On Wed, May 26, 2010 at 04:27:22PM +1000, 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.
> 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;

you can't do it easily with plpgsql because plpgsql cannot influence
transactions.

what you can do is to use some client (like psql) and make it simply
issue a lot of queries.

for example. let's assume your table t has column id, which is primary
key and contains values from 1 to 100000.

now you can:
perl -e 'for ($i=1; $i<100000; $i+=1000) {printf "update t set col_c = col_a + col_b where col_a = a and id between %u
and%u;\n", $i, $i+999}' | psql -U ... -d ... 

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

Len Walter <len.walter@gmail.com> 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.
>>
>> That's unusual, what is the error you get?

> Here it is:
> ...
>     AfterTriggerEvents: 2642403328 total in 327 blocks; 10176 free (319
> chunks); 2642393152 used

And there's the problem.  Evidently you have an AFTER trigger on the
table, and the queued events for that trigger are overrunning memory.

Figuring out what to do about that would depend on what the trigger's
for, but usually the best bet is to drop the trigger and instead do
whatever it's doing in some bulk fashion instead of retail.

(We have a TODO item to allow the trigger queue to spill to disk instead
of always being kept in memory; but it's fairly low on the priority
list, because quite frankly once the event list gets this large, you'd
not want to wait around for all the triggers to execute anyway ...)

            regards, tom lane

len.walter@gmail.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.
--
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://cbbrowne.com/info/internet.html
"MS  apparently now  has a  team dedicated  to tracking  problems with
Linux  and publicizing them.   I guess  eventually they'll  figure out
this back fires... ;)" -- William Burrow <aa126@DELETE.fan.nb.ca>

Chris Browne <cbbrowne@acm.org> writes:
> len.walter@gmail.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.

Unless, of course, there's a trigger on that table.  In which case
it's pretty likely that you'd want to suppress the trigger...
--
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://cbbrowne.com/info/internet.html
"MS  apparently now  has a  team dedicated  to tracking  problems with
Linux  and publicizing them.   I guess  eventually they'll  figure out
this back fires... ;)" -- William Burrow <aa126@DELETE.fan.nb.ca>

On 06/03/2010 08:26 AM, Chris Browne wrote:
> len.walter@gmail.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.