Re: data integrity (fwd)

> Date: Tue, 6 Nov 2001 12:13:34 -0300 (CLST)
> From: Alvaro Herrera <alvherre(at)atentus(dot)com>
>
> On Mon, 5 Nov 2001, Jelle Ouwerkerk wrote:
>
> > Problem: How are people dealing with data integrity issues such as stale
> > data when writing web-based apps? For instance:
> [...]
>
> > In some scenarios, user B should be prevented from updating.
>
> Maybe you can do something like this:
>
> user A SELECTs the data, and the web app caches the fields.
> user B does the same thing.
> user B changes some fields and commits.
> user A changes some fields and tries to commit. His webapp SELECTs the
> data again and checks against its cache.
> Since the data is different, the webapp presents him the three versions
> and lets him choose.
>
> The idea is to check every time you are going to commit the data to see
> if it changed while you weren't looking. If it did, tell him so and let
> him decide.

Right, but the 'check to see if anything has changed' is easier said than
done. It means re-selecting using the original SQL (when the second update
arrives) and deciding whether or not a merge will result in
inconsistent/faulty data.

What constitutes an acceptable merge? In CVS this is probably pretty
cut-and-dry, but I'm not so sure in this situation. Ideally, the second
update is modifying a field(s) that has not changed, but what if
something else in the row has changed? Is it acceptable that user A and
user B are modifying different fields of the same row? I'm getting the
impression that the answer lies at the application-level (need context)
and cannot be solved in a generic way at the application-server-level.

The idea of having to cache session results for every read of the database
seems like a major burden.

...Jelle

p.s. I apologize that this discussion isn't postgres-specific.