Conditional commit inside functions

Started by Gerhard Wiesingerover 17 years ago7 messagesgeneral

lists@wiesinger.com

over 17 years ago

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/

Pavel Stehule

pavel.stehule@gmail.com

over 17 years ago

In reply to: Gerhard Wiesinger (#1)

Re: Conditional commit inside functions

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>:

Show quoted text

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

Asko Oja

ascoja@gmail.com

over 17 years ago

In reply to: Pavel Stehule (#2)

Re: Conditional commit inside functions

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

Show quoted text

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

Gerhard Wiesinger

lists@wiesinger.com

over 17 years ago

In reply to: Gerhard Wiesinger (#1)

Re: Conditional commit inside functions

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:

Show quoted text

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

Pavel Stehule

pavel.stehule@gmail.com

over 17 years ago

In reply to: Gerhard Wiesinger (#4)

Re: Conditional commit inside functions

Hello

why do you need commit?

pavel

2008/12/26 Gerhard Wiesinger <lists@wiesinger.com>:

Show quoted text

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

Gerhard Wiesinger

lists@wiesinger.com

over 17 years ago

In reply to: Pavel Stehule (#5)

Re: Conditional commit inside functions

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:

Show quoted text

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

Pavel Stehule

pavel.stehule@gmail.com

over 17 years ago

In reply to: Gerhard Wiesinger (#6)

Re: Conditional commit inside functions

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

Show quoted text

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