Re: Working around spurious unique constraint errors due to SERIALIZABLE bug

* Albe Laurenz:

> SELECT COUNT(id) INTO i2 FROM a WHERE id = i;
> IF i2 = 0 THEN
> /* This INSERT will never throw an exception if the
> transactions are truly serialized */
> INSERT INTO a (id) VALUES (i);
> RETURN TRUE;
> ELSE
> RETURN FALSE;
> END IF;

> This is what you are talking about, right?

Yes.

> I am not sure what exactly you mean by retrying the transaction in
> Session A. Even on a second try A would not be able to insert the
> duplicate key. But at least there would not be an error:

I often need to obtain the automatically generated primary key in both
cases (with and without INSERT).

> The best way to work around a problem like this is to write
> code that does not assume true serializability, for example:
>
> BEGIN
> INSERT INTO a (id) VALUES (i);
> RETURN TRUE;
> EXCEPTION
> WHEN unique_violation THEN
> RETURN FALSE;
> END;

Oh, since when does this perform an implicit snapshot? I haven't
noticed this before.

The drawback is that some of the side effects of the INSERT occur
before the constraint check fails, so it seems to me that I still need
to perform the select.

My main concern is that the unqiue violation could occur for another
reason (which would be a bug), and I want to avoid an endless loop in
such cases. But if it's possible to isolate this type of error
recovery to a single statement, this risk is greatly reduced.

--
Florian Weimer <fweimer(at)bfk(dot)de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99