Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id B19A9632662
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 29 Dec 2009 18:59:18 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 15943-03 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 29 Dec 2009 22:59:07 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from col0-omc2-s15.col0.hotmail.com (col0-omc2-s15.col0.hotmail.com
	[65.55.34.89])
	by mail.postgresql.org (Postfix) with ESMTP id E7AC36324D2
	for <pgsql-sql@postgresql.org>; Tue, 29 Dec 2009 18:59:07 -0400 (AST)
Received: from COL122-W49 ([65.55.34.73]) by col0-omc2-s15.col0.hotmail.com
	with Microsoft SMTPSVC(6.0.3790.3959);
	Tue, 29 Dec 2009 14:59:06 -0800
Message-ID: <COL122-W493DEB26AA345A7DCE0058CD7A0@phx.gbl>
Content-Type: multipart/alternative;
	boundary="_5b9caa96-0354-482d-a1c3-4db8c99c228e_"
X-Originating-IP: [81.151.75.171]
From: Andrew Hall <andrewah@hotmail.com>
To: PostgreSQL Mailing List <pgsql-sql@postgresql.org>
Subject: Foreign key - Indexing & Deadlocking.
Date: Tue, 29 Dec 2009 22:59:06 +0000
Importance: Normal
MIME-Version: 1.0
X-OriginalArrivalTime: 29 Dec 2009 22:59:06.0754 (UTC)
	FILETIME=[86196620:01CA88DA]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.184 tagged_above=-10 required=5
	tests=BAYES_40=-0.185, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200912/85
X-Sequence-Number: 33523

--_5b9caa96-0354-482d-a1c3-4db8c99c228e_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Hi=2C

I'm porting some Oracle software to PostgreSQL=2C & have a question about b=
est practices with foreign keys.

In the Oracle environment=2C you're basically obliged to index all foreign =
keys (more specifically foreign key columns should appear on the leading ed=
ge of an index) due to the way that it manages locks.=20

Failure to index foreign keys in this way in Oracle often causes deadlock s=
ituations.

Although I could no find a mention of anything similar in the postgreSQL do=
cumentation=2C I just wanted to check whether or not the same was true of P=
ostgreSQL to be on the safe side.

Thanks=2C

Andrew.
 		 	   		 =20
_________________________________________________________________
Use Hotmail to send and receive mail from your different email accounts
http://clk.atdmt.com/UKM/go/186394592/direct/01/=

--_5b9caa96-0354-482d-a1c3-4db8c99c228e_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<style><!--
.hmmessage P
{
margin:0px=3B
padding:0px
}
body.hmmessage
{
font-size: 10pt=3B
font-family:Verdana
}
--></style>
</head>
<body class=3D'hmmessage'>
Hi=2C<br><br>I'm porting some Oracle software to PostgreSQL=2C &amp=3B have=
 a question about best practices with foreign keys.<br><br>In the Oracle en=
vironment=2C you're basically obliged to index all foreign keys (more speci=
fically foreign key columns should appear on the leading edge of an index) =
due to the way that it manages locks. <br><br>Failure to index foreign keys=
 in this way in Oracle often causes deadlock situations.<br><br>Although I =
could no find a mention of anything similar in the postgreSQL documentation=
=2C I just wanted to check whether or not the same was true of PostgreSQL t=
o be on the safe side.<br><br>Thanks=2C<br><br>Andrew.<br> 		 	   		  <br /=
><hr />Use Hotmail to send and receive mail from your different email accou=
nts. <a href=3D'http://clk.atdmt.com/UKM/go/186394592/direct/01/' target=3D=
'_new'>Find out how.</a></body>
</html>=

--_5b9caa96-0354-482d-a1c3-4db8c99c228e_--