Re: Conditional commit inside functions

On Thu, Dec 25, 2008 at 2:58 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>wrote:

> Hello,
>
> PostgreSQL has doesn't use rollback segment, so commit over 10000
> lines is not necessary, and it is bad.

If you are having database in your laptop it might be true.
In OLTP environments it's not feasible to have large transactions
a) because of locking problems
b) lot's of databases use some kind of replication to have fail over
databases doing large updates in such environments causes problems.

We use data_maintainer.py script from SkyTools package to do such updates.

regards
Asko

>
> Regards
> Pavel Stehule
>
> 2008/12/25 Gerhard Wiesinger <lists(at)wiesinger(dot)com>:
> > Hello!
> >
> > I want to translate the following Oracle PL/SQL script into plpgsql.
> > Especially I'm having problems with the transaction thing. i tried START
> > TRANSACTION and COMMIT without success.
> >
> > Any ideas?
> >
> > Thanx.
> >
> > Ciao,
> > Gerhard
> >
> > CREATE OR REPLACE PROCEDURE insert_1Mio
> > IS
> > maxcommit NUMBER;
> > BEGIN
> > maxcommit := 10000;
> >
> > FOR i IN 1..1000000 LOOP
> > INSERT INTO employee (id, department, firstname, lastname) VALUES (i,
> i,
> > 'John' || to_char(i), 'Smith' || to_char(i));
> > IF MOD(i, maxcommit) = 0 THEN
> > COMMIT;
> > END IF;
> > END LOOP;
> >
> > COMMIT;
> > END;
> >
> >
> >
> > --
> > http://www.wiesinger.com/
> >
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> >
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>