Cause CHAR(n) to TEXT or VARCHAR conversion to automatically strip trailing

blanks, in hopes of reducing the surprise factor for newbies.  Remove
redundant operators for VARCHAR (it depends wholly on TEXT operations now).
Clean up resolution of ambiguous operators/functions to avoid surprising
choices for domains: domains are treated as equivalent to their base types
and binary-coercibility is no longer considered a preference item when
choosing among multiple operators/functions.  IsBinaryCoercible now correctly
reflects the notion that you need *only* relabel the type to get from type
A to type B: that is, a domain is binary-coercible to its base type, but
not vice versa.  Various marginal cleanup, including merging the essentially
duplicate resolution code in parse_func.c and parse_oper.c.  Improve opr_sanity
regression test to understand about binary compatibility (using pg_cast),
and fix a couple of small errors in the catalogs revealed thereby.
Restructure "special operator" handling to fetch operators via index opclasses
rather than hardwiring assumptions about names (cleans up the pattern_ops
stuff a little).

12 files changed, 242 insertions, 191 deletions

diff --git a/src/test/regress/expected/name.out b/src/test/regress/expected/name.out
index 8cc09dae744..f6c3a17fe51 100644
--- a/src/test/regress/expected/name.out
+++ b/src/test/regress/expected/name.out
@@ -27,7 +27,7 @@ INSERT INTO NAME_TBL(f1) VALUES ('d34aaasdf');
 INSERT INTO NAME_TBL(f1) VALUES ('');
 INSERT INTO NAME_TBL(f1) VALUES ('1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ');
 SELECT '' AS seven, NAME_TBL.*;
- seven |               f1                
+ seven |                               f1                                
 -------+-----------------------------------------------------------------
        | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
        | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq
@@ -39,7 +39,7 @@ SELECT '' AS seven, NAME_TBL.*;
 (7 rows)
 
 SELECT '' AS six, c.f1 FROM NAME_TBL c WHERE c.f1 <> '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR';
- six |               f1                
+ six |                               f1                                
 -----+-----------------------------------------------------------------
      | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq
      | asdfghjkl;
@@ -49,20 +49,20 @@ SELECT '' AS six, c.f1 FROM NAME_TBL c WHERE c.f1 <> '1234567890ABCDEFGHIJKLMNOP
 (5 rows)
 
 SELECT '' AS one, c.f1 FROM NAME_TBL c WHERE c.f1 = '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR';
- one |        f1        
+ one |                               f1                                
 -----+-----------------------------------------------------------------
      | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
      | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
 (2 rows)
 
 SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 < '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR';
- three |               f1                
+ three | f1 
 -------+----
        | 
 (1 row)
 
 SELECT '' AS four, c.f1 FROM NAME_TBL c WHERE c.f1 <= '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR';
- four |               f1                
+ four |                               f1                                
 ------+-----------------------------------------------------------------
       | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
       | 
@@ -70,7 +70,7 @@ SELECT '' AS four, c.f1 FROM NAME_TBL c WHERE c.f1 <= '1234567890ABCDEFGHIJKLMNO
 (3 rows)
 
 SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 > '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR';
- three |        f1        
+ three |                               f1                                
 -------+-----------------------------------------------------------------
        | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq
        | asdfghjkl;
@@ -79,7 +79,7 @@ SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 > '1234567890ABCDEFGHIJKLMNO
 (4 rows)
 
 SELECT '' AS four, c.f1 FROM NAME_TBL c WHERE c.f1 >= '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR';
- four |        f1        
+ four |                               f1                                
 ------+-----------------------------------------------------------------
       | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
       | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq
@@ -90,7 +90,7 @@ SELECT '' AS four, c.f1 FROM NAME_TBL c WHERE c.f1 >= '1234567890ABCDEFGHIJKLMNO
 (6 rows)
 
 SELECT '' AS seven, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '.*';
- seven |               f1                
+ seven |                               f1                                
 -------+-----------------------------------------------------------------
        | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
        | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq
@@ -107,7 +107,7 @@ SELECT '' AS zero, c.f1 FROM NAME_TBL c WHERE c.f1 !~ '.*';
 (0 rows)
 
 SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '[0-9]';
- three |               f1                
+ three |                               f1                                
 -------+-----------------------------------------------------------------
        | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
        | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 2573d2011c3..e0504706f3c 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -14,6 +14,23 @@
 --
 -- NB: run this test earlier than the create_operator test, because
 -- that test creates some bogus operators...
+-- Helper functions to deal with cases where binary-coercible matches are
+-- allowed.
+-- This should match IsBinaryCoercible() in parse_coerce.c.
+create function binary_coercible(oid, oid) returns bool as
+'SELECT ($1 = $2) OR
+ EXISTS(select 1 from pg_cast where
+        castsource = $1 and casttarget = $2 and
+        castfunc = 0 and castcontext = ''i'')'
+language sql;
+-- This one ignores castcontext, so it considers only physical equivalence
+-- and not whether the coercion can be invoked implicitly.
+create function physically_coercible(oid, oid) returns bool as
+'SELECT ($1 = $2) OR
+ EXISTS(select 1 from pg_cast where
+        castsource = $1 and casttarget = $2 and
+        castfunc = 0)'
+language sql;
 -- **************** pg_proc ****************
 -- Look for illegal values in pg_proc fields.
 -- NOTE: in reality pronargs could be more than 10, but I'm too lazy to put
@@ -105,11 +122,10 @@ WHERE p1.oid != p2.oid AND
 -------------+-------------
           25 |        1042
           25 |        1043
-        1042 |        1043
         1114 |        1184
         1560 |        1562
         2277 |        2283
-(6 rows)
+(5 rows)
 
 SELECT DISTINCT p1.proargtypes[1], p2.proargtypes[1]
 FROM pg_proc AS p1, pg_proc AS p2
@@ -120,13 +136,12 @@ WHERE p1.oid != p2.oid AND
     (p1.proargtypes[1] < p2.proargtypes[1]);
  proargtypes | proargtypes 
 -------------+-------------
+          23 |          28
           25 |        1042
-          25 |        1043
-        1042 |        1043
         1114 |        1184
         1560 |        1562
         2277 |        2283
-(6 rows)
+(5 rows)
 
 SELECT DISTINCT p1.proargtypes[2], p2.proargtypes[2]
 FROM pg_proc AS p1, pg_proc AS p2
@@ -228,23 +243,17 @@ SELECT c.*
 FROM pg_cast c, pg_proc p
 WHERE c.castfunc = p.oid AND
     (p.pronargs <> 1
-     OR NOT (c.castsource = p.proargtypes[0] OR
-             EXISTS (SELECT 1 FROM pg_cast k
-                     WHERE k.castfunc = 0 AND
-                       k.castsource = c.castsource AND
-                       k.casttarget = p.proargtypes[0]))
-     OR NOT (p.prorettype = c.casttarget OR
-             EXISTS (SELECT 1 FROM pg_cast k
-                     WHERE k.castfunc = 0 AND
-                       k.castsource = p.prorettype AND
-                       k.casttarget = c.casttarget)));
+     OR NOT binary_coercible(c.castsource, p.proargtypes[0])
+     OR NOT binary_coercible(p.prorettype, c.casttarget));
  castsource | casttarget | castfunc | castcontext 
 ------------+------------+----------+-------------
 (0 rows)
 
 -- Look for binary compatible casts that do not have the reverse
 -- direction registered as well, or where the reverse direction is not
--- also binary compatible.  This is legal, but probably not intended.
+-- also binary compatible.  This is legal, but usually not intended.
+-- As of 7.4, this finds the casts from text and varchar to bpchar, because
+-- those are binary-compatible while the reverse way goes through rtrim().
 SELECT *
 FROM pg_cast c
 WHERE c.castfunc = 0 AND
@@ -254,7 +263,9 @@ WHERE c.castfunc = 0 AND
                     k.casttarget = c.castsource);
  castsource | casttarget | castfunc | castcontext 
 ------------+------------+----------+-------------
-(0 rows)
+         25 |       1042 |        0 | i
+       1043 |       1042 |        0 | i
+(2 rows)
 
 -- **************** pg_operator ****************
 -- Look for illegal values in pg_operator fields.
@@ -425,14 +436,15 @@ WHERE p1.oprlsortop != p1.oprrsortop AND
 -- Hashing only works on simple equality operators "type = sametype",
 -- since the hash itself depends on the bitwise representation of the type.
 -- Check that allegedly hashable operators look like they might be "=".
--- NOTE: in 7.3, this search finds xideqint4.
--- Until we have some cleaner way of dealing with binary-equivalent types,
--- just leave that tuple in the expected output.
+-- NOTE: as of 7.3, this search finds xideqint4.  Since we do not mark
+-- xid and int4 as binary-equivalent in pg_cast, there's no easy way to
+-- recognize that case as OK; just leave that tuple in the expected output.
 SELECT p1.oid, p1.oprname
 FROM pg_operator AS p1
 WHERE p1.oprcanhash AND NOT
     (p1.oprkind = 'b' AND p1.oprresult = 'bool'::regtype AND
-     p1.oprleft = p1.oprright AND p1.oprname IN ('=', '~=~') AND p1.oprcom = p1.oid);
+     p1.oprleft = p1.oprright AND p1.oprname IN ('=', '~=~') AND
+     p1.oprcom = p1.oid);
  oid | oprname 
 -----+---------
  353 | =
@@ -464,33 +476,26 @@ WHERE p1.oprcanhash AND p1.oprcode = p2.oid AND p2.proname = 'array_eq';
 
 -- Check that each operator defined in pg_operator matches its oprcode entry
 -- in pg_proc.  Easiest to do this separately for each oprkind.
--- FIXME: want to check that argument/result types match, but how to do that
--- in the face of binary-compatible types?
 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
 FROM pg_operator AS p1, pg_proc AS p2
 WHERE p1.oprcode = p2.oid AND
     p1.oprkind = 'b' AND
     (p2.pronargs != 2
--- diked out until we find a way of marking binary-compatible types
--- OR
---     p1.oprresult != p2.prorettype OR
---     (p1.oprleft != p2.proargtypes[0] AND p2.proargtypes[0] != 0) OR
---     (p1.oprright != p2.proargtypes[1] AND p2.proargtypes[1] != 0)
-);
+     OR NOT binary_coercible(p2.prorettype, p1.oprresult)
+     OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0])
+     OR NOT binary_coercible(p1.oprright, p2.proargtypes[1]));
  oid | oprname | oid | proname 
 -----+---------+-----+---------
 (0 rows)
 
--- These two selects can be left as-is because there are no binary-compatible
--- cases that they trip over, at least in 6.5:
 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
 FROM pg_operator AS p1, pg_proc AS p2
 WHERE p1.oprcode = p2.oid AND
     p1.oprkind = 'l' AND
-    (p2.pronargs != 1 OR
-     p1.oprresult != p2.prorettype OR
-     (p1.oprright != p2.proargtypes[0] AND p2.proargtypes[0] != 0) OR
-     p1.oprleft != 0);
+    (p2.pronargs != 1
+     OR NOT binary_coercible(p2.prorettype, p1.oprresult)
+     OR NOT binary_coercible(p1.oprright, p2.proargtypes[0])
+     OR p1.oprleft != 0);
  oid | oprname | oid | proname 
 -----+---------+-----+---------
 (0 rows)
@@ -499,10 +504,10 @@ SELECT p1.oid, p1.oprname, p2.oid, p2.proname
 FROM pg_operator AS p1, pg_proc AS p2
 WHERE p1.oprcode = p2.oid AND
     p1.oprkind = 'r' AND
-    (p2.pronargs != 1 OR
-     p1.oprresult != p2.prorettype OR
-     (p1.oprleft != p2.proargtypes[0] AND p2.proargtypes[0] != 0) OR
-     p1.oprright != 0);
+    (p2.pronargs != 1
+     OR NOT binary_coercible(p2.prorettype, p1.oprresult)
+     OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0])
+     OR p1.oprright != 0);
  oid | oprname | oid | proname 
 -----+---------+-----+---------
 (0 rows)
@@ -591,48 +596,46 @@ WHERE a.aggfnoid = p.oid AND
 (0 rows)
 
 -- Cross-check transfn against its entry in pg_proc.
--- FIXME: what about binary-compatible types?
--- NOTE: in 7.1, this search finds max and min on abstime, which are
--- implemented using int4larger/int4smaller.  Until we have
--- some cleaner way of dealing with binary-equivalent types, just leave
--- those two tuples in the expected output.
-SELECT a.aggfnoid::oid, p.proname, p2.oid, p2.proname
-FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS p2
+-- NOTE: use physically_coercible here, not binary_coercible, because
+-- max and min on abstime are implemented using int4larger/int4smaller.
+SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
+FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
 WHERE a.aggfnoid = p.oid AND
-    a.aggtransfn = p2.oid AND
-    (p2.proretset OR
-     a.aggtranstype != p2.prorettype OR
-     a.aggtranstype != p2.proargtypes[0] OR
-     NOT ((p2.pronargs = 2 AND p.proargtypes[0] = p2.proargtypes[1]) OR
-          (p2.pronargs = 1 AND p.proargtypes[0] = '"any"'::regtype)))
-ORDER BY 1;
- aggfnoid | proname | oid |   proname   
-----------+---------+-----+-------------
-     2121 | max     | 768 | int4larger
-     2137 | min     | 769 | int4smaller
-(2 rows)
+    a.aggtransfn = ptr.oid AND
+    (ptr.proretset
+     OR NOT physically_coercible(ptr.prorettype, a.aggtranstype)
+     OR NOT physically_coercible(a.aggtranstype, ptr.proargtypes[0])
+     OR NOT ((ptr.pronargs = 2 AND
+              physically_coercible(p.proargtypes[0], ptr.proargtypes[1]))
+             OR
+             (ptr.pronargs = 1 AND
+              p.proargtypes[0] = '"any"'::regtype)));
+ aggfnoid | proname | oid | proname 
+----------+---------+-----+---------
+(0 rows)
 
 -- Cross-check finalfn (if present) against its entry in pg_proc.
--- FIXME: what about binary-compatible types?
-SELECT a.aggfnoid::oid, p.proname, p2.oid, p2.proname
-FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS p2
+SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname
+FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn
 WHERE a.aggfnoid = p.oid AND
-    a.aggfinalfn = p2.oid AND
-    (p2.proretset OR p.prorettype != p2.prorettype OR
-     p2.pronargs != 1 OR
-     a.aggtranstype != p2.proargtypes[0]);
+    a.aggfinalfn = pfn.oid AND
+    (pfn.proretset
+     OR NOT binary_coercible(pfn.prorettype, p.prorettype)
+     OR pfn.pronargs != 1
+     OR NOT binary_coercible(a.aggtranstype, pfn.proargtypes[0]));
  aggfnoid | proname | oid | proname 
 ----------+---------+-----+---------
 (0 rows)
 
 -- If transfn is strict then either initval should be non-NULL, or
--- input type should equal transtype so that the first non-null input
+-- input type should match transtype so that the first non-null input
 -- can be assigned as the state value.
-SELECT a.aggfnoid::oid, p.proname, p2.oid, p2.proname
-FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS p2
+SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
+FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
 WHERE a.aggfnoid = p.oid AND
-    a.aggtransfn = p2.oid AND p2.proisstrict AND
-    a.agginitval IS NULL AND p.proargtypes[0] != a.aggtranstype;
+    a.aggtransfn = ptr.oid AND ptr.proisstrict AND
+    a.agginitval IS NULL AND
+    NOT binary_coercible(p.proargtypes[0], a.aggtranstype);
  aggfnoid | proname | oid | proname 
 ----------+---------+-----+---------
 (0 rows)
@@ -714,7 +717,8 @@ WHERE p1.amopopr = p2.oid AND
 SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname, p3.opcname
 FROM pg_amop AS p1, pg_operator AS p2, pg_opclass AS p3
 WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid AND
-    (p3.opcintype != p2.oprleft OR p3.opcintype != p2.oprright);
+    (NOT binary_coercible(p3.opcintype, p2.oprleft) OR
+     p2.oprleft != p2.oprright);
  amopclaid | amopopr | oid | oprname | opcname 
 -----------+---------+-----+---------+---------
 (0 rows)
@@ -752,7 +756,8 @@ WHERE p2.opcamid = p1.oid AND
 -- signature of the function may be different for different support routines
 -- or different base data types.
 -- We can check that all the referenced instances of the same support
--- routine number take the same number of parameters, but that's about it...
+-- routine number take the same number of parameters, but that's about it
+-- for a general check...
 SELECT p1.amopclaid, p1.amprocnum,
 	p2.oid, p2.proname,
 	p3.opcname,
@@ -769,3 +774,22 @@ WHERE p1.amopclaid = p3.oid AND p4.amopclaid = p6.oid AND
 -----------+-----------+-----+---------+---------+-----------+-----------+-----+---------+---------
 (0 rows)
 
+-- For btree, though, we can do better since we know the support routines
+-- must be of the form cmp(input, input) returns int4.
+SELECT p1.amopclaid, p1.amprocnum,
+	p2.oid, p2.proname,
+	p3.opcname
+FROM pg_amproc AS p1, pg_proc AS p2, pg_opclass AS p3
+WHERE p3.opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree')
+    AND p1.amopclaid = p3.oid AND p1.amproc = p2.oid AND
+    (opckeytype != 0
+     OR amprocnum != 1
+     OR proretset
+     OR prorettype != 23
+     OR pronargs != 2
+     OR NOT binary_coercible(opcintype, proargtypes[0])
+     OR proargtypes[0] != proargtypes[1]);
+ amopclaid | amprocnum | oid | proname | opcname 
+-----------+-----------+-----+---------+---------
+(0 rows)
+
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index f173b6cf544..322b84ff100 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1317,9 +1317,9 @@ SELECT tablename, rulename, definition FROM pg_rules
 ---------------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  pg_settings   | pg_settings_n   | CREATE RULE pg_settings_n AS ON UPDATE TO pg_settings DO INSTEAD NOTHING;
  pg_settings   | pg_settings_u   | CREATE RULE pg_settings_u AS ON UPDATE TO pg_settings WHERE (new.name = old.name) DO SELECT set_config(old.name, new.setting, false) AS set_config;
- rtest_emp     | rtest_emp_del   | CREATE RULE rtest_emp_del AS ON DELETE TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (old.ename, "current_user"(), 'fired     '::bpchar, '$0.00'::money, old.salary);
- rtest_emp     | rtest_emp_ins   | CREATE RULE rtest_emp_ins AS ON INSERT TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'hired     '::bpchar, new.salary, '$0.00'::money);
- rtest_emp     | rtest_emp_upd   | CREATE RULE rtest_emp_upd AS ON UPDATE TO rtest_emp WHERE (new.salary <> old.salary) DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'honored   '::bpchar, new.salary, old.salary);
+ rtest_emp     | rtest_emp_del   | CREATE RULE rtest_emp_del AS ON DELETE TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (old.ename, "current_user"(), 'fired'::bpchar, '$0.00'::money, old.salary);
+ rtest_emp     | rtest_emp_ins   | CREATE RULE rtest_emp_ins AS ON INSERT TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'hired'::bpchar, new.salary, '$0.00'::money);
+ rtest_emp     | rtest_emp_upd   | CREATE RULE rtest_emp_upd AS ON UPDATE TO rtest_emp WHERE (new.salary <> old.salary) DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'honored'::bpchar, new.salary, old.salary);
  rtest_nothn1  | rtest_nothn_r1  | CREATE RULE rtest_nothn_r1 AS ON INSERT TO rtest_nothn1 WHERE ((new.a >= 10) AND (new.a < 20)) DO INSTEAD NOTHING;
  rtest_nothn1  | rtest_nothn_r2  | CREATE RULE rtest_nothn_r2 AS ON INSERT TO rtest_nothn1 WHERE ((new.a >= 30) AND (new.a < 40)) DO INSTEAD NOTHING;
  rtest_nothn2  | rtest_nothn_r3  | CREATE RULE rtest_nothn_r3 AS ON INSERT TO rtest_nothn2 WHERE (new.a >= 100) DO INSTEAD INSERT INTO rtest_nothn3 (a, b) VALUES (new.a, new.b);
diff --git a/src/test/regress/expected/select_having.out b/src/test/regress/expected/select_having.out
index 4447a9df4d6..37793d49b57 100644
--- a/src/test/regress/expected/select_having.out
+++ b/src/test/regress/expected/select_having.out
@@ -33,11 +33,11 @@ SELECT b, c FROM test_having
 SELECT lower(c), count(c) FROM test_having
 	GROUP BY lower(c) HAVING count(*) > 2 OR min(a) = max(a)
 	ORDER BY lower(c);
-  lower   | count 
-----------+-------
- bbbb     |     3
- cccc     |     4
- xxxx     |     1
+ lower | count 
+-------+-------
+ bbbb  |     3
+ cccc  |     4
+ xxxx  |     1
 (3 rows)
 
 SELECT c, max(a) FROM test_having
diff --git a/src/test/regress/expected/select_having_1.out b/src/test/regress/expected/select_having_1.out
index ffbb591b125..6154bcbfe86 100644
--- a/src/test/regress/expected/select_having_1.out
+++ b/src/test/regress/expected/select_having_1.out
@@ -33,11 +33,11 @@ SELECT b, c FROM test_having
 SELECT lower(c), count(c) FROM test_having
 	GROUP BY lower(c) HAVING count(*) > 2 OR min(a) = max(a)
 	ORDER BY lower(c);
-  lower   | count 
-----------+-------
- bbbb     |     3
- cccc     |     4
- xxxx     |     1
+ lower | count 
+-------+-------
+ bbbb  |     3
+ cccc  |     4
+ xxxx  |     1
 (3 rows)
 
 SELECT c, max(a) FROM test_having
diff --git a/src/test/regress/expected/select_implicit.out b/src/test/regress/expected/select_implicit.out
index 47d2e80289e..d8a9edbd781 100644
--- a/src/test/regress/expected/select_implicit.out
+++ b/src/test/regress/expected/select_implicit.out
@@ -248,12 +248,12 @@ SELECT count(b) FROM test_missing_target GROUP BY b/2 ORDER BY b/2;
 --   w/ existing GROUP BY target using a relation name in target
 SELECT lower(test_missing_target.c), count(c)
   FROM test_missing_target GROUP BY lower(c) ORDER BY lower(c);
-  lower   | count 
-----------+-------
- aaaa     |     2
- bbbb     |     3
- cccc     |     4
- xxxx     |     1
+ lower | count 
+-------+-------
+ aaaa  |     2
+ bbbb  |     3
+ cccc  |     4
+ xxxx  |     1
 (4 rows)
 
 --   w/o existing GROUP BY target
diff --git a/src/test/regress/expected/select_implicit_1.out b/src/test/regress/expected/select_implicit_1.out
index 80abfd8f9a2..fa67bf3a5d1 100644
--- a/src/test/regress/expected/select_implicit_1.out
+++ b/src/test/regress/expected/select_implicit_1.out
@@ -248,12 +248,12 @@ SELECT count(b) FROM test_missing_target GROUP BY b/2 ORDER BY b/2;
 --   w/ existing GROUP BY target using a relation name in target
 SELECT lower(test_missing_target.c), count(c)
   FROM test_missing_target GROUP BY lower(c) ORDER BY lower(c);
-  lower   | count 
-----------+-------
- aaaa     |     2
- bbbb     |     3
- cccc     |     4
- xxxx     |     1
+ lower | count 
+-------+-------
+ aaaa  |     2
+ bbbb  |     3
+ cccc  |     4
+ xxxx  |     1
 (4 rows)
 
 --   w/o existing GROUP BY target
diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out
index bb999785eb1..26c18d1690b 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -26,8 +26,8 @@ ERROR:  parser: parse error at or near "' - third line'" at character 75
 SELECT CAST(f1 AS text) AS "text(char)" FROM CHAR_TBL;
  text(char) 
 ------------
- a   
- ab  
+ a
+ ab
  abcd
  abcd
 (4 rows)
@@ -88,8 +88,8 @@ SELECT CAST(f1 AS varchar) AS "varchar(text)" FROM TEXT_TBL;
 SELECT CAST(f1 AS varchar) AS "varchar(char)" FROM CHAR_TBL;
  varchar(char) 
 ---------------
- a   
- ab  
+ a
+ ab
  abcd
  abcd
 (4 rows)
@@ -570,16 +570,16 @@ SELECT text 'text' || ' and unknown' AS "Concat text to unknown type";
  text and unknown
 (1 row)
 
-SELECT char(20) 'characters' || 'and text' AS "Concat char to unknown type";
- Concat char to unknown type  
-------------------------------
- characters          and text
+SELECT char(20) 'characters' || ' and text' AS "Concat char to unknown type";
+ Concat char to unknown type 
+-----------------------------
+ characters and text
 (1 row)
 
 SELECT text 'text' || char(20) ' and characters' AS "Concat text to char";
-   Concat text to char    
---------------------------
- text and characters     
+ Concat text to char 
+---------------------
+ text and characters
 (1 row)
 
 SELECT text 'text' || varchar ' and varchar' AS "Concat text to varchar";
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index a46a44becab..43d32298a58 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -203,21 +203,19 @@ SELECT f1 FROM INT4_TBL
                 123456
 (5 rows)
 
-SELECT f1 AS five FROM VARCHAR_TBL
+SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL
 UNION
 SELECT f1 FROM CHAR_TBL;
- five 
-------
- a
+ three 
+-------
  a   
- ab
  ab  
  abcd
-(5 rows)
+(3 rows)
 
 SELECT f1 AS three FROM VARCHAR_TBL
 UNION
-SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL;
+SELECT CAST(f1 AS varchar) FROM CHAR_TBL;
  three 
 -------
  a
@@ -234,8 +232,8 @@ SELECT f1 FROM CHAR_TBL;
  ab
  abcd
  abcd
- a   
- ab  
+ a
+ ab
  abcd
  abcd
 (8 rows)
diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql
index 5be76aa3b41..4b7bd7b4dd5 100644
--- a/src/test/regress/sql/opr_sanity.sql
+++ b/src/test/regress/sql/opr_sanity.sql
@@ -15,6 +15,27 @@
 -- NB: run this test earlier than the create_operator test, because
 -- that test creates some bogus operators...
 
+
+-- Helper functions to deal with cases where binary-coercible matches are
+-- allowed.
+
+-- This should match IsBinaryCoercible() in parse_coerce.c.
+create function binary_coercible(oid, oid) returns bool as
+'SELECT ($1 = $2) OR
+ EXISTS(select 1 from pg_cast where
+        castsource = $1 and casttarget = $2 and
+        castfunc = 0 and castcontext = ''i'')'
+language sql;
+
+-- This one ignores castcontext, so it considers only physical equivalence
+-- and not whether the coercion can be invoked implicitly.
+create function physically_coercible(oid, oid) returns bool as
+'SELECT ($1 = $2) OR
+ EXISTS(select 1 from pg_cast where
+        castsource = $1 and casttarget = $2 and
+        castfunc = 0)'
+language sql;
+
 -- **************** pg_proc ****************
 
 -- Look for illegal values in pg_proc fields.
@@ -180,20 +201,15 @@ SELECT c.*
 FROM pg_cast c, pg_proc p
 WHERE c.castfunc = p.oid AND
     (p.pronargs <> 1
-     OR NOT (c.castsource = p.proargtypes[0] OR
-             EXISTS (SELECT 1 FROM pg_cast k
-                     WHERE k.castfunc = 0 AND
-                       k.castsource = c.castsource AND
-                       k.casttarget = p.proargtypes[0]))
-     OR NOT (p.prorettype = c.casttarget OR
-             EXISTS (SELECT 1 FROM pg_cast k
-                     WHERE k.castfunc = 0 AND
-                       k.castsource = p.prorettype AND
-                       k.casttarget = c.casttarget)));
+     OR NOT binary_coercible(c.castsource, p.proargtypes[0])
+     OR NOT binary_coercible(p.prorettype, c.casttarget));
 
 -- Look for binary compatible casts that do not have the reverse
 -- direction registered as well, or where the reverse direction is not
--- also binary compatible.  This is legal, but probably not intended.
+-- also binary compatible.  This is legal, but usually not intended.
+
+-- As of 7.4, this finds the casts from text and varchar to bpchar, because
+-- those are binary-compatible while the reverse way goes through rtrim().
 
 SELECT *
 FROM pg_cast c
@@ -347,15 +363,17 @@ WHERE p1.oprlsortop != p1.oprrsortop AND
 -- Hashing only works on simple equality operators "type = sametype",
 -- since the hash itself depends on the bitwise representation of the type.
 -- Check that allegedly hashable operators look like they might be "=".
--- NOTE: in 7.3, this search finds xideqint4.
--- Until we have some cleaner way of dealing with binary-equivalent types,
--- just leave that tuple in the expected output.
+
+-- NOTE: as of 7.3, this search finds xideqint4.  Since we do not mark
+-- xid and int4 as binary-equivalent in pg_cast, there's no easy way to
+-- recognize that case as OK; just leave that tuple in the expected output.
 
 SELECT p1.oid, p1.oprname
 FROM pg_operator AS p1
 WHERE p1.oprcanhash AND NOT
     (p1.oprkind = 'b' AND p1.oprresult = 'bool'::regtype AND
-     p1.oprleft = p1.oprright AND p1.oprname IN ('=', '~=~') AND p1.oprcom = p1.oid);
+     p1.oprleft = p1.oprright AND p1.oprname IN ('=', '~=~') AND
+     p1.oprcom = p1.oid);
 
 -- In 6.5 we accepted hashable array equality operators when the array element
 -- type is hashable.  However, what we actually need to make hashjoin work on
@@ -382,41 +400,33 @@ WHERE p1.oprcanhash AND p1.oprcode = p2.oid AND p2.proname = 'array_eq';
 
 -- Check that each operator defined in pg_operator matches its oprcode entry
 -- in pg_proc.  Easiest to do this separately for each oprkind.
--- FIXME: want to check that argument/result types match, but how to do that
--- in the face of binary-compatible types?
 
 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
 FROM pg_operator AS p1, pg_proc AS p2
 WHERE p1.oprcode = p2.oid AND
     p1.oprkind = 'b' AND
     (p2.pronargs != 2
--- diked out until we find a way of marking binary-compatible types
--- OR
---     p1.oprresult != p2.prorettype OR
---     (p1.oprleft != p2.proargtypes[0] AND p2.proargtypes[0] != 0) OR
---     (p1.oprright != p2.proargtypes[1] AND p2.proargtypes[1] != 0)
-);
-
--- These two selects can be left as-is because there are no binary-compatible
--- cases that they trip over, at least in 6.5:
+     OR NOT binary_coercible(p2.prorettype, p1.oprresult)
+     OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0])
+     OR NOT binary_coercible(p1.oprright, p2.proargtypes[1]));
 
 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
 FROM pg_operator AS p1, pg_proc AS p2
 WHERE p1.oprcode = p2.oid AND
     p1.oprkind = 'l' AND
-    (p2.pronargs != 1 OR
-     p1.oprresult != p2.prorettype OR
-     (p1.oprright != p2.proargtypes[0] AND p2.proargtypes[0] != 0) OR
-     p1.oprleft != 0);
+    (p2.pronargs != 1
+     OR NOT binary_coercible(p2.prorettype, p1.oprresult)
+     OR NOT binary_coercible(p1.oprright, p2.proargtypes[0])
+     OR p1.oprleft != 0);
 
 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
 FROM pg_operator AS p1, pg_proc AS p2
 WHERE p1.oprcode = p2.oid AND
     p1.oprkind = 'r' AND
-    (p2.pronargs != 1 OR
-     p1.oprresult != p2.prorettype OR
-     (p1.oprleft != p2.proargtypes[0] AND p2.proargtypes[0] != 0) OR
-     p1.oprright != 0);
+    (p2.pronargs != 1
+     OR NOT binary_coercible(p2.prorettype, p1.oprresult)
+     OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0])
+     OR p1.oprright != 0);
 
 -- If the operator is mergejoinable or hashjoinable, its underlying function
 -- should not be volatile.
@@ -489,42 +499,42 @@ WHERE a.aggfnoid = p.oid AND
     a.aggfinalfn = 0 AND p.prorettype != a.aggtranstype;
 
 -- Cross-check transfn against its entry in pg_proc.
--- FIXME: what about binary-compatible types?
--- NOTE: in 7.1, this search finds max and min on abstime, which are
--- implemented using int4larger/int4smaller.  Until we have
--- some cleaner way of dealing with binary-equivalent types, just leave
--- those two tuples in the expected output.
-SELECT a.aggfnoid::oid, p.proname, p2.oid, p2.proname
-FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS p2
+-- NOTE: use physically_coercible here, not binary_coercible, because
+-- max and min on abstime are implemented using int4larger/int4smaller.
+SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
+FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
 WHERE a.aggfnoid = p.oid AND
-    a.aggtransfn = p2.oid AND
-    (p2.proretset OR
-     a.aggtranstype != p2.prorettype OR
-     a.aggtranstype != p2.proargtypes[0] OR
-     NOT ((p2.pronargs = 2 AND p.proargtypes[0] = p2.proargtypes[1]) OR
-          (p2.pronargs = 1 AND p.proargtypes[0] = '"any"'::regtype)))
-ORDER BY 1;
+    a.aggtransfn = ptr.oid AND
+    (ptr.proretset
+     OR NOT physically_coercible(ptr.prorettype, a.aggtranstype)
+     OR NOT physically_coercible(a.aggtranstype, ptr.proargtypes[0])
+     OR NOT ((ptr.pronargs = 2 AND
+              physically_coercible(p.proargtypes[0], ptr.proargtypes[1]))
+             OR
+             (ptr.pronargs = 1 AND
+              p.proargtypes[0] = '"any"'::regtype)));
 
 -- Cross-check finalfn (if present) against its entry in pg_proc.
--- FIXME: what about binary-compatible types?
 
-SELECT a.aggfnoid::oid, p.proname, p2.oid, p2.proname
-FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS p2
+SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname
+FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn
 WHERE a.aggfnoid = p.oid AND
-    a.aggfinalfn = p2.oid AND
-    (p2.proretset OR p.prorettype != p2.prorettype OR
-     p2.pronargs != 1 OR
-     a.aggtranstype != p2.proargtypes[0]);
+    a.aggfinalfn = pfn.oid AND
+    (pfn.proretset
+     OR NOT binary_coercible(pfn.prorettype, p.prorettype)
+     OR pfn.pronargs != 1
+     OR NOT binary_coercible(a.aggtranstype, pfn.proargtypes[0]));
 
 -- If transfn is strict then either initval should be non-NULL, or
--- input type should equal transtype so that the first non-null input
+-- input type should match transtype so that the first non-null input
 -- can be assigned as the state value.
 
-SELECT a.aggfnoid::oid, p.proname, p2.oid, p2.proname
-FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS p2
+SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
+FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
 WHERE a.aggfnoid = p.oid AND
-    a.aggtransfn = p2.oid AND p2.proisstrict AND
-    a.agginitval IS NULL AND p.proargtypes[0] != a.aggtranstype;
+    a.aggtransfn = ptr.oid AND ptr.proisstrict AND
+    a.agginitval IS NULL AND
+    NOT binary_coercible(p.proargtypes[0], a.aggtranstype);
 
 -- **************** pg_opclass ****************
 
@@ -592,7 +602,8 @@ WHERE p1.amopopr = p2.oid AND
 SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname, p3.opcname
 FROM pg_amop AS p1, pg_operator AS p2, pg_opclass AS p3
 WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid AND
-    (p3.opcintype != p2.oprleft OR p3.opcintype != p2.oprright);
+    (NOT binary_coercible(p3.opcintype, p2.oprleft) OR
+     p2.oprleft != p2.oprright);
 
 -- **************** pg_amproc ****************
 
@@ -622,7 +633,8 @@ WHERE p2.opcamid = p1.oid AND
 -- signature of the function may be different for different support routines
 -- or different base data types.
 -- We can check that all the referenced instances of the same support
--- routine number take the same number of parameters, but that's about it...
+-- routine number take the same number of parameters, but that's about it
+-- for a general check...
 
 SELECT p1.amopclaid, p1.amprocnum,
 	p2.oid, p2.proname,
@@ -636,3 +648,20 @@ WHERE p1.amopclaid = p3.oid AND p4.amopclaid = p6.oid AND
     p3.opcamid = p6.opcamid AND p1.amprocnum = p4.amprocnum AND
     p1.amproc = p2.oid AND p4.amproc = p5.oid AND
     (p2.proretset OR p5.proretset OR p2.pronargs != p5.pronargs);
+
+-- For btree, though, we can do better since we know the support routines
+-- must be of the form cmp(input, input) returns int4.
+
+SELECT p1.amopclaid, p1.amprocnum,
+	p2.oid, p2.proname,
+	p3.opcname
+FROM pg_amproc AS p1, pg_proc AS p2, pg_opclass AS p3
+WHERE p3.opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree')
+    AND p1.amopclaid = p3.oid AND p1.amproc = p2.oid AND
+    (opckeytype != 0
+     OR amprocnum != 1
+     OR proretset
+     OR prorettype != 23
+     OR pronargs != 2
+     OR NOT binary_coercible(opcintype, proargtypes[0])
+     OR proargtypes[0] != proargtypes[1]);
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index b84d0cb8adb..d2368d6ba53 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -199,7 +199,7 @@ SELECT 'unknown' || ' and unknown' AS "Concat unknown types";
 
 SELECT text 'text' || ' and unknown' AS "Concat text to unknown type";
 
-SELECT char(20) 'characters' || 'and text' AS "Concat char to unknown type";
+SELECT char(20) 'characters' || ' and text' AS "Concat char to unknown type";
 
 SELECT text 'text' || char(20) ' and characters' AS "Concat text to char";
 
diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql
index 747d281948f..c69e4c3f152 100644
--- a/src/test/regress/sql/union.sql
+++ b/src/test/regress/sql/union.sql
@@ -66,13 +66,13 @@ UNION
 SELECT f1 FROM INT4_TBL
   WHERE f1 BETWEEN 0 AND 1000000;
 
-SELECT f1 AS five FROM VARCHAR_TBL
+SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL
 UNION
 SELECT f1 FROM CHAR_TBL;
 
 SELECT f1 AS three FROM VARCHAR_TBL
 UNION
-SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL;
+SELECT CAST(f1 AS varchar) FROM CHAR_TBL;
 
 SELECT f1 AS eight FROM VARCHAR_TBL
 UNION ALL