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

Craig Ringer wrote:
> > 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.
>
> If you really can't afford the INSERT side effects and can't redesign
> your code to be tolerant of them, you can always lock the table before
> an INSERT.

I wonder what could be the side effects of an INSERT that causes an error
that is caught in a plpgsql exception block.

What happens behind the scenes when an exception is caught is that the
transaction is rolled back to a savepoint that was set at the beginning
of the block.

So all changes to the database that were caused as side effects of the INSERT,
for example triggers, will be rolled back as well.

The only side effects that would remain could be caused by things that
go outside the limits of the database, e.g. modify files on the database
server or perform trigger based replication to another database.
Everything that is not under MVCC control, for short.

Is that the problem here?

On another line:
The original question asked was "how can I tell an error that is caused
by incomplete isolation from another error?"

If you have a code segment like
SELECT COUNT(id) INTO i2 FROM a WHERE id = i;
IF i2 = 0 THEN
INSERT INTO a (id) VALUES (i);
END IF;

Then you can be certain that any "unique_violation" thrown here must
be a serialization problem (if the only unique contraint is on "id").

Yours,
Laurenz Albe