Thread: Conditional commit inside functions
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/
Hello, PostgreSQL has doesn't use rollback segment, so commit over 10000 lines is not necessary, and it is bad. Regards Pavel Stehule 2008/12/25 Gerhard Wiesinger <lists@wiesinger.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On Thu, Dec 25, 2008 at 2:58 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
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
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@wiesinger.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hello!
I tried the following, but still one transaction:
SELECT insert_1Mio();
(parallel select count(id) from employee; is done)
CREATE OR REPLACE FUNCTION insert_some(start_i INTEGER, end_i INTEGER)
RETURNS void
AS $func$
DECLARE
BEGIN
FOR i IN start_i..end_i LOOP
INSERT INTO employee (id, department, firstname, lastname) VALUES (i, i, 'John' || i, 'Smith' || i);
END LOOP;
END;
$func$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION insert_1Mio() RETURNS void
AS $func$
DECLARE
maxcommit INTEGER;
start_i INTEGER;
end_i INTEGER;
now_i INTEGER;
BEGIN
maxcommit := 10000;
start_i :=1;
end_i := 1000000;
now_i := start_i;
FOR i IN start_i..end_i LOOP
IF MOD(i, maxcommit) = 0 THEN
PERFORM insert_some(now_i, i);
now_i := i + 1;
END IF;
END LOOP;
PERFORM insert_some(now_i, end_i);
END;
$func$ LANGUAGE plpgsql;
Any ideas?
Ciao,
Gerhard
--
http://www.wiesinger.com/
On Thu, 25 Dec 2008, Gerhard Wiesinger wrote:
> 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Hello why do you need commit? pavel 2008/12/26 Gerhard Wiesinger <lists@wiesinger.com>: > Hello! > > I tried the following, but still one transaction: > > SELECT insert_1Mio(); > > (parallel select count(id) from employee; is done) > > CREATE OR REPLACE FUNCTION insert_some(start_i INTEGER, end_i INTEGER) > RETURNS void > AS $func$ > DECLARE > BEGIN > FOR i IN start_i..end_i LOOP > INSERT INTO employee (id, department, firstname, lastname) VALUES (i, i, > 'John' || i, 'Smith' || i); > END LOOP; > END; > $func$ LANGUAGE plpgsql; > > CREATE OR REPLACE FUNCTION insert_1Mio() RETURNS void > AS $func$ > DECLARE > maxcommit INTEGER; > start_i INTEGER; > end_i INTEGER; > now_i INTEGER; > BEGIN > maxcommit := 10000; > start_i :=1; > end_i := 1000000; > > now_i := start_i; > > FOR i IN start_i..end_i LOOP > IF MOD(i, maxcommit) = 0 THEN > PERFORM insert_some(now_i, i); > now_i := i + 1; > END IF; > END LOOP; > PERFORM insert_some(now_i, end_i); > END; > $func$ LANGUAGE plpgsql; > > Any ideas? > > Ciao, > Gerhard > > -- > http://www.wiesinger.com/ > > > On Thu, 25 Dec 2008, Gerhard Wiesinger wrote: > >> 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@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Hello, Aren't there any drawbacks in postgrs on such large transaction (like in Oracle), e.g if I would use 500.000.000 or even more? Ciao, Gerhard -- http://www.wiesinger.com/ On Fri, 26 Dec 2008, Pavel Stehule wrote: > Hello > > why do you need commit? > > pavel > > 2008/12/26 Gerhard Wiesinger <lists@wiesinger.com>: >> Hello! >> >> I tried the following, but still one transaction: >> >> SELECT insert_1Mio(); >> >> (parallel select count(id) from employee; is done) >> >> CREATE OR REPLACE FUNCTION insert_some(start_i INTEGER, end_i INTEGER) >> RETURNS void >> AS $func$ >> DECLARE >> BEGIN >> FOR i IN start_i..end_i LOOP >> INSERT INTO employee (id, department, firstname, lastname) VALUES (i, i, >> 'John' || i, 'Smith' || i); >> END LOOP; >> END; >> $func$ LANGUAGE plpgsql; >> >> CREATE OR REPLACE FUNCTION insert_1Mio() RETURNS void >> AS $func$ >> DECLARE >> maxcommit INTEGER; >> start_i INTEGER; >> end_i INTEGER; >> now_i INTEGER; >> BEGIN >> maxcommit := 10000; >> start_i :=1; >> end_i := 1000000; >> >> now_i := start_i; >> >> FOR i IN start_i..end_i LOOP >> IF MOD(i, maxcommit) = 0 THEN >> PERFORM insert_some(now_i, i); >> now_i := i + 1; >> END IF; >> END LOOP; >> PERFORM insert_some(now_i, end_i); >> END; >> $func$ LANGUAGE plpgsql; >> >> Any ideas? >> >> Ciao, >> Gerhard >> >> -- >> http://www.wiesinger.com/ >> >> >> On Thu, 25 Dec 2008, Gerhard Wiesinger wrote: >> >>> 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@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-general >>> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
2008/12/26 Gerhard Wiesinger <lists@wiesinger.com>: > Hello, > > Aren't there any drawbacks in postgrs on such large transaction (like in > Oracle), e.g if I would use 500.000.000 or even more? for insert no Regards Pavel > > Ciao, > Gerhard > > -- > http://www.wiesinger.com/ > > > On Fri, 26 Dec 2008, Pavel Stehule wrote: > >> Hello >> >> why do you need commit? >> >> pavel >> >> 2008/12/26 Gerhard Wiesinger <lists@wiesinger.com>: >>> >>> Hello! >>> >>> I tried the following, but still one transaction: >>> >>> SELECT insert_1Mio(); >>> >>> (parallel select count(id) from employee; is done) >>> >>> CREATE OR REPLACE FUNCTION insert_some(start_i INTEGER, end_i INTEGER) >>> RETURNS void >>> AS $func$ >>> DECLARE >>> BEGIN >>> FOR i IN start_i..end_i LOOP >>> INSERT INTO employee (id, department, firstname, lastname) VALUES (i, >>> i, >>> 'John' || i, 'Smith' || i); >>> END LOOP; >>> END; >>> $func$ LANGUAGE plpgsql; >>> >>> CREATE OR REPLACE FUNCTION insert_1Mio() RETURNS void >>> AS $func$ >>> DECLARE >>> maxcommit INTEGER; >>> start_i INTEGER; >>> end_i INTEGER; >>> now_i INTEGER; >>> BEGIN >>> maxcommit := 10000; >>> start_i :=1; >>> end_i := 1000000; >>> >>> now_i := start_i; >>> >>> FOR i IN start_i..end_i LOOP >>> IF MOD(i, maxcommit) = 0 THEN >>> PERFORM insert_some(now_i, i); >>> now_i := i + 1; >>> END IF; >>> END LOOP; >>> PERFORM insert_some(now_i, end_i); >>> END; >>> $func$ LANGUAGE plpgsql; >>> >>> Any ideas? >>> >>> Ciao, >>> Gerhard >>> >>> -- >>> http://www.wiesinger.com/ >>> >>> >>> On Thu, 25 Dec 2008, Gerhard Wiesinger wrote: >>> >>>> 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@postgresql.org) >>>> To make changes to your subscription: >>>> http://www.postgresql.org/mailpref/pgsql-general >>>> >>> >>> -- >>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-general >>> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> >