Re: Commit within a PL/PGSQL procedure
От | Mel Jamero |
---|---|
Тема | Re: Commit within a PL/PGSQL procedure |
Дата | |
Msg-id | 002101c33546$87d211e0$1b06a8c0@CMPMEL обсуждение исходный текст |
Ответ на | Re: Commit within a PL/PGSQL procedure (Josh Berkus <josh@agliodbs.com>) |
Список | pgsql-novice |
>Now I could use Perl or similar to do this, but I was under the >impression that doing it on "the backend" in PL/PGSQL was the most efficient. About 2 years ago, I was under the same impression. We "took out" a lot of our stored procedures and functions and converted it -- first into PERL modules and then later on into C modules. The improvements were remarkable, for each of the conversion stages ("db backend" to PERL and then PERL to C). I have no idea about the memory problem. HTH. -- Mel -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Josh Berkus Sent: Tuesday, June 17, 2003 11:41 PM To: Harry Broomhall; pgsql-novice@postgresql.org Subject: Re: [NOVICE] Commit within a PL/PGSQL procedure Harry, > The problem I have come up against is that such a function is treated > as a single transaction, so if the database is large the memory gets > eaten up before it finishes. > > I'm told that in the Oracle equivalent system one can insert COMMIT > statements to aleviate the problem. > > Is there any way under PgSQL to do the same? Or is there some other > 'trick' to achieve this? No. PostgreSQL's design strategy is that each function is "atomic", or its own transaction. Also, is is unlikely that your problem is running out of memory ... far more likely, later steps in your procedure are suffering from the lack of VACUUM after earlier steps. And VACUUM may not be done inside a function. I suggest that you break up the complicated rules into 5-20 seperate PL/pgSQL functions, and then call them with a Perl DBI script, with VACUUMs in between. I agree, it would be nice to be able to encapsulate this all in the database, but PL/pgSQL and our procedureal language functionality needs some more work ... (volunteers?) -- Josh Berkus Aglio Database Solutions San Francisco ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
В списке pgsql-novice по дате отправления: