From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: pgsql-performance@postgresql.org
Subject: Re: Avoid long-running transactions in a long-running stored
	procedure?
Date: Thu, 14 Feb 2008 17:29:18 -0800
User-Agent: KMail/1.8
Cc: "David Crane" <davidc@donorschoose.org>
References: <41ED0E73B2268F4D9E4081FAB5ED05FD04420FC7@midas.utopiasystems.net>
In-Reply-To: 
 <41ED0E73B2268F4D9E4081FAB5ED05FD04420FC7@midas.utopiasystems.net>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200802141729.18657.josh@agliodbs.com>

David,

> Once per quarter, we need to load a lot of data, which causes many
> updates across the database.  We have an online transaction
> processing-style application, which we really want to stay up during the
> update job.

What you're talking about is "autonomous transactions".  There's someone 
working on them for 8.4, and we may get them next version, but you can't 
have them now.

However, you can write your stored procedures in an external language (like 
PL/Perl, PL/Ruby, PL/Java or PL/Python) and re-connect to your database in 
order to run several separate transactions.  Several users are doing this 
for large ETL jobs. 


-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco