Re: data integrity

On Mar 06 Nov 2001 08:24, Janning Vygen wrote:
> Am Dienstag,  6. November 2001 00:17 schrieb Martín Marqués:
> > 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. :-)
>
> I dont think that FOR UPDATE helps in this situation, but it might be
> a misunderstanding.
>
> FOR UPDATE locks the rows of the table for an update. right. but it
> removes the lock if the transaction commits.
>
> In a web based application you cant enforce a transaction between the
> sending of a form and the exceuting of a submit.
>
> Its an application problem: the data i see in my frontend are never
> realtime. they might have change in the last seconds. But who cares.
> If i want to have a value of 8 and another one wants a value of 10
> its a question of whom granting write access.

Web based? Great! PHP?
If you already have 7 saved, then make a CASE statment in your query, and
depending on the IF you update or not.
You may want to think about using plpgsql.

Simple. :-)

--
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
-----------------------------------------------------------------