Red Hat Bugzilla – Bug 107290
DDL generator should create indexes on foreign key columns
Last modified: 2007-04-18 12:58:30 EDT
From Bugzilla Helper: User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030701 Galeon/1.3.9 Description of problem: Missing indexes on foreign key columns are a known cause of deadlocks (see http://www.ixora.com.au/q+a/0010/26231626.htm). The DDL generator should be automatically creating an index every time it adds a foreign key constraint to a column. Version-Release number of selected component (if applicable): How reproducible: Sometimes Steps to Reproduce: If multiple sessions are accessing the same table, deadlocks could occur in Oracle (and possibly also in Postgres, see http://ccm-installer.boston.redhat.com/junit/rickshaw/36920/postgres/cms-internal/com.arsdigita.populate.cms.PopulateSuite.html). It's not 100% clear that the lack of indexes are causing these problems, but it seems likely. Actual Results: Deadlocks Expected Results: No deadlocks Additional info: Marking this as a high priority because these deadlocks cause the system to break in unexpected ways, and they are difficult to debug if you haven't seen them before. Many thanks to David Lutterkort and Aram Kananov for their assistance in diagnosing this problem.
See also https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=107547
Apparently, it's not 100% of the time you want to create the FK indices, so we'll want an opt-out/opt-in solution, depending on whether ultimately the default is opt-out or opt-in.
stale