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@leadboat.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@vmware.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