Re: Transactions within plpgsql functions?

On Tue, Jun 28, 2005 at 09:23:24AM -0500, Celia McInnis wrote:
> On Mon, 27 Jun 2005 15:41:29 -0600, Michael Fuhr wrote
> >
> > If the only trouble is that you're exceeding the limit on the number
> > of operations in a transaction, then you might benefit from using
> > a language other than PL/pgSQL that doesn't increment the command
> > counter for every little thing it does.
>
> Which language? I like the look of plperl but actually didn't use it because
> it is likely that I will be forced to move my work into oracle :-( at some
> point and it looked as if plpgsql is fairly close to oracle's procedural
> language. Any advice here would be much appreciated.

I had in mind PL/Perl, PL/Python, or PL/Tcl -- the standard languages
that PostgreSQL supports -- but there are also third-party
implementations for Ruby, Java, R, etc., and there's always C :-)
But if you anticipate porting away from PostgreSQL and similarity
to PL/pgSQL is an issue, then you might be stuck.

> > Do you really have functions that take days to run, or was that an
> > exaggeration?
>
> Yes, I have functions which take days to run, this was not an exaggeration.
> Being new to postgresql, I may be doing something stupid that's resulting in
> the slowness, but, everything looks legitimate to me... The one thing that I
> have found awkward is dealing with nulls when inserting the results in my
> output table. (I use the COALESCE function for every entry in my insert
> command).

You might want to ask for help in pgsql-performance.  If you're
missing anything that could improve efficiency then the people on
that list might be able to help.

> > What are you doing that performs so many operations?
>
> Calculating sets of quartiles on all kinds of subsets of columns in large
> datasets, etc., and yes, the columns are indexed.

Again, pgsql-performance might be able to help verify that queries
are being performed as efficiently as possible.  Also, if you're
doing statistical analysis, then you might want to check out PL/R.

http://gborg.postgresql.org/project/plr/projdisplay.php

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/