Re: pg_dump search path issue - Mailing list pgsql-bugs

Andrew Gierth <andrew@tao11.riddles.org.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