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

Re: Conditional commit inside functions

From: Gerhard Wiesinger <lists(at)wiesinger(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Conditional commit inside functions
Date: 2008-12-26 08:25:16
Message-ID: alpine.LFD.1.10.0812260923060.10575@bbs.intern (view raw or flat)
Thread:
Lists: pgsql-general
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
>

In response to

Responses

pgsql-general by date

Next:From: Gerhard WiesingerDate: 2008-12-26 08:29:19
Subject: Re: Information about Pages, row versions of tables, indices
Previous:From: Dmitry KoterovDate: 2008-12-25 23:08:16
Subject: Bgwriter and pg_stat_bgwriter.buffers_clean aspects

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