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