From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Karim Nassar <karim.nassar@NAU.EDU>
Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: Delete query takes exorbitant amount of time
Date: Fri, 25 Mar 2005 13:10:51 -0800 (PST)
Message-ID: <20050325130124.U15470@megazone.bigpanda.com> (raw)
In-Reply-To: <1111782529.11750.815.camel@localhost.localdomain>
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>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sszabo@megazone.bigpanda.com, simon@2ndquadrant.com, tgl@sss.pgh.pa.us, karim.nassar@NAU.EDU, chriskl@familyhealth.com.au
Subject: Re: Delete query takes exorbitant amount of time
In-Reply-To: <20050325130124.U15470@megazone.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox