Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



* 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@xxxxxx>
BFK edv-consulting GmbH       http://www.bfk.de/
Kriegsstraße 100              tel: +49-721-96201-1
D-76133 Karlsruhe             fax: +49-721-96201-99

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux