Re: [HACKERS] INSERT/SELECT and excessive foreign key checks
On 19-aug-2007, at 12:38, Tom Lane wrote: Hack is the right word. People keep proposing variants of the idea that the executor should optimize updates on the basis of examining the query tree to see whether columns changed or not, and they're always wrong. You don't know what else might have been done to the row by BEFORE triggers. but that's something it can check for. if there are BEFORE triggers on the table, don't do it. An additional problem with your proposal is that it fails to consider other changes that might be happening concurrently -- eg, what if some other backend deletes a source row after you copy it, and commits before you do? There would be an interval with no committed row having that FK value, and no one holding a row lock on the referenced PK row, so some third transaction could delete the PK row. so if it checks those FKs being carried over also only once, that would close that hole, right? it would just be nice to not have to disable triggers altogether in this case. there is a person twiddling his/her thumbs while all this checking and re-checking is going on. Lodewijk ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] INSERT/SELECT and excessive foreign key checks
On 19-aug-2007, at 12:38, Tom Lane wrote: An additional problem with your proposal is that it fails to consider other changes that might be happening concurrently -- eg, what if some other backend deletes a source row after you copy it, and commits before you do? then the patch indeed failed, but when I change it to check those carried over FKs also once, it catches it correctly. are there other such issues? or is this kind of optimization not going in no matter what? Lodewijk ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] INSERT/SELECT and excessive foreign key checks
On 21-aug-2007, at 10:55, Alvaro Herrera wrote: It might go in if it's correct. If you have an answer to all the objections then there's no reason not to include it. But I must admit I didn't understand what was your answer to the above objection; care to rephrase? sorry, egg on my face, testing error. the revised patch doesn't catch the case of another backend deleting referenced tuples. I'll work on it some more. Lodewijk ---(end of broadcast)--- TIP 6: explain analyze is your friend
