Re: Foreign keys causing conflicts leading toserialization failures

Tom Lane wrote:
> >> 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.
>
> Actually, I think it does, because we went to great lengths to cause
> this case to error out. It would be much simpler, code-wise, if the
> RI checks just always used a current snapshot and didn't worry about
> whether serializability had been violated.
>
> (Albe's description of the implementation is largely fiction, but the
> conclusion is accurate: we throw error if the referenced PK row has been
> updated since the serializable transaction started. The exact nature
> of the update is not considered.)

I am aware that I know nothing of the implementation and only can
describe the behaviour...

Of course a serializable transaction cannot just use the current index
entry for verifying referential integrity, because then it might not
behave consistently with the transaction snapshot.

What I mean is: if the serializable transaction went out of its way
to check if the update it wants to make is both consistent with
its snapshot and the current index row, it should not violate anything
to allow that update. The index entry would not be changed in that case.

Yours,
Laurenz Albe