Apply table and domain CHECK constraints in name order.

Previously, CHECK constraints of the same scope were checked in whatever
order they happened to be read from pg_constraint.  (Usually, but not
reliably, this would be creation order for domain constraints and reverse
creation order for table constraints, because of differing implementation
details.)  Nondeterministic results of this sort are problematic at least
for testing purposes, and in discussion it was agreed to be a violation of
the principle of least astonishment.  Therefore, borrow the principle
already established for triggers, and apply such checks in name order
(using strcmp() sort rules).  This lets users control the check order
if they have a mind to.

Domain CHECK constraints still follow the rule of checking lower nested
domains' constraints first; the name sort only applies to multiple
constraints attached to the same domain.

In passing, I failed to resist the temptation to wordsmith a bit in
create_domain.sgml.

Apply to HEAD only, since this could result in a behavioral change in
existing applications, and the potential regression test failures have
not actually been observed in our buildfarm.

1 files changed, 40 insertions, 7 deletions

diff --git a/doc/src/sgml/ref/create_domain.sgml b/doc/src/sgml/ref/create_domain.sgml
index dd782e48dce..8b7848b820d 100644
--- a/doc/src/sgml/ref/create_domain.sgml
+++ b/doc/src/sgml/ref/create_domain.sgml
@@ -139,12 +139,8 @@ CREATE DOMAIN <replaceable class="parameter">name</replaceable> [ AS ] <replacea
       <term><literal>NOT NULL</></term>
       <listitem>
        <para>
-        Values of this domain are normally prevented from being null.
-        However, it is still possible for a domain with this constraint
-        to take a null value if it is assigned a matching domain type
-        that has become null, e.g. via a LEFT OUTER JOIN, or
-        <command>INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM
-        tab WHERE false))</command>.
+        Values of this domain are prevented from being null
+        (but see notes below).
        </para>
       </listitem>
      </varlistentry>
@@ -171,19 +167,56 @@ CREATE DOMAIN <replaceable class="parameter">name</replaceable> [ AS ] <replacea
       which values of the domain must satisfy.
       Each constraint must be an expression
       producing a Boolean result.  It should use the key word <literal>VALUE</>
-      to refer to the value being tested.
+      to refer to the value being tested.  Expressions evaluating
+      to TRUE or UNKNOWN succeed.  If the expression produces a FALSE result,
+      an error is reported and the value is not allowed to be converted
+      to the domain type.
      </para>
 
      <para>
       Currently, <literal>CHECK</literal> expressions cannot contain
       subqueries nor refer to variables other than <literal>VALUE</>.
      </para>
+
+     <para>
+      When a domain has multiple <literal>CHECK</literal> constraints,
+      they will be tested in alphabetical order by name.
+      (<productname>PostgreSQL</> versions before 9.5 did not honor any
+      particular firing order for <literal>CHECK</literal> constraints.)
+     </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </refsect1>
 
  <refsect1>
+  <title>Notes</title>
+
+  <para>
+   Domain constraints, particularly <literal>NOT NULL</>, are checked when
+   converting a value to the domain type.  It is possible for a column that
+   is nominally of the domain type to read as null despite there being such
+   a constraint.  For example, this can happen in an outer-join query, if
+   the domain column is on the nullable side of the outer join.  A more
+   subtle example is
+<programlisting>
+INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false));
+</programlisting>
+   The empty scalar sub-SELECT will produce a null value that is considered
+   to be of the domain type, so no further constraint checking is applied
+   to it, and the insertion will succeed.
+  </para>
+
+  <para>
+   It is very difficult to avoid such problems, because of SQL's general
+   assumption that NULL is a valid value of every datatype.  Best practice
+   therefore is to design a domain's constraints so that NULL is allowed,
+   and then to apply column <literal>NOT NULL</> constraints to columns of
+   the domain type as needed, rather than directly to the domain type.
+  </para>
+ </refsect1>
+
+ <refsect1>
   <title>Examples</title>
 
   <para>