Re: SQL design pattern for a delta trigger?

On 12/10/07, Colin Wetherbee <cww(at)denterprises(dot)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.