* 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
