Bug 107290

Summary:	DDL generator should create indexes on foreign key columns
Product:	[Retired] Red Hat Web Application Framework	Reporter:	Mike Bonnet <mikeb>
Component:	persistence	Assignee:	ccm-bugs-list
Status:	CLOSED WONTFIX	QA Contact:	Jon Orris <jorris>
Severity:	high	Docs Contact:
Priority:	medium
Version:	6.0	CC:	vnasardinov
Target Milestone:	---
Target Release:	---
Hardware:	All
OS:	Linux
Whiteboard:
Fixed In Version:		Doc Type:	Bug Fix
Doc Text:		Story Points:	---
Clone Of:		Environment:
Last Closed:	2005-08-03 18:32:14 UTC	Type:	---
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:
Bug Depends On:
Bug Blocks:	111508

Description Mike Bonnet 2003-10-16 16:04:15 UTC

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.

Comment 1 Vadim Nasardinov 2003-10-20 14:26:01 UTC

See also
https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=107547

Comment 2 Richard Li 2003-12-04 19:28:57 UTC

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.

Comment 3 Vadim Nasardinov 2005-08-03 18:32:14 UTC

stale