Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id CB7A147517F for ; Sat, 27 Apr 2002 17:36:11 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g3RLaCF07082; Sat, 27 Apr 2002 17:36:12 -0400 (EDT) To: "Josh Berkus" Cc: pgsql-sql@postgresql.org Subject: Re: Function won't complete In-reply-to: References: Comments: In-reply-to "Josh Berkus" message dated "Sat, 27 Apr 2002 11:15:21 -0700" Date: Sat, 27 Apr 2002 17:36:11 -0400 Message-ID: <7079.1019943371@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200204/345 X-Sequence-Number: 7609 "Josh Berkus" 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