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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
