On Fri, 2005-03-25 at 08:23 -0800, Stephan Szabo wrote: > On Fri, 25 Mar 2005, Simon Riggs wrote: > > > On Fri, 2005-03-25 at 10:17 -0500, Tom Lane wrote: > > > Simon Riggs <[EMAIL PROTECTED]> 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. > > IMHO, that reference is irrrelevant.
Tom had said SQL99 required this; I have pointed out SQL:2003, which supercedes the SQL99 standard, does not require this. Leading us back to my original point - what is the benefit of continuing with having a WARNING when that leads people into trouble later? > Yes, there must be an index due to > our implementation, however that doesn't imply that the types must be the > same No, it doesn't imply it, but what benefit do you see from the interpretation that they are allowed to differ? That interpretation currently leads to many mistakes leading to poor performance. There is clear benefit from forcing them to be the same. In logical data terms, they *should* be the same. I don't check fruit.apple_grade against fruit_type.orange_grade. When would I want to make a check of that nature? If there is a reason, thats great, lets keep status quo then. I respect the effort and thought that has already gone into the implementation; I seek only to offer a very minor improvement based upon recent list issues. > nor even that the index must be usable for the cross table > comparison. Thats a separate discussion, possibly the next one. Best Regards, Simon Riggs ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match