On Fri, 25 Mar 2005, Simon Riggs wrote:
> On Fri, 2005-03-25 at 13:47 -0500, Tom Lane wrote:
> > Simon Riggs <simon(at)2ndquadrant(dot)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
> 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.
In response to
pgsql-performance by date
|Next:||From: Matthew T. O'Connor||Date: 2005-03-25 21:12:27|
|Subject: Re: lazy_update_relstats considered harmful (was Re: [PERFORM]|
|Previous:||From: Matthew T. O'Connor||Date: 2005-03-25 21:04:25|
|Subject: Re: pg_autovacuum not having enough suction ?|