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

Heikki Linnakangas <hlinnakangas@vmware.com> writes:
> In the catalogs, we store the OID of the equality operator used in a
> NULLIF or IS DISTINCT FROM expression. But when you try to deparse that
> back to an SQL statement, it's impossible to construct an equivalent SQL
> statement that would refer to the same operator, when that operator is
> not in search_path. In essence, it's not possible to schema-qualify the
> equality operator used.

Right.

> 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.

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?

It's possible that such a redefinition would lead to rejecting some
cases that work today, but I think they'd be strange corner cases.
A quick look through pg_operator suggests that there are no built-in
operators named "=" that link input types that aren't in the same
opfamily.

The big-picture situation is that right now, we have a weird mixture of
cases where we do equality or sorting on the basis of operators looked up
with an implicitly-assumed operator name, and cases where we do it based
on finding a suitable member of a default btree opclass.  The second way
is on far more solid ground theoretically IMO.  The only defense of the
first way is that the SQL spec frequently says in so many words "this
syntax is equivalent to A = B", and if you take that in a very literal
fashion then looking up an operator named "=" is the thing to do.  But
I think a reasonable case could be made that they mean "A equals B" in
some more abstract sense than "what is the operator named?".

            regards, tom lane