Re: SQL design pattern for a delta trigger?

On 12/10/07, Colin Wetherbee <cww@denterprises.org> wrote:
> Vivek Khera wrote:
> > On Dec 10, 2007, at 5:04 PM, Colin Wetherbee wrote:

> >> IF (a query matching your old data returns rows) THEN UPDATE with
> >> your new data ELSE INSERT your new data

> > Still exists race condition.  Your race comes from testing existence,
> >  then creating/modifying data afterwards.  You need to make the
> > test/set atomic else you have race.

> I guess when I wrote that the algorithm would have to be implemented in
> an atomic manner, it fell on deaf ears.

The problem is that there isn't a good atomic method for that order of
operations, short of locking the entire table first. A concurrent
transaction might insert a row after your test but before your own
INSERT. Even a SERIALIZABLE transaction won't help, as PostgreSQL
doesn't implement predicate locking.

That's why the example in the docs is a loop with result checking on
both operations, and requires a UNIQUE constraint to work correctly.

If high concurrency isn't a concern, table locking is the simpler approach.