Thanks for the prompt replies!
It sounds like these are variations of the same approach. In our case,
we need to do a lot of comparing against the old data, audit tables and
so forth, so the bulk of the work is in the body of the existing loop
(already coded). So I think keeping that loop body in a stand-alone
stored procedure will be the most efficient for us. And we'll port the
logic outside the loop into a java program, easier for us to schedule
through another existing system.
Those autonomous transactions are gonna be nice, but PostgreSQL is
plenty nice as it is. Progress is good, though.
Thanks,
David Crane
-----Original Message-----
From: Ow Mun Heng [mailto:Ow.Mun.Heng@wdc.com]
Sent: Thursday, February 14, 2008 8:31 PM
To: josh@agliodbs.com
Cc: pgsql-performance@postgresql.org; David Crane
Subject: Re: [PERFORM] Avoid long-running transactions in a
long-runningstored procedure?
On Thu, 2008-02-14 at 17:29 -0800, Josh Berkus wrote:
> 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.
> 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.
>
I actually do it externally via a perl script even, and I'm breaking the
data down to even more than miniscule size.