Home Explore Blog CI



postgresql

6th chunk of `doc/src/sgml/xoper.sgml`
a4fb39b2743f1cd2e02b7067812d3ca0cc187b3d63f5be200000000100000b31
 different representations.  For example, it's fairly simple
     to arrange this property when hashing integers of different widths.
    </para>

    <para>
     To be marked <literal>HASHES</literal>, the join operator must appear
     in a hash index operator family.  This is not enforced when you create
     the operator, since of course the referencing operator family couldn't
     exist yet.  But attempts to use the operator in hash joins will fail
     at run time if no such operator family exists.  The system needs the
     operator family to find the data-type-specific hash function(s) for the
     operator's input data type(s).  Of course, you must also create suitable
     hash functions before you can create the operator family.
    </para>

    <para>
     Care should be exercised when preparing a hash function, because there
     are machine-dependent ways in which it might fail to do the right thing.
     For example, if your data type is a structure in which there might be
     uninteresting pad bits, you cannot simply pass the whole structure to
     <function>hash_any</function>.  (Unless you write your other operators and
     functions to ensure that the unused bits are always zero, which is the
     recommended strategy.)
     Another example is that on machines that meet the <acronym>IEEE</acronym>
     floating-point standard, negative zero and positive zero are different
     values (different bit patterns) but they are defined to compare equal.
     If a float value might contain negative zero then extra steps are needed
     to ensure it generates the same hash value as positive zero.
    </para>

    <para>
     A hash-joinable operator must have a commutator (itself if the two
     operand data types are the same, or a related equality operator
     if they are different) that appears in the same operator family.
     If this is not the case, planner errors might occur when the operator
     is used.  Also, it is a good idea (but not strictly required) for
     a hash operator family that supports multiple data types to provide
     equality operators for every combination of the data types; this
     allows better optimization.
    </para>

    <note>
    <para>
     The function underlying a hash-joinable operator must be marked
     immutable or stable.  If it is volatile, the system will never
     attempt to use the operator for a hash join.
    </para>
    </note>

    <note>
    <para>
     If a hash-joinable operator has an underlying function that is marked
     strict, the
     function must also be complete: that is, it should return true or
     false, never null, for any two nonnull inputs.  If this rule is
     not followed, hash-optimization of <literal>IN</literal> operations might
     generate wrong results.  (Specifically, <literal>IN</literal> might return
     false

Title: Considerations for Hash-Joinable Operators
Summary
This section discusses the requirements and considerations for creating hash-joinable operators, including the need for a hash index operator family, suitable hash functions, and commutator operators, as well as guidelines for handling machine-dependent issues, immutability, and strictness to ensure correct optimization and results.