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 7CF31536EC
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 25 Mar 2005 21:10:54 +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 63660-09
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 25 Mar 2005 21:10:51 +0000 (GMT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id AECCA536AA
	for <pgsql-performance@postgresql.org>;
	Fri, 25 Mar 2005 21:10:49 +0000 (GMT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id BCCE63566D; Fri, 25 Mar 2005 13:10:51 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id BAB343562E; Fri, 25 Mar 2005 13:10:51 -0800 (PST)
Date: Fri, 25 Mar 2005 13:10:51 -0800 (PST)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Karim Nassar <karim.nassar@NAU.EDU>,
	Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
	pgsql-performance@postgresql.org
Subject: Re: Delete query takes exorbitant amount of time
In-Reply-To: <1111782529.11750.815.camel@localhost.localdomain>
Message-ID: <20050325130124.U15470@megazone.bigpanda.com>
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>
	<1111766478.11750.762.camel@localhost.localdomain>
	<20050325082101.G4358@megazone.bigpanda.com>
	<1111775050.11750.790.camel@localhost.localdomain>
	<28057.1111776442@sss.pgh.pa.us>
	<1111782529.11750.815.camel@localhost.localdomain>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200503/562
X-Sequence-Number: 11267


On Fri, 25 Mar 2005, Simon Riggs wrote:

> On Fri, 2005-03-25 at 13:47 -0500, Tom Lane wrote:
> > Simon Riggs <simon@2ndquadrant.com> writes:
> > > On Fri, 2005-03-25 at 10:17 -0500, Tom Lane wrote:
> > >>> 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.
> >
> > > Tom had said SQL99 required this; I have pointed out SQL:2003, which
> > > supercedes the SQL99 standard, does not require this.
> >
> > You're reading the wrong part of SQL:2003.  11.8 <referential constraint
> > definition> syntax rule 9 still has the text I quoted.
>
> So, we have this from SQL:2003 section 11.8 p.550
> - 3a) requires us to have an index
> - 9) requires the data types to be "comparable"
>
> In the name of spec-compliance we wish to accept an interpretation of
> the word "comparable" that means we will accept two datatypes that are
> not actually the same.
>
> So we are happy to enforce having the index, but not happy to ensure the
> index is actually usable for the task?

The indexes "usability" only applies to the purpose of guaranteeing
uniqueness which doesn't depend on the referencing type AFAICS.

> > > Leading us back to my original point - what is the benefit of continuing
> > > with having a WARNING when that leads people into trouble later?
> >
> > Accepting spec-compliant schemas.
>
> I definitely want this too - as you know I have worked on documenting
> compliance previously.
>
> Is the word "comparable" defined elsewhere in the standard?

Yes.  And at least in SQL99, there's a bunch of statements in 4.* about
what are comparable.

> Currently, datatypes with similar type categories are comparable and yet
> (in 8.0) will now use the index. So, we are taking comparable to include
> fairly radically different datatypes?

Not entirely. I believe a referenced column of int, and a referencing
column of numeric currently displays that warning, but appears to be
allowed by the spec (as the numeric types are considered mutually
comparable).

> Could it be that because PostgreSQL has a very highly developed sense of
> datatype comparison that we might be taking this to extremes? Would any
> other RDBMS consider two different datatypes to be comparable?

We do have a broader comparable than the spec. However, if we were to
limit it to the spec then many of the implicit casts and cross-type
comparison operators we have would be invalid as well since the comparison
between those types would have to fail as well unless we treat the
comparable used by <comparison predicate> differently.