Tim Kientzle wrote:
> ...
> However, I do agree that a failed INSERT should
> not automatically rollback the entire transaction.
> INSERTs often fail due to integrity constraints.
> The common suggestion of doing a test SELECT
> first to determine whether to INSERT or UPDATE
> is simple nonsense; that just creates a race
> condition...
> ...
> PostgreSQL's behavior essentially forces you to
> do BOTH of the following:
> * SELECT first to see whether to INSERT or UPDATE
> * AND be prepared to replay the _entire_ transaction
> when you lose the race.
> You will sometimes lose the race, so the second
> step is not optional.
> ...
How about:
CREATE TABLE foo (key int4, value text);
Then, in the client application,
INSERT INTO foo SELECT 1, 'Hello' WHERE NOT EXISTS (SELECT
key FROM foo WHERE key = 1);
The insert will then insert either (1) a single row if the
row did not already exist, or (2) no rows if the row already
existed. If the latter, you may then update the row using an
update. An UPDATE of zero rows would suggest that the row
was deleted between the time of the INSERT and the time of
the UPDATE, so one would either have to loop or take the
appropriate action for when another user wishes to delete
the row. And even that possibility is dependent upon the
selected TRANSACTION ISOLATION LEVEL. But it would *not*
cause an error in a transaction block forcing a rollback of
the entire transaction.
Mike Mascari