From: chester c young <chestercyoung@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: unnecessary updates
Date: Wed, 30 Oct 2002 08:42:43 -0800 (PST)
Message-ID: <20021030164243.79844.qmail@web12701.mail.yahoo.com> (raw)
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/
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: chestercyoung@yahoo.com
Subject: Re: unnecessary updates
In-Reply-To: <20021030164243.79844.qmail@web12701.mail.yahoo.com>
* 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