Thread: Very big transaction in a stored procedure : how can i commit in the middle of it ?
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
Re: 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.
Attachment
Re: Very big transaction in a stored procedure : how can i commit in the middle of it ?
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 verybig 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
Re: Very big transaction in a stored procedure : how can i commit in the middle of it ?
=?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
Re: Very big transaction in a stored procedure : how can i commit in the middle of it ?
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 makesvery 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/ > From each according to his ability. To each according to his ability to litigate.