Thread: Conditional commit inside functions

Conditional commit inside functions

From
Gerhard Wiesinger
Date:
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/


Re: Conditional commit inside functions

From
"Pavel Stehule"
Date:
Hello,

PostgreSQL has doesn't use rollback segment, so commit over 10000
lines is not necessary, and it is bad.

Regards
Pavel Stehule

2008/12/25 Gerhard Wiesinger <lists@wiesinger.com>:
> 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
>

Re: Conditional commit inside functions

From
"Asko Oja"
Date:


On Thu, Dec 25, 2008 at 2:58 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hello,

PostgreSQL has doesn't use rollback segment, so commit over 10000
lines is not necessary, and it is bad.

If you are having database in your laptop it might be true.
In OLTP environments it's not feasible to have large transactions
a) because of locking problems
b) lot's of databases use some kind of replication to have fail over databases doing large updates in such environments causes problems.

We use data_maintainer.py script from SkyTools package to do such updates.

regards
Asko

Regards
Pavel Stehule

2008/12/25 Gerhard Wiesinger <lists@wiesinger.com>:
> 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

Re: Conditional commit inside functions

From
Gerhard Wiesinger
Date:
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
>

Re: Conditional commit inside functions

From
"Pavel Stehule"
Date:
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
>

Re: Conditional commit inside functions

From
Gerhard Wiesinger
Date:
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
>

Re: Conditional commit inside functions

From
"Pavel Stehule"
Date:
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
>>
>