Re: Conditional commit inside functions

2008/12/26 Gerhard Wiesinger <lists(at)wiesinger(dot)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(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
>>
>