Add opr_sanity queries to inspect commutator/negator links more closely.

Make lists of the names of all operators that are claimed to be commutator
pairs or negator pairs.  This is analogous to the existing queries that
make lists of all operator names appearing in particular opclass strategy
slots.  Unexpected additions to these lists are likely to be mistakes; had
we had these queries in place before, bug #11178 might've been prevented.

2 files changed, 90 insertions, 6 deletions

diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index c04cddc4eec..992522ea3f1 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -4,9 +4,10 @@
 -- pg_operator, pg_proc, pg_cast, pg_aggregate, pg_am,
 -- pg_amop, pg_amproc, pg_opclass, pg_opfamily.
 --
--- Every test failures in this file should be closely inspected. The
--- description of the failing test should be read carefully before
--- adjusting the expected output.
+-- Every test failure in this file should be closely inspected.
+-- The description of the failing test should be read carefully before
+-- adjusting the expected output.  In most cases, the queries should
+-- not find *any* matching entries.
 --
 -- NB: we assume the oidjoins test will have caught any dangling links,
 -- that is OID or REGPROC fields that are not zero and do not match some
@@ -844,6 +845,72 @@ WHERE p1.oprnegate = p2.oid AND
 -----+---------+-----+---------
 (0 rows)
 
+-- Make a list of the names of operators that are claimed to be commutator
+-- pairs.  This list will grow over time, but before accepting a new entry
+-- make sure you didn't link the wrong operators.
+SELECT DISTINCT o1.oprname AS op1, o2.oprname AS op2
+FROM pg_operator o1, pg_operator o2
+WHERE o1.oprcom = o2.oid AND o1.oprname <= o2.oprname
+ORDER BY 1, 2;
+ op1  | op2  
+------+------
+ #    | #
+ &    | &
+ &&   | &&
+ *    | *
+ *<   | *>
+ *<=  | *>=
+ *<>  | *<>
+ *=   | *=
+ +    | +
+ -|-  | -|-
+ <    | >
+ <->  | <->
+ <<   | >>
+ <<=  | >>=
+ <=   | >=
+ <>   | <>
+ <@   | @>
+ =    | =
+ ?#   | ?#
+ ?-   | ?-
+ ?-|  | ?-|
+ ?|   | ?|
+ ?||  | ?||
+ @    | ~
+ @@   | @@
+ @@@  | @@@
+ |    | |
+ ~<=~ | ~>=~
+ ~<~  | ~>~
+ ~=   | ~=
+(30 rows)
+
+-- Likewise for negator pairs.
+SELECT DISTINCT o1.oprname AS op1, o2.oprname AS op2
+FROM pg_operator o1, pg_operator o2
+WHERE o1.oprnegate = o2.oid AND o1.oprname <= o2.oprname
+ORDER BY 1, 2;
+ op1  | op2  
+------+------
+ !~   | ~
+ !~*  | ~*
+ !~~  | ~~
+ !~~* | ~~*
+ #<   | #>=
+ #<=  | #>
+ #<>  | #=
+ *<   | *>=
+ *<=  | *>
+ *<>  | *=
+ <    | >=
+ <=   | >
+ <>   | =
+ <>   | ~=
+ ~<=~ | ~>~
+ ~<~  | ~>=~
+(16 rows)
+
 -- A mergejoinable or hashjoinable operator must be binary, must return
 -- boolean, and must have a commutator (itself, unless it's a cross-type
 -- operator).
diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql
index 213a66d4a31..b394c300769 100644
--- a/src/test/regress/sql/opr_sanity.sql
+++ b/src/test/regress/sql/opr_sanity.sql
@@ -4,9 +4,10 @@
 -- pg_operator, pg_proc, pg_cast, pg_aggregate, pg_am,
 -- pg_amop, pg_amproc, pg_opclass, pg_opfamily.
 --
--- Every test failures in this file should be closely inspected. The
--- description of the failing test should be read carefully before
--- adjusting the expected output.
+-- Every test failure in this file should be closely inspected.
+-- The description of the failing test should be read carefully before
+-- adjusting the expected output.  In most cases, the queries should
+-- not find *any* matching entries.
 --
 -- NB: we assume the oidjoins test will have caught any dangling links,
 -- that is OID or REGPROC fields that are not zero and do not match some
@@ -481,6 +482,22 @@ WHERE p1.oprnegate = p2.oid AND
      p1.oid != p2.oprnegate OR
      p1.oid = p2.oid);
 
+-- Make a list of the names of operators that are claimed to be commutator
+-- pairs.  This list will grow over time, but before accepting a new entry
+-- make sure you didn't link the wrong operators.
+
+SELECT DISTINCT o1.oprname AS op1, o2.oprname AS op2
+FROM pg_operator o1, pg_operator o2
+WHERE o1.oprcom = o2.oid AND o1.oprname <= o2.oprname
+ORDER BY 1, 2;
+
+-- Likewise for negator pairs.
+
+SELECT DISTINCT o1.oprname AS op1, o2.oprname AS op2
+FROM pg_operator o1, pg_operator o2
+WHERE o1.oprnegate = o2.oid AND o1.oprname <= o2.oprname
+ORDER BY 1, 2;
+
 -- A mergejoinable or hashjoinable operator must be binary, must return
 -- boolean, and must have a commutator (itself, unless it's a cross-type
 -- operator).