Re: [SQL] unnecessary updates - Mailing list pgsql-general

Ideally, you could write some generic function, either in the server
or in the application, that takes two records and returns true/false
if they are the same/different.

---------------------------------------------------------------------------

Ian Harding wrote:
> 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@tpchd.org
>
> "Objection!  Incompetent, irrelevant and immaterial!"
>
>        - Hamilton Burger
>
> >>> chester c young <chestercyoung@yahoo.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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073