Re: unnecessary updates

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(at)perrin(dot)socsci(dot)unc(dot)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
>