Very big transaction in a stored procedure : how can i commit in the middle of it ?
Hi,
I already know that transaction is impossible inside a function, but I think I really need a way to counter this
I have a stored procedure in pl/sql that makes about 2 000 000 insert. With the way it works, PostGreSQL il making a unique transaction with all this, resulting so bad performances I can't wait the procedure to finish
I must find a way to make commit between INSERT.
Thanks in advance,
Célestin
Here is the skeleton of my code :
CREATE OR REPLACE FUNCTION F2(...) AS
$$ DECLARE
...
BEGIN
...
FOR ligne IN ...
...
LOOP
NSERT INTO
< place I wish I could put a commit >
END LOOP
...
END $$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION F1(...) AS
$$ DECLARE
BEGIN
FOR all IN ...
LOOP
PERFORM F2(...)
< another place I could put my commit >
END LOOP
...
END $$ LANGUAGE plpgsql;
2007 - Maporama International - Outgoing mail scanned by BlackSpider
On Thu, May 24, 2007 at 03:59:15PM +0200, Célestin HELLEU wrote:
Hi,
I already know that transaction is impossible inside a function, but I think I really need a way to counter this
I have a stored procedure in pl/sql that makes about 2 000 000
insert. With the way it works, PostGreSQL il making a unique
transaction with all this, resulting so bad performances I can't wait
the procedure to finish
In general making seperate transactions slows things down, not speeds
things up. Have you actually check what the cause of the slowness is?
Are there any triggers, foreign key, etc defined. Is the query in the
loop fast enough?
You're going to have to provide more details.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
From each according to his ability. To each according to his ability to litigate.
Well, with any database, if I had to insert 20 000 000 record in a table, I wouldntt do it in one transaction, it makes very big intermediate file, and the commit at the end is really heavy.
I would cut the transaction in midi-transaction, of let's say 1000 records.
There is either not really more code, no trigger, no key, etc.
Imagine something like this :
FOR all IN (select * from TABLE1)
LOOP
FOR some IN (select * from)
LOOP
INSERT INTO TABLE2 VALUES (all.id, some.id)
END LOOP
END LOOP
I with I could put a commit in the inside for !!
-----Message d'origine-----
De : Martijn van Oosterhout [mailto:kleptog@svana.org]
Envoyé : jeudi 24 mai 2007 16:48
À : Célestin HELLEU
Cc : pgsql-general@postgresql.org
Objet : Re: [GENERAL] Very big transaction in a stored procedure : how can i commit in the middle of it ?
On Thu, May 24, 2007 at 03:59:15PM +0200, Célestin HELLEU wrote:
Hi,
I already know that transaction is impossible inside a function, but I think I really need a way to counter this
I have a stored procedure in pl/sql that makes about 2 000 000
insert. With the way it works, PostGreSQL il making a unique
transaction with all this, resulting so bad performances I can't wait
the procedure to finish
In general making seperate transactions slows things down, not speeds
things up. Have you actually check what the cause of the slowness is?
Are there any triggers, foreign key, etc defined. Is the query in the
loop fast enough?
You're going to have to provide more details.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
From each according to his ability. To each according to his ability to litigate.
2007 - Maporama International - Outgoing mail scanned by BlackSpider
Import Notes
Resolved by subject fallback
=?iso-8859-1?Q?C=E9lestin_HELLEU?= <celestin.helleu@maporama.com> writes:
Well, with any database, if I had to insert 20 000 000 record in a table, I=
wouldntt do it in one transaction, it makes very big intermediate file, an=
d the commit at the end is really heavy.
There may be some databases where the above is correct thinking, but
Postgres isn't one of them. The time to do COMMIT, per se, is
independent of the number of rows inserted.
You need to find out where your bottleneck actually is, without any
preconceptions inherited from some other database.
regards, tom lane
On Thu, May 24, 2007 at 05:34:00PM +0200, Célestin HELLEU wrote:
Well, with any database, if I had to insert 20 000 000 record in a table, I wouldntt do it in one transaction, it makes very big intermediate file, and the commit at the end is really heavy.
I would cut the transaction in midi-transaction, of let's say 1000 records.
Postgres does not create an intermediate file and the cost of commit is
independant of the number of statements within the transaction.
Postgres uses a form of MVCC which means you get costs for rollback,
but commit is very cheap.
I beleive your costs are down to the fact that there are 20 000 000
statements. There is a cost per statement, so if you can write your
function to do less statements, you're better off...
FOR all IN (select * from TABLE1)
LOOP
FOR some IN (select * from)
LOOP
INSERT INTO TABLE2 VALUES (all.id, some.id)
END LOOP
END LOOP
I'd replace the whole loop with a single INSERT statement:
INSERT INTO TABLE2 SELECT all.id, some.id FROM all, some WHERE...
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
From each according to his ability. To each according to his ability to litigate.