Re: data integrity

On Lun 05 Nov 2001 18:06, you wrote:
> Hi
>
> I'm using postgresql to manage a web site.
>
> Problem: How are people dealing with data integrity issues such as stale
> data when writing web-based apps? For instance:
>
> The value of X is 7.
>
> Step (1) User A selects X.
> Step (2) User B selects X.
> Step (3) User A updates X to 8.
> Step (4) User B updates X to 10 under the assumption that X is still 7.

Lock it with a SELECT ...... FOR UPDATE. The rows that are going to be
updated will be locked, but not the entire table. :-)

> In some scenarios, user B should be prevented from updating.
>
> Solutions (that I'm considering):
>
> (1) Timestamp: Have a timestamp for every row in every table, which
> automatically changes when a row is inserted or updated, or
>
> (2) Cache: Similarly, cache the user's selected data on the server side
> (every tuple from the original select) and compare when the user attempts
> to update, or
>
> (3) Squash: modify only those fields in the row that need updating
> (instead of the entire row) and whatever happens happens.
>
> Has anyone solved this 'stale data' problem before?
>
> Does this fall into a family of problems that has an existing (trivial)
> solution? Also, is it the responsibility of the application server to deal
> with the scenario above or should the application itself be responsible?

saludos... ;-)

--
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués                  |        mmarques@unl.edu.ar
Programador, Administrador, DBA |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------