From: Simon Riggs <simon@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>
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 20:28:49 +0000
Message-ID: <1111782529.11750.815.camel@localhost.localdomain> (raw)
In-Reply-To: <28057.1111776442@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>
<1111766478.11750.762.camel@localhost.localdomain>
<20050325082101.G4358@megazone.bigpanda.com>
<1111775050.11750.790.camel@localhost.localdomain>
<28057.1111776442@sss.pgh.pa.us>
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?
> > 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?
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?
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?
Please consider this.
Best Regards, Simon Riggs
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: simon@2ndquadrant.com, tgl@sss.pgh.pa.us, sszabo@megazone.bigpanda.com, karim.nassar@NAU.EDU, chriskl@familyhealth.com.au
Subject: Re: Delete query takes exorbitant amount of time
In-Reply-To: <1111782529.11750.815.camel@localhost.localdomain>
* 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