This has not been fixed yet, right? --------------------------------------------------------------------------- Tom Lane wrote: > I wrote: > > Michael Fuhr <mike@fuhr.org> writes: > >> I have a query that works in 7.3.6 but not in 7.4.2 unless I turn > >> off enable_hashjoin. I'm joining a table of network interfaces and > >> a table of networks so I can find additional info about a particular > >> interface's network. > > > Hmm. The inet = operator is marked hashable in 7.4 but not in 7.3 ... > > I wonder if that is a mistake? > > Digging further, I find that indeed this seems to be a mistake. CIDR > and INET values that have the same address and masklen compare as equal > according to network_eq(), but they will not hash the same because > there's a flag identifying whether a given value is considered CIDR or > INET. And what the network() function returns is marked as a CIDR. > It's a bit surprising that your hash join produces any matches at all... > > I believe I got misled on this because there is a hash index operator > class for inet; at one point during the 7.4 cycle I went around and > cleaned up cases where the equality operator's canhash flag was > inconsistent with the set of hash index opclasses. Arguably the hash > opclass is broken, although in practice people probably don't notice the > failure since a given column is likely to contain either all inet or all > cidr values. (And of course it's entirely likely that there *aren't* > any people using the inet hash opclass, period...) > > I can think of a number of possible fixes: > > 1. Mark inet = as not hashjoinable. We'd probably want to remove the > inet hash opclass too. > > 2. Redefine inet = so that CIDR and INET values are never considered > equal, thus eliminating the unused field. This could be back-patched > into 7.4 but otherwise seems to have little to recommend it. It > would certainly not help solve Michael's problem. > > 3. Provide a specialized hash method for type inet that ignores the > iptype field. > > #3 seems the most desirable going forward, but is probably impractical > to back-patch into 7.4.*, so I'm not sure what to do about the problem > in that branch. Given the relatively low incidence of the problem, > maybe it's okay to just clear the oprcanhash flag in future 7.4.* > releases. This would not fix the problem for existing installations > (unless they initdb) but any complainers could be told how to adjust > their catalogs manually. > > Can anyone think of any other approaches? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
