Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Foreign keys causing conflicts leading toserialization failures
"Albe Laurenz" <[EMAIL PROTECTED]> writes: > Peter Schuller 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.) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
