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

On Wed, Jan 07, 2015 at 10:12:10AM -0500, Tom Lane wrote:
> Noah Misch <noah(at)leadboat(dot)com> writes:
> > 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.
>
> That solves only the first example I came up with, not the general
> problem.

Correct.

> >> 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:
>
> > 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.
>
> That's completely not comparable, because =ANY is still valid/standard
> SQL syntax. I don't see how you can justify the idea that introducing
> new, nonstandard syntax for a fundamental SQL operator is better than
> introducing a new operator selection rule.

I weight various priorities as follows; higher is better:

8 - backward compatibility for applications
7 - dump/restore cycle working
5 - query behavior predictable by non-hackers
2 - beauty of ruleutils.c output
1 - limiting use of nonstandard SQL in ruleutils.c output

Hence my conclusion above. Also, I have more than once written an application
query that would have used IS DISTINCT but for the inability to reach a
specific operator; the proposed syntax is an independently-useful feature.

> Keep in mind also that IS DISTINCT FROM is just the tip of the iceberg.
> There's also the shorthand CASE syntax, and probably some other cases
> that I'm not recalling in my caffeine-deprived state.

The list:

IS [NOT] DISTINCT
[NOT] IN
JOIN USING, NATURAL JOIN
CASE <expr> WHEN
NULLIF