On 11/15/18 00:02, Tom Lane wrote:
> I think this could be improved some more. Perhaps something like this
> (I've not bothered with markup...)
>
>
> This is a little verbose maybe, but as the text stands, it sounds like
> using a trigger is enough to solve all the consistency problems that
> a cross-row CHECK has. Which it's not of course.
Thank you for the rewriting, this is much more clear and explicit that way.
> I'm also wondering whether it's better to put this in the CREATE TABLE
> reference page instead of here. While there are certainly benefits in
> having the caveat here, I'm a bit troubled by the number of forward
> references to concepts that are described later. OTOH, a lot of people
> who need the warning might never see it if it's buried in the reference
> material.
To address your remark, I added a small message in the CREATE TABLE
reference page to be more explicit about the topic, so that it would be
a warning for the users reading the section. And then a reference to the
CHECK constraint page where the full explanation is to be located.
That way, the caveat is mentioned in both pages, but the full
explanation is located only on a single page.
Please, let me know if this is good enough or maybe if I missed
something.
Patrick Francelle
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 61c4a25460..bfe89ef8ae 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -403,6 +403,33 @@ CREATE TABLE products (
ensure that a column does not contain null values, the not-null
constraint described in the next section can be used.
</para>
+
+ <note>
+ <para>
+ <productname>PostgreSQL</productname> does not support
+ <literal>CHECK</literal> constraints that reference table data other than
+ the new or updated row being checked. While a <literal>CHECK</literal>
+ constraint that violates this rule may appear to work in simple
+ tests, it cannot guarantee that the database will not reach a state
+ in which the constraint condition is false (due to subsequent changes
+ of the other row(s) involved). This would cause a database dump and
+ reload to fail. The reload could fail even when the complete
+ database state is consistent with the constraint, due to rows not
+ being loaded in an order that will satisfy the constraint. If
+ possible, use <literal>UNIQUE</literal>, <literal>EXCLUDE</literal>,
+ or <literal>FOREIGN KEY</literal> constraints to express
+ cross-row and cross-table restrictions.
+ </para>
+
+ <para>
+ If what you desire is a one-time check against other rows at row
+ insertion, rather than a continuously-maintained consistency
+ guarantee, a custom <link linkend="triggers">trigger</link> can be used
+ to implement that. (This approach avoids the dump/reload problem because
+ <application>pg_dump</application> does not reinstall triggers until after
+ reloading data, so that the check will not be enforced during a dump/reload.)
+ </para>
+ </note>
</sect2>
<sect2>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 50d5597002..d6d1191ddd 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -756,6 +756,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
subqueries nor refer to variables other than columns of the
current row. The system column <literal>tableoid</literal>
may be referenced, but not any other system column.
+ Also, <literal>CHECK</literal> constraints that references other tables
+ are not supported (see <xref linkend="ddl-constraints-check-constraints"/>).
</para>
<para>
