Skip site navigation (1) Skip section navigation (2)

Re: Conditional commit inside functions

From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
To: "Gerhard Wiesinger" <lists(at)wiesinger(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Conditional commit inside functions
Date: 2008-12-26 09:52:07
Message-ID: 162867790812260152n542ea4b7obde3806102794f7d@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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
>

In response to

Responses

pgsql-general by date

Next:From: Gerhard WiesingerDate: 2008-12-26 09:57:13
Subject: Re: Conditional commit inside functions
Previous:From: Pavel StehuleDate: 2008-12-26 09:47:18
Subject: Re: Information about Pages, row versions of tables, indices

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group