Message-ID: <20021030164243.79844.qmail@web12701.mail.yahoo.com>
Date: Wed, 30 Oct 2002 08:42:43 -0800 (PST)
From: chester c young <chestercyoung@yahoo.com>
Subject: unnecessary updates
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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/