Re: BUG #11617: issue with dump/restore involving view with hstore data type embedded in where condition

On Sat, Oct 25, 2014 at 09:28:27PM -0400, Tom Lane wrote:
> I wrote:
> > I wonder whether we couldn't fix this better by insisting that these
> > operations depend on default btree opclasses instead of looking up
> > "=" by name. Upthread I whined that this wouldn't work for comparisons
> > of nonidentical datatypes, but could we insist for cross-type cases that
> > both types have default btree opclasses belonging to the same opfamily?
>
> I thought a bit more about this, and that idea isn't going to work, at
> least not by itself. Right now you can do, eg, "integer IS DISTINCT
> FROM numeric", and it works, but the way it works is that the integer
> is promoted to numeric and we use the "numeric = numeric" operator.
> int and numeric do not have an opfamily in common, so this case would
> fail with the rule above.

Is there something precluding implementation of an all-numeric-types opfamily
that contains the existing default btree opclass operators? That wouldn't
solve every example like this, but it would help here among other places.

> A reasonable way to handle that sort of case is to do select_common_type
> (ie, UNION-like type promotion) and then insist that the common type have
> a default btree opclass.
>
> However, the common-opfamily approach seems like a better idea when it
> works, since it would avoid a runtime type coercion in cases where there
> is a suitable cross-type comparison operator. So what I now suggest is:
>
> 1. If the two inputs are of types that have default btree opclasses in
> the same opfamily, and that opfamily has an equality operator that
> accepts these input types, use that operator.
>
> 2. Otherwise, do select_common_type(), and see if the common type has
> a default btree opclass. If so, use that opclass's equality operator
> after coercing both inputs to the common type.
>
> 3. If neither of those work, fail.

I'm loathe to introduce another operator selection method. We have the
func_select_candidates() method and the opfamily method. This third method
shares some features with each of the existing two, and it makes novel use of
select_common_type(). To break compatibility like this, we had better be
confident that the new algorithm is a great one. I'm confident that the
current specification is a bad one, but I'm not confident that bringing
select_common_type() into the mix is the right replacement.

On Fri, Oct 24, 2014 at 06:36:47PM -0400, Tom Lane wrote:
> Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> writes:
> > I don't think this can be solved without some additional syntax, for
> > specifying the equality operator explicitly. I propose that we add an
> > optional USING <operator> after the problematic expressions:
>
> > a IS DISTINCT FROM b USING myschema.=
> > NULLIF(a, b) USING myschema.=
> > ...
>
> Meh. When would this be used? I don't think we'd want ruleutils to
> deparse this way all the time. Also, in the case of IS DISTINCT FROM
> on composite values, it's not clear that one operator name is enough.

The similar situation with IN -> =ANY is tolerable, and I could be content
with ruleutils deparsing that way every time. That's certainly the safe fix.

Thanks,
nm