Re: Conditional commit inside functions

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(at)wiesinger(dot)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(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
>>
>
> --
> 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
>