Re: pg_dump search path issue

Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> writes:
> Just found (via a report on irc) a case that doesn't involve functions:

> create extension hstore;
> create schema foo
> create table t1 (a hstore, b integer)
> create table t2 (a hstore, c integer)
> create view v1 as select * from t1 join t2 using (a);

> dump/restore gives:

> ERROR: operator does not exist: public.hstore = public.hstore

Meh. We've discussed this case before, though I'm having a hard time
finding it in the archives tonight. I'm of the opinion that the real
problem here is that JOIN USING uses a name-based operator lookup at all.
Even disregarding the question of whether what you want is in the search
path, it's operating purely on faith that an operator named '=' will do
something appropriate. What it ought to be doing (and likewise CASE,
IS DISTINCT FROM, and anything else that's implicitly assuming that '='
is what to use) is looking for an operator associated with an appropriate
default btree opclass.

In the last go-round, I remember worrying that cross-type operators might
be a problem to identify this way, but a look in the system catalogs
doesn't find anything that seems like an issue. I'm envisioning a lookup
rule like this:

1. Find a default btree opclass that accepts the lefthand data type,
preferably exactly but if not that then binary-compatibly; fail if not
present or more than one binary-compatible candidate. (This is the same
rule used to identify how to build a btree index by default.)

2. Likewise for the righthand datatype.

3. These opclasses must belong to the same operator family, and there
must be a suitable equality operator in that opfamily, else fail.

AFAICT, the operators that would be selected by the existing rule
but not by this rule are just these:

db1=# select o.oid::regoperator, oprcode, cl.opcfamily, cr.opcfamily from pg_operator o
left join pg_opclass cl
on cl.opcintype=o.oprleft and cl.opcmethod = 403 and cl.opcdefault
left join pg_opclass cr
on cr.opcintype=o.oprright and cr.opcmethod = 403 and cr.opcdefault
where
oprname = '=' and
(cl.opcfamily is null or
cr.opcfamily is null or
cl.opcfamily != cr.opcfamily);
oid | oprcode | opcfamily | opcfamily
--------------------------+--------------+-----------+-----------
=(xid,xid) | xideq | |
=(xid,integer) | xideqint4 | | 1976
=(cid,cid) | cideq | |
=(int2vector,int2vector) | int2vectoreq | |
=(aclitem,aclitem) | aclitemeq | |
=(box,box) | box_eq | |
=(path,path) | path_n_eq | |
=(circle,circle) | circle_eq | |
=(lseg,lseg) | lseg_eq | |
=(line,line) | line_eq | |
(10 rows)

The first five of these are internal datatypes anyway; the other five are
fine examples of cases where you actively *don't want* the system silently
relying on them for JOIN/CASE/etc. Three of those aren't equality at all
by any sane definition, and the other two are fuzzy.

So I think this would be a good change and we should just bite the bullet
and do it...

regards, tom lane