Re: unnecessary updates - Mailing list pgsql-sql

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)