Skip site navigation (1) Skip section navigation (2)

Re: Transactions within plpgsql functions?

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Celia McInnis <celia(at)drmath(dot)ca>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Transactions within plpgsql functions?
Date: 2005-06-28 16:00:42
Message-ID: 20050628160042.GA24477@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-novice
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/

In response to

pgsql-novice by date

Next:From: SeanDate: 2005-06-28 20:20:35
Subject: Sstored Procedures
Previous:From: Celia McInnisDate: 2005-06-28 14:23:24
Subject: Re: Transactions within plpgsql functions?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group