Re: [SQL] unnecessary updates

I don't know the answer to the question about what MVCC does with no-change updates, but I assume it processes them as normal.

It seems like a broken app that processes updates for records that were not touched. I use client side javascript to toggle a checkbox if the record was touched using the onChange for each widget. The server ignores records without the checkbox checked.

OK, so assuming you are stuck with what you have, consider a before trigger that goes through all the relatts and compares old to new. If it finds no changes, it returns without doing anything. This costs something, but may cost less than the increases frequency of vacuums you might need without it??

THis kind of brings up the "feature" some brand X dbms have which is the UPDATED keyword, something like

IF UPDATED(mycolumn)

which you can use in stored procedures to do something only if a field was updated. We have to explicitly compare OLD to NEW, after (in pltcl anyway) making sure the OLD and/or NEW variable even exist, since they might not if the value is/was NULL. This wouldn't solve your problem, but would make my suggestion easier to implement.

Ian A. Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
(253) 798-3549
iharding(at)tpchd(dot)org

"Objection! Incompetent, irrelevant and immaterial!"

- Hamilton Burger

>>> chester c young <chestercyoung(at)yahoo(dot)com> 10/30/02 08:42AM >>>
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