X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 40BBA5360E
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 25 Mar 2005 16:02:37 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 12773-05
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 25 Mar 2005 16:02:32 +0000 (GMT)
Received: from cmailg1.svr.pol.co.uk (cmailg1.svr.pol.co.uk [195.92.195.171])
	by svr1.postgresql.org (Postfix) with ESMTP id 3DEEA53551
	for <pgsql-performance@postgresql.org>;
	Fri, 25 Mar 2005 16:02:31 +0000 (GMT)
Received: from modem-1391.llama.dialup.pol.co.uk ([217.135.181.111]
	helo=192.168.0.102) by cmailg1.svr.pol.co.uk with esmtp (Exim 4.14)
	id 1DErGX-00087K-Eb; Fri, 25 Mar 2005 16:02:29 +0000
Subject: Re: Delete query takes exorbitant amount of time
From: Simon Riggs <simon@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Karim Nassar <karim.nassar@NAU.EDU>,
	Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
	pgsql-performance@postgresql.org
In-Reply-To: <23515.1111763837@sss.pgh.pa.us>
References: <1111709457.9085.127.camel@k2.cet.nau.edu>
	<2630.1111711927@sss.pgh.pa.us>
	<1111715131.9085.174.camel@k2.cet.nau.edu>
	<3750.1111715318@sss.pgh.pa.us> <4243879B.10200@familyhealth.com.au>
	<20845.1111733900@sss.pgh.pa.us>
	<1111763448.11750.730.camel@localhost.localdomain>
	<23515.1111763837@sss.pgh.pa.us>
Content-Type: text/plain
Organization: 2nd Quadrant
Date: Fri, 25 Mar 2005 16:01:18 +0000
Message-Id: <1111766478.11750.762.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.0.2 (2.0.2-3) 
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.048 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200503/546
X-Sequence-Number: 11251

On Fri, 2005-03-25 at 10:17 -0500, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > I vote to make this an ERROR in 8.1 - I see little benefit in allowing
> > this situation to continue.
> 
> Other than spec compliance, you mean?  SQL99 says
> 
>             ... The declared type of each referencing column shall be
>             comparable to the declared type of the corresponding referenced
>             column.
> 
> It doesn't say that it has to be indexable, and most definitely not that
> there has to be an index.

specs at dawn, eh?

Well, SQL:2003 Foundation, p.550 clause 3a) states that the the
<reference columns> in the referencing table must match a unique
constraint on the referenced table, or the PRIMARY KEY if the columns
are not specified. Either way, the referenced columns are a unique
constraint (which makes perfect sense from a logical data perspective).

We implement unique constraints via an index, so for PostgreSQL the
clause implies that it must refer to an index. 

touche, Monsieur Lane and Happy Easter :-)

But even without that, there is little benefit in allowing it...

WARNING -> ERROR, please.

Best Regards, Simon Riggs