Re: Function won't complete

"Josh Berkus" <josh@agliodbs.com> writes:
> Analysis:
> By upping the debug level and tailing the logs, I could see what was
> happening.  Because all of the activity was taking place inside a
> single function, the database had to be prepared to roll it all back as
> a single transaction.  As a result, with each succeeding operation
> within the function, the calls to the transaction log got longer and
> slower.  

This analysis is nonsense, because PG does not rely on WAL for
transaction rollback, and the amount of WAL activity is *not*
proportional to transaction length.  (At least not since 7.1.2.)

It might be that the real issue is growth of the list of pending
triggers, if you have deferred triggers (eg RI triggers) on the
relations you are updating.  Or it could be something else; since you
say the thing remains I/O-bound, it seems like we must be talking about
tuple access or updates somewhere.  (If VACUUM between steps helps, it
might just be accumulation of dead tuples.)  However, until you drop
your focus on the WAL we'll not find out what's really the bottleneck...
        regards, tom lane