Skip to content

Commit 3ddd8ee

Browse files
tglsfdctglsfdc
committed
Doc: back-patch documentation about limitations of CHECK constraints.
Back-patch commits 36d442a and 1f66c65 into all supported branches. I'd considered doing this when putting in the latter commit, but failed to pull the trigger. Now that we've had an actual field complaint about the lack of such docs, let's do it. Per bug #16158 from Piotr Jander. Original patches by Lætitia Avrot, Patrick Francelle, and me. Discussion: https://postgr.es/m/16158-7ccf2f74b3d655db@postgresql.org
1 parent 7309e75 commit 3ddd8ee

File tree

5 files changed

+147
-30
lines changed

5 files changed

+147
-30
lines changed

‎doc/src/sgml/ddl.sgml

Lines changed: 53 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -402,6 +402,59 @@ CREATE TABLE products (
402402
ensure that a column does not contain null values, the not-null
403403
constraint described in the next section can be used.
404404
</para>
405+
406+
<note>
407+
<para>
408+
<productname>PostgreSQL</productname> does not support
409+
<literal>CHECK</literal> constraints that reference table data other than
410+
the new or updated row being checked. While a <literal>CHECK</literal>
411+
constraint that violates this rule may appear to work in simple
412+
tests, it cannot guarantee that the database will not reach a state
413+
in which the constraint condition is false (due to subsequent changes
414+
of the other row(s) involved). This would cause a database dump and
415+
reload to fail. The reload could fail even when the complete
416+
database state is consistent with the constraint, due to rows not
417+
being loaded in an order that will satisfy the constraint. If
418+
possible, use <literal>UNIQUE</literal>, <literal>EXCLUDE</literal>,
419+
or <literal>FOREIGN KEY</literal> constraints to express
420+
cross-row and cross-table restrictions.
421+
</para>
422+
423+
<para>
424+
If what you desire is a one-time check against other rows at row
425+
insertion, rather than a continuously-maintained consistency
426+
guarantee, a custom <link linkend="triggers">trigger</link> can be used
427+
to implement that. (This approach avoids the dump/reload problem because
428+
<application>pg_dump</application> does not reinstall triggers until after
429+
reloading data, so that the check will not be enforced during a
430+
dump/reload.)
431+
</para>
432+
</note>
433+
434+
<note>
435+
<para>
436+
<productname>PostgreSQL</productname> assumes that
437+
<literal>CHECK</literal> constraints' conditions are immutable, that
438+
is, they will always give the same result for the same input row.
439+
This assumption is what justifies examining <literal>CHECK</literal>
440+
constraints only when rows are inserted or updated, and not at other
441+
times. (The warning above about not referencing other table data is
442+
really a special case of this restriction.)
443+
</para>
444+
445+
<para>
446+
An example of a common way to break this assumption is to reference a
447+
user-defined function in a <literal>CHECK</literal> expression, and
448+
then change the behavior of that
449+
function. <productname>PostgreSQL</productname> does not disallow
450+
that, but it will not notice if there are rows in the table that now
451+
violate the <literal>CHECK</literal> constraint. That would cause a
452+
subsequent database dump and reload to fail.
453+
The recommended way to handle such a change is to drop the constraint
454+
(using <command>ALTER TABLE</command>), adjust the function definition,
455+
and re-add the constraint, thereby rechecking it against all table rows.
456+
</para>
457+
</note>
405458
</sect2>
406459

407460
<sect2>

‎doc/src/sgml/ref/alter_domain.sgml

Lines changed: 21 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -118,8 +118,8 @@ ALTER DOMAIN <replaceable class="PARAMETER">name</replaceable>
118118
<listitem>
119119
<para>
120120
This form validates a constraint previously added as
121-
<literal>NOT VALID</>, that is, verify that all data in columns using the
122-
domain satisfy the specified constraint.
121+
<literal>NOT VALID</literal>, that is, it verifies that all values in
122+
table columns of the domain type satisfy the specified constraint.
123123
</para>
124124
</listitem>
125125
</varlistentry>
@@ -202,7 +202,7 @@ ALTER DOMAIN <replaceable class="PARAMETER">name</replaceable>
202202
<term><replaceable class="PARAMETER">NOT VALID</replaceable></term>
203203
<listitem>
204204
<para>
205-
Do not verify existing column data for constraint validity.
205+
Do not verify existing stored data for constraint validity.
206206
</para>
207207
</listitem>
208208
</varlistentry>
@@ -271,8 +271,24 @@ ALTER DOMAIN <replaceable class="PARAMETER">name</replaceable>
271271
<title>Notes</title>
272272

273273
<para>
274-
Currently, <command>ALTER DOMAIN ADD CONSTRAINT</> and
275-
<command>ALTER DOMAIN SET NOT NULL</> will fail if the named domain or
274+
Although <command>ALTER DOMAIN ADD CONSTRAINT</command> attempts to verify
275+
that existing stored data satisfies the new constraint, this check is not
276+
bulletproof, because the command cannot <quote>see</quote> table rows that
277+
are newly inserted or updated and not yet committed. If there is a hazard
278+
that concurrent operations might insert bad data, the way to proceed is to
279+
add the constraint using the <literal>NOT VALID</literal> option, commit
280+
that command, wait until all transactions started before that commit have
281+
finished, and then issue <command>ALTER DOMAIN VALIDATE
282+
CONSTRAINT</command> to search for data violating the constraint. This
283+
method is reliable because once the constraint is committed, all new
284+
transactions are guaranteed to enforce it against new values of the domain
285+
type.
286+
</para>
287+
288+
<para>
289+
Currently, <command>ALTER DOMAIN ADD CONSTRAINT</>, <command>ALTER
290+
DOMAIN VALIDATE CONSTRAINT</>, and <command>ALTER DOMAIN SET NOT NULL</>
291+
will fail if the validated named domain or
276292
any derived domain is used within a composite-type column of any
277293
table in the database. They should eventually be improved to be
278294
able to verify the new constraint for such nested columns.

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 47 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -266,16 +266,23 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
266266
<xref linkend="SQL-CREATETABLE">, plus the option <literal>NOT
267267
VALID</literal>, which is currently only allowed for foreign key
268268
and CHECK constraints.
269-
If the constraint is marked <literal>NOT VALID</literal>, the
270-
potentially-lengthy initial check to verify that all rows in the table
271-
satisfy the constraint is skipped. The constraint will still be
269+
</para>
270+
271+
<para>
272+
Normally, this form will cause a scan of the table to verify that all
273+
existing rows in the table satisfy the new constraint. But if
274+
the <literal>NOT VALID</literal> option is used, this
275+
potentially-lengthy scan is skipped. The constraint will still be
272276
enforced against subsequent inserts or updates (that is, they'll fail
273277
unless there is a matching row in the referenced table, in the case
274-
of foreign keys; and they'll fail unless the new row matches the
275-
specified check constraints). But the
278+
of foreign keys, or they'll fail unless the new row matches the
279+
specified check condition). But the
276280
database will not assume that the constraint holds for all rows in
277281
the table, until it is validated by using the <literal>VALIDATE
278282
CONSTRAINT</literal> option.
283+
See <xref linkend="sql-altertable-notes"
284+
endterm="sql-altertable-notes-title"> below for more information
285+
about using the <literal>NOT VALID</literal> option.
279286
</para>
280287
</listitem>
281288
</varlistentry>
@@ -345,23 +352,13 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
345352
<term><literal>VALIDATE CONSTRAINT</literal></term>
346353
<listitem>
347354
<para>
348-
This form validates a foreign key or check constraint that was previously created
349-
as <literal>NOT VALID</literal>, by scanning the table to ensure there
350-
are no rows for which the constraint is not satisfied.
351-
Nothing happens if the constraint is already marked valid.
352-
</para>
353-
<para>
354-
Validation can be a long process on larger tables. The value of separating
355-
validation from initial creation is that you can defer validation to less
356-
busy times, or can be used to give additional time to correct pre-existing
357-
errors while preventing new errors. Note also that validation on its own
358-
does not prevent normal write commands against the table while it runs.
359-
</para>
360-
<para>
361-
Validation acquires only a <literal>SHARE UPDATE EXCLUSIVE</literal> lock
362-
on the table being altered. If the constraint is a foreign key then
363-
a <literal>ROW SHARE</literal> lock is also required on
364-
the table referenced by the constraint.
355+
This form validates a foreign key or check constraint that was
356+
previously created as <literal>NOT VALID</literal>, by scanning the
357+
table to ensure there are no rows for which the constraint is not
358+
satisfied. Nothing happens if the constraint is already marked valid.
359+
(See <xref linkend="sql-altertable-notes"
360+
endterm="sql-altertable-notes-title"> below for an explanation of the
361+
usefulness of this command.)
365362
</para>
366363
</listitem>
367364
</varlistentry>
@@ -883,8 +880,8 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
883880
</variablelist>
884881
</refsect1>
885882

886-
<refsect1>
887-
<title>Notes</title>
883+
<refsect1 id="sql-altertable-notes">
884+
<title id="sql-altertable-notes-title">Notes</title>
888885

889886
<para>
890887
The key word <literal>COLUMN</literal> is noise and can be omitted.
@@ -925,6 +922,32 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
925922
rewrites can thereby be combined into a single pass over the table.
926923
</para>
927924

925+
<para>
926+
Scanning a large table to verify a new foreign key or check constraint
927+
can take a long time, and other updates to the table are locked out
928+
until the <command>ALTER TABLE ADD CONSTRAINT</command> command is
929+
committed. The main purpose of the <literal>NOT VALID</literal>
930+
constraint option is to reduce the impact of adding a constraint on
931+
concurrent updates. With <literal>NOT VALID</literal>,
932+
the <command>ADD CONSTRAINT</command> command does not scan the table
933+
and can be committed immediately. After that, a <literal>VALIDATE
934+
CONSTRAINT</literal> command can be issued to verify that existing rows
935+
satisfy the constraint. The validation step does not need to lock out
936+
concurrent updates, since it knows that other transactions will be
937+
enforcing the constraint for rows that they insert or update; only
938+
pre-existing rows need to be checked. Hence, validation acquires only
939+
a <literal>SHARE UPDATE EXCLUSIVE</literal> lock on the table being
940+
altered. (If the constraint is a foreign key then a <literal>ROW
941+
SHARE</literal> lock is also required on the table referenced by the
942+
constraint.) In addition to improving concurrency, it can be useful to
943+
use <literal>NOT VALID</literal> and <literal>VALIDATE
944+
CONSTRAINT</literal> in cases where the table is known to contain
945+
pre-existing violations. Once the constraint is in place, no new
946+
violations can be inserted, and the existing problems can be corrected
947+
at leisure until <literal>VALIDATE CONSTRAINT</literal> finally
948+
succeeds.
949+
</para>
950+
928951
<para>
929952
The <literal>DROP COLUMN</literal> form does not physically remove
930953
the column, but simply makes it invisible to SQL operations. Subsequent

‎doc/src/sgml/ref/create_domain.sgml

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -216,6 +216,30 @@ CREATE TABLE us_snail_addy (
216216
The command <command>CREATE DOMAIN</command> conforms to the SQL
217217
standard.
218218
</para>
219+
220+
<para>
221+
<productname>PostgreSQL</productname> assumes that
222+
<literal>CHECK</literal> constraints' conditions are immutable, that is,
223+
they will always give the same result for the same input value. This
224+
assumption is what justifies examining <literal>CHECK</literal>
225+
constraints only when a value is first converted to be of a domain type,
226+
and not at other times. (This is essentially the same as the treatment
227+
of table <literal>CHECK</literal> constraints, as described in
228+
<xref linkend="ddl-constraints-check-constraints">.)
229+
</para>
230+
231+
<para>
232+
An example of a common way to break this assumption is to reference a
233+
user-defined function in a <literal>CHECK</literal> expression, and then
234+
change the behavior of that
235+
function. <productname>PostgreSQL</productname> does not disallow that,
236+
but it will not notice if there are stored values of the domain type that
237+
now violate the <literal>CHECK</literal> constraint. That would cause a
238+
subsequent database dump and reload to fail. The recommended way to
239+
handle such a change is to drop the constraint (using <command>ALTER
240+
DOMAIN</command>), adjust the function definition, and re-add the
241+
constraint, thereby rechecking it against stored data.
242+
</para>
219243
</refsect1>
220244

221245
<refsect1 id="SQL-CREATEDOMAIN-see-also">

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -442,7 +442,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
442442
<para>
443443
Currently, <literal>CHECK</literal> expressions cannot contain
444444
subqueries nor refer to variables other than columns of the
445-
current row. The system column <literal>tableoid</literal>
445+
current row (see <xref linkend="ddl-constraints-check-constraints">).
446+
The system column <literal>tableoid</literal>
446447
may be referenced, but not any other system column.
447448
</para>
448449

0 commit comments

Comments
 (0)