Thread: Commit within a PL/PGSQL procedure
I am trying to develop a special function to read an entire database
and create a new database from it according to various complicated rules.
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.
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?
Regards,
Harry.
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
Josh Berkus writes:
> Harry,
>
[ SNIP]
Many thanks for your reply...
> 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.
The reason I say it runs out of memory is the message:
ERROR: Memory exhausted in AllocSetAlloc(36)
in the log file, and I am running 'top' and see the process hit 512MB at
about this point!
>
> 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.
In fact my *test* function consists of a simple scan through a database,
and 3 INSERTs into a new table for each record in the original, where
the values inserted are derived from values in the original.
>
> 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?)
I was begining to realise that I would have to code it up in Perl and
run it from the "front-end".
And I'm a long way away in expertese to the point that I could
contribute to the functionality! <grin>
Regards,
Harry.
Harry, > The reason I say it runs out of memory is the message: > ERROR: Memory exhausted in AllocSetAlloc(36) > in the log file, and I am running 'top' and see the process hit 512MB at > about this point! Hmmm ... this is highly unlikely to be becuase of the transaction. When Postgres runs out of available RAM, it starts swapping to disk ... which can take hours, but will *not* produce that error. What did you set your shared_buffers and sort_mem to? What OS are you on? > In fact my *test* function consists of a simple scan through a database, > and 3 INSERTs into a new table for each record in the original, where > the values inserted are derived from values in the original. Postgresql should be able to handle this regardless of table size, it's just a question of speed. -- Josh Berkus Aglio Database Solutions San Francisco
Harry Broomhall <harry.broomhall@uk.easynet.net> writes:
> The reason I say it runs out of memory is the message:
> ERROR: Memory exhausted in AllocSetAlloc(36)
> in the log file, and I am running 'top' and see the process hit 512MB at
> about this point!
Sounds like running out of memory to me too ;-)
We have found (and fixed) various intra-function-call memory leaks in
plpgsql in the past, and I wouldn't be surprised to find more. Which
PG version is this exactly? If it's current, can you supply a complete
test case to cause the problem? I'd be happy to look into it.
regards, tom lane
Josh Berkus writes:
> Harry,
>
> > The reason I say it runs out of memory is the message:
> > ERROR: Memory exhausted in AllocSetAlloc(36)
> > in the log file, and I am running 'top' and see the process hit 512MB at
> > about this point!
>
> Hmmm ... this is highly unlikely to be becuase of the transaction. When
> Postgres runs out of available RAM, it starts swapping to disk ... which can
> take hours, but will *not* produce that error.
At the above mentioned point the swap was about 34% used (normaly it
is about 3%). It takes about an hour for it to get to this point.
>
> What did you set your shared_buffers and sort_mem to? What OS are you on?
FreeBSD 5.0-RELEASE with PostgreSQL 7.3.3
shared_buffers not set - so presumably default of 64
sort_mem not set - so presumably default of 1024
I haven't fiddled with the conf file on the grounds that I have no real
idea of how to do so - and take the view that I would probably make it worse!
>
> > In fact my *test* function consists of a simple scan through a database,
> > and 3 INSERTs into a new table for each record in the original, where
> > the values inserted are derived from values in the original.
>
> Postgresql should be able to handle this regardless of table size, it's just a
> question of speed.
The source file is about 6.4 ^6 records, and the system dies at about
the 1 million mark.
Regards,
Harry.
Harry Broomhall <harry.broomhall@uk.easynet.net> writes:
> In fact my *test* function consists of a simple scan through a database,
> and 3 INSERTs into a new table for each record in the original, where
> the values inserted are derived from values in the original.
> The source file is about 6.4 ^6 records, and the system dies at about
> the 1 million mark.
Now that I think twice, do you have foreign key linkages in the
tables being inserted into? If so, the most likely explanation is just
that the list of deferred trigger events is getting too large. This is
a known deficiency on our TODO list (it should be possible to shove the
list out to disk when it gets too large).
regards, tom lane
>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
Tom Lane writes:
> Harry Broomhall <harry.broomhall@uk.easynet.net> writes:
> > In fact my *test* function consists of a simple scan through a database,
> > and 3 INSERTs into a new table for each record in the original, where
> > the values inserted are derived from values in the original.
> > The source file is about 6.4 ^6 records, and the system dies at about
> > the 1 million mark.
>
> Now that I think twice, do you have foreign key linkages in the
> tables being inserted into?
Spot on! The new table has a field that references an ID in the
original table.
> If so, the most likely explanation is just
> that the list of deferred trigger events is getting too large. This is
> a known deficiency on our TODO list (it should be possible to shove the
> list out to disk when it gets too large).
Any way round this?
Regards,
Harry.
Harry Broomhall <harry.broomhall@uk.easynet.net> writes:
> Tom Lane writes:
>> If so, the most likely explanation is just
>> that the list of deferred trigger events is getting too large. This is
>> a known deficiency on our TODO list (it should be possible to shove the
>> list out to disk when it gets too large).
> Any way round this?
Not much :-(. AFAIR there is no way to get the trigger list to be
processed while you're inside your function --- you have to return to
the outer command loop. (The correctness of this is debatable, but
that's how things work at the moment.) So you must either break your
work into multiple function calls, or not use foreign keys
while you're building the new table. You could think about adding
the FK constraint after you're done inserting data.
regards, tom lane
Does anyone know of any tools to help in the process of verbally and graphically documenting a PostgreSql database and it's associated application. I want/need to be able to show the relationships among tables and views. I'm currently using DIA which is somewhat similar to Visio but it is a long drawn out process. TIA Julie
Am Mit, 2003-06-18 um 19.57 schrieb Juliet May: > Does anyone know of any tools to help in the process of verbally and > graphically documenting a PostgreSql database and it's associated > application. I want/need to be able to show the relationships among tables > and views. I'm currently using DIA which is somewhat similar to Visio but it > is a long drawn out process. I'm writing such an app at the moment. It's based on GnomeCanvas for tables to be dragged around. References are displayed as arrows, columns (actually displayed as rows) can be teared off and dropped somewhere else, to create references etc. At the moment I'm too busy but I hope to continue soon. For the moment you could use pgaccess. bye -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de
Juliet, > > Does anyone know of any tools to help in the process of verbally and > > graphically documenting a PostgreSql database and it's associated > > application. I want/need to be able to show the relationships among tables > > and views. I'm currently using DIA which is somewhat similar to Visio but it > > is a long drawn out process. There's some kind of a db documentation tool on GBORG: gborg.postgresql.org -- -Josh Berkus Aglio Database Solutions San Francisco
Hello, are you using the UML diagrams????, you could use the postgresautodoc.pl look for it in freshmeat... it generates an UML diagram, a docbook doc and mmmm, i don't remember if a html file... Regards, Jorge Ramirez. http://www.chicolinux.com.mx/ El mié, 18 de 06 de 2003 a las 12:57, Juliet May escribió: > Does anyone know of any tools to help in the process of verbally and > graphically documenting a PostgreSql database and it's associated > application. I want/need to be able to show the relationships among tables > and views. I'm currently using DIA which is somewhat similar to Visio but it > is a long drawn out process. > > TIA > > Julie > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
Thanks for the suggestion. I don't have time to experiment with it today, but it definately looks helpful. Right about now I am wishing I had asked my question before I used DIA to diagram out the majority of my database. And yes, I did use the UML diagram tool, although I'm guessing I probably didn't use it correctly. Thanks to everyone else for their suggestions too. <snip> ----- Original Message ----- From: "Jorge Ramirez" <jramirez@gvicda.com.mx> > Hello, are you using the UML diagrams????, you could use the > postgresautodoc.pl > look for it in freshmeat... > > it generates an UML diagram, a docbook doc and mmmm, i don't remember if > a html file... </snip>
http://freshmeat.net/projects/postgresql_autodoc/?topic_id=66 Jorge Ramirez wrote: >Hello, are you using the UML diagrams????, you could use the >postgresautodoc.pl >look for it in freshmeat... > >it generates an UML diagram, a docbook doc and mmmm, i don't remember if >a html file... > >Regards, >Jorge Ramirez. >http://www.chicolinux.com.mx/ > > > >El mié, 18 de 06 de 2003 a las 12:57, Juliet May escribió: > > >>Does anyone know of any tools to help in the process of verbally and >>graphically documenting a PostgreSql database and it's associated >>application. I want/need to be able to show the relationships among tables >>and views. I'm currently using DIA which is somewhat similar to Visio but it >>is a long drawn out process. >> >>TIA >> >>Julie >> >>
Am Mit, 2003-06-18 um 19.57 schrieb Juliet May: > Does anyone know of any tools to help in the process of verbally and > graphically documenting a PostgreSql database and it's associated > application. I want/need to be able to show the relationships among tables > and views. I'm currently using DIA which is somewhat similar to Visio but it > is a long drawn out process. I'm writing such an app at the moment. It's based on GnomeCanvas for tables to be dragged around. References are displayed as arrows, columns (actually displayed as rows) can be teared off and dropped somewhere else, to create references etc. At the moment I'm too busy but I hope to continue soon. For the moment you could use pgaccess. bye -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de
Tom Lane writes:
> Harry Broomhall <harry.broomhall@uk.easynet.net> writes:
> > Tom Lane writes:
> >> If so, the most likely explanation is just
> >> that the list of deferred trigger events is getting too large. This is
> >> a known deficiency on our TODO list (it should be possible to shove the
> >> list out to disk when it gets too large).
>
> > Any way round this?
>
> Not much :-(. AFAIR there is no way to get the trigger list to be
> processed while you're inside your function --- you have to return to
> the outer command loop. (The correctness of this is debatable, but
> that's how things work at the moment.) So you must either break your
> work into multiple function calls, or not use foreign keys
> while you're building the new table. You could think about adding
> the FK constraint after you're done inserting data.
FYI:
I removed the foreign key from the table definition and tried again
from scratch.
The function went through with no problems, and 'top' showed that the
backend barely broke sweat, keeping a low memory useage throughout.
So I've now started to rewrite the thing with this experience in mind.
Many thanks for your insight and help.
Regards,
Harry.