Another way to approach this would be to add a trigger to your table in the database, that rejects updates that don't change any values. You'd basically have to hard code that same logic (new.a != old.a or new.b != old.b ...) and it'd fire on every update, so you're talking about trading computational cycles for savings in diskspace (and vacuum time). Like all things, it's a tradoff. Only way to tell for your case is to try it, I'd guess. It'd be kind of interesting to know if this would be useful, but you'd need to write a tool to analyze your tables before vacuum, to determine if the dead tuples differ from the current values (or from each other).
Ross On Wed, Oct 30, 2002 at 01:02:26PM -0500, Andrew Perrin wrote: > 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 > [EMAIL PROTECTED] * 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 > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
