Re: Foreign keys causing conflicts leading toserialization failures

Peter Schuller wrote:
[about a serialization error caused by a foreign key constraint]

>> Transaction 2 now issues an INSERT on "atable". This requires a
>> RowShareLock on the index row of the index on "othertable" that
>> is referenced by the foreign key constraint. But the corresponding
>> index row has changed since the transaction began, hence the error.
>>
>> Transaction 2 needs the index entry to verify that the foreign
>> key constraint is satisfied. It cannot perform the check on the old
>> version of the index row as this might introduce inconsistencies.
>> Being serializable, it must not use the new index entry.
>>
>> One could argue that, as long as the old index entry and the new
>> index entry have the same values, the transaction could safely
proceed.
>
> Yes. :)
>
> Or alternatively, the fact that it was never updated could be
> tracked. I guess you might argue that if one, for example, deleted the
> row and re-created one with another id, that this would in fact break
> referential integrity. Same for updating the relevant column.

Well, the index entry *was* changed because it now points somewhere else
in table "othertable".

> But barring implementation reasons, it seems clear that if the row was
> not dropped and the relevant column was not touched, the ideal
> implementation would allow the INSERT to complete even in a
> serializable transaction.
>
>> I guess it is for ease of implementation, design or performance
reasons
>> that this is not done.
>
> This is what I am wondering. Whether it is done this way due to
> expecation/standard, or as an implementation side effect. In the
> latter case it is fixable.

I don't see how this could break a standard.

Maybe somebody who knows more than me knows the answer :^)

Yours,
Laurenz Albe