2008/12/26 Gerhard Wiesinger <lists@xxxxxxxxxxxxx>: > 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@xxxxxxxxxxxxx>: >>> >>> 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@xxxxxxxxxxxxxx) >>>> To make changes to your subscription: >>>> http://www.postgresql.org/mailpref/pgsql-general >>>> >>> >>> -- >>> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-general >>> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
