From: Ross J. Reedstrom <reedstrm@rice.edu>
To: Andrew Perrin <clists@perrin.socsci.unc.edu>
Cc: chester c young <chestercyoung@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: unnecessary updates
Date: Fri, 1 Nov 2002 15:27:28 -0600
Message-ID: <20021101212728.GB967@wallace.ece.rice.edu> (raw)
In-Reply-To: <Pine.LNX.4.21.0210301300310.690-100000@perrin.socsci.unc.edu>
References: <20021030164243.79844.qmail@web12701.mail.yahoo.com>
<Pine.LNX.4.21.0210301300310.690-100000@perrin.socsci.unc.edu>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: reedstrm@rice.edu, clists@perrin.socsci.unc.edu, chestercyoung@yahoo.com
Subject: Re: unnecessary updates
In-Reply-To: <20021101212728.GB967@wallace.ece.rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox