Re: Conditional commit inside functions - Mailing list 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
>