Date: Fri, 1 Nov 2002 15:27:28 -0600
From: "Ross J. Reedstrom" <reedstrm@rice.edu>
To: Andrew Perrin <clists@perrin.socsci.unc.edu>
Cc: chester c young <chestercyoung@yahoo.com>,
	pgsql-sql@postgresql.org
Subject: Re: unnecessary updates
Message-ID: <20021101212728.GB967@wallace.ece.rice.edu>
Mail-Followup-To: "Ross J. Reedstrom" <reedstrm@ece.rice.edu>,
	Andrew Perrin <clists@perrin.socsci.unc.edu>,
	chester c young <chestercyoung@yahoo.com>, pgsql-sql@postgresql.org
References: <20021030164243.79844.qmail@web12701.mail.yahoo.com>
	<Pine.LNX.4.21.0210301300310.690-100000@perrin.socsci.unc.edu>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <Pine.LNX.4.21.0210301300310.690-100000@perrin.socsci.unc.edu>
User-Agent: Mutt/1.4i

Another way to approach this would be to add a trigger to your table
in the database, that rejects updates that don't change any values.
You'd basically have to hard code that same logic (new.a != old.a or
new.b != old.b ...) and it'd fire on every update, so you're talking
about trading computational cycles for savings in diskspace (and vacuum
time). Like all things, it's a tradoff. Only way to tell for your case is
to try it, I'd guess. It'd be kind of interesting to know if this would
be useful, but you'd need to write a tool to analyze your tables before
vacuum, to determine if the dead tuples differ from the current values
(or from each other).

Ross

On Wed, Oct 30, 2002 at 01:02:26PM -0500, Andrew Perrin wrote:
> One strategy is to use some sort of middleware that takes care of this. On
> a project I did a few years ago, I used a perl module that read the record
> from Postgres and made it into a perl object. The object contained a
> variable, "changed", that reflected whether anything had actually changed
> in the object. Finally, there was an object method put() that took care of
> updating the database. put() checked the changed property and simply
> silently finished unless changed was true.
> 
> ap
> 
> ----------------------------------------------------------------------
> Andrew J Perrin - http://www.unc.edu/~aperrin
> Assistant Professor of Sociology, U of North Carolina, Chapel Hill
> clists@perrin.socsci.unc.edu * andrew_perrin (at) unc.edu
> 
> 
> On Wed, 30 Oct 2002, chester c young wrote:
> 
> > When doing database work over the web, especially when many records are
> > on one page, *many* updates get posted to pg that do not change the
> > record.  Eg, the page may contain 50 records, the user changes 1, and
> > submits.
> > 
> > I assume that a no-change update takes the same resources as a "real"
> > update, ie, a new block is allocated to write the record, the record
> > written, indicies are rerouted to the new block, and the old block
> > needs to be vacuumed later.  Is this true?
> > 
> > In SQL, the only way I know to prevent this thrashing is to write the
> > update with an elaborate where clause, eg, "update ... where pk=1 and
> > (c1!='v1' or c2!='v2' or ... )".  This adds cost both to the app server
> > and to pg - is the cost justified?
> > 
> > Finally, is there anyway to flag pg to ignore no-change updates?  This
> > seems to me to me the most efficient way of handling the needless work.
> > 
> > thanks
> > chester
> > 
> > __________________________________________________
> > Do you Yahoo!?
> > HotJobs - Search new jobs daily now
> > http://hotjobs.yahoo.com/
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> > 
> > http://archives.postgresql.org
> > 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)