Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit49b83f6

Browse files
committed
Doc: back-patch documentation about limitations of CHECK constraints.
Back-patch commits36d442a and1f66c65 into all supportedbranches. I'd considered doing this when putting in the lattercommit, but failed to pull the trigger. Now that we've had anactual 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 parent2ed302a commit49b83f6

File tree

5 files changed

+151
-33
lines changed

5 files changed

+151
-33
lines changed

‎doc/src/sgml/ddl.sgml‎

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

408461
<sect2>

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

Lines changed: 18 additions & 3 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</literal>, that is,verifythat alldata in columns using the
122-
domain satisfy the specified constraint.
121+
<literal>NOT VALID</literal>, that is,it verifiesthat allvalues in
122+
table columns of thedomain type satisfy the specified constraint.
123123
</para>
124124
</listitem>
125125
</varlistentry>
@@ -202,7 +202,7 @@ ALTER DOMAIN <replaceable class="parameter">name</replaceable>
202202
<term><literal>NOT VALID</literal></term>
203203
<listitem>
204204
<para>
205-
Do not verify existingcolumn data for constraint validity.
205+
Do not verify existingstored data for constraint validity.
206206
</para>
207207
</listitem>
208208
</varlistentry>
@@ -272,6 +272,21 @@ ALTER DOMAIN <replaceable class="parameter">name</replaceable>
272272
<refsect1>
273273
<title>Notes</title>
274274

275+
<para>
276+
Although <command>ALTER DOMAIN ADD CONSTRAINT</command> attempts to verify
277+
that existing stored data satisfies the new constraint, this check is not
278+
bulletproof, because the command cannot <quote>see</quote> table rows that
279+
are newly inserted or updated and not yet committed. If there is a hazard
280+
that concurrent operations might insert bad data, the way to proceed is to
281+
add the constraint using the <literal>NOT VALID</literal> option, commit
282+
that command, wait until all transactions started before that commit have
283+
finished, and then issue <command>ALTER DOMAIN VALIDATE
284+
CONSTRAINT</command> to search for data violating the constraint. This
285+
method is reliable because once the constraint is committed, all new
286+
transactions are guaranteed to enforce it against new values of the domain
287+
type.
288+
</para>
289+
275290
<para>
276291
Currently, <command>ALTER DOMAIN ADD CONSTRAINT</command>, <command>ALTER
277292
DOMAIN VALIDATE CONSTRAINT</command>, and <command>ALTER DOMAIN SET NOT

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

Lines changed: 54 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -359,27 +359,36 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
359359
<xref linkend="sql-createtable"/>, plus the option <literal>NOT
360360
VALID</literal>, which is currently only allowed for foreign key
361361
and CHECK constraints.
362-
If the constraint is marked <literal>NOT VALID</literal>, the
363-
potentially-lengthy initial check to verify that all rows in the table
364-
satisfy the constraint is skipped. The constraint will still be
362+
</para>
363+
364+
<para>
365+
Normally, this form will cause a scan of the table to verify that all
366+
existing rows in the table satisfy the new constraint. But if
367+
the <literal>NOT VALID</literal> option is used, this
368+
potentially-lengthy scan is skipped. The constraint will still be
365369
enforced against subsequent inserts or updates (that is, they'll fail
366370
unless there is a matching row in the referenced table, in the case
367-
of foreign keys; and they'll fail unless the new row matches the
368-
specified checkconstraints). But the
371+
of foreign keys, or they'll fail unless the new row matches the
372+
specified checkcondition). But the
369373
database will not assume that the constraint holds for all rows in
370374
the table, until it is validated by using the <literal>VALIDATE
371-
CONSTRAINT</literal> option. Foreign key constraints on partitioned
372-
tables may not be declared <literal>NOT VALID</literal> at present.
375+
CONSTRAINT</literal> option.
376+
See <xref linkend="sql-altertable-notes"
377+
endterm="sql-altertable-notes-title"/> below for more information
378+
about using the <literal>NOT VALID</literal> option.
373379
</para>
374380

375381
<para>
376-
The addition of a foreign key constraint requires a
377-
<literal>SHARE ROW EXCLUSIVE</literal> lock on the referenced table.
382+
Addition of a foreign key constraint requires a
383+
<literal>SHARE ROW EXCLUSIVE</literal> lock on the referenced table,
384+
in addition to the lock on the table receiving the constraint.
378385
</para>
379386

380387
<para>
381388
Additional restrictions apply when unique or primary key constraints
382-
are added to partitioned tables; see <xref linkend="sql-createtable" />.
389+
are added to partitioned tables; see <xref linkend="sql-createtable"/>.
390+
Also, foreign key constraints on partitioned
391+
tables may not be declared <literal>NOT VALID</literal> at present.
383392
</para>
384393

385394
</listitem>
@@ -454,23 +463,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
454463
<term><literal>VALIDATE CONSTRAINT</literal></term>
455464
<listitem>
456465
<para>
457-
This form validates a foreign key or check constraint that was previously created
458-
as <literal>NOT VALID</literal>, by scanning the table to ensure there
459-
are no rows for which the constraint is not satisfied.
460-
Nothing happens if the constraint is already marked valid.
461-
</para>
462-
<para>
463-
Validation can be a long process on larger tables. The value of separating
464-
validation from initial creation is that you can defer validation to less
465-
busy times, or can be used to give additional time to correct pre-existing
466-
errors while preventing new errors. Note also that validation on its own
467-
does not prevent normal write commands against the table while it runs.
468-
</para>
469-
<para>
470-
Validation acquires only a <literal>SHARE UPDATE EXCLUSIVE</literal> lock
471-
on the table being altered. If the constraint is a foreign key then
472-
a <literal>ROW SHARE</literal> lock is also required on
473-
the table referenced by the constraint.
466+
This form validates a foreign key or check constraint that was
467+
previously created as <literal>NOT VALID</literal>, by scanning the
468+
table to ensure there are no rows for which the constraint is not
469+
satisfied. Nothing happens if the constraint is already marked valid.
470+
(See <xref linkend="sql-altertable-notes"
471+
endterm="sql-altertable-notes-title"/> below for an explanation of the
472+
usefulness of this command.)
474473
</para>
475474
</listitem>
476475
</varlistentry>
@@ -1190,8 +1189,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
11901189
</variablelist>
11911190
</refsect1>
11921191

1193-
<refsect1>
1194-
<title>Notes</title>
1192+
<refsect1 id="sql-altertable-notes">
1193+
<title id="sql-altertable-notes-title">Notes</title>
11951194

11961195
<para>
11971196
The key word <literal>COLUMN</literal> is noise and can be omitted.
@@ -1237,6 +1236,32 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
12371236
rewrites can thereby be combined into a single pass over the table.
12381237
</para>
12391238

1239+
<para>
1240+
Scanning a large table to verify a new foreign key or check constraint
1241+
can take a long time, and other updates to the table are locked out
1242+
until the <command>ALTER TABLE ADD CONSTRAINT</command> command is
1243+
committed. The main purpose of the <literal>NOT VALID</literal>
1244+
constraint option is to reduce the impact of adding a constraint on
1245+
concurrent updates. With <literal>NOT VALID</literal>,
1246+
the <command>ADD CONSTRAINT</command> command does not scan the table
1247+
and can be committed immediately. After that, a <literal>VALIDATE
1248+
CONSTRAINT</literal> command can be issued to verify that existing rows
1249+
satisfy the constraint. The validation step does not need to lock out
1250+
concurrent updates, since it knows that other transactions will be
1251+
enforcing the constraint for rows that they insert or update; only
1252+
pre-existing rows need to be checked. Hence, validation acquires only
1253+
a <literal>SHARE UPDATE EXCLUSIVE</literal> lock on the table being
1254+
altered. (If the constraint is a foreign key then a <literal>ROW
1255+
SHARE</literal> lock is also required on the table referenced by the
1256+
constraint.) In addition to improving concurrency, it can be useful to
1257+
use <literal>NOT VALID</literal> and <literal>VALIDATE
1258+
CONSTRAINT</literal> in cases where the table is known to contain
1259+
pre-existing violations. Once the constraint is in place, no new
1260+
violations can be inserted, and the existing problems can be corrected
1261+
at leisure until <literal>VALIDATE CONSTRAINT</literal> finally
1262+
succeeds.
1263+
</para>
1264+
12401265
<para>
12411266
The <literal>DROP COLUMN</literal> form does not physically remove
12421267
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
@@ -214,6 +214,30 @@ INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false));
214214
and then to apply column <literal>NOT NULL</literal> constraints to columns of
215215
the domain type as needed, rather than directly to the domain type.
216216
</para>
217+
218+
<para>
219+
<productname>PostgreSQL</productname> assumes that
220+
<literal>CHECK</literal> constraints' conditions are immutable, that is,
221+
they will always give the same result for the same input value. This
222+
assumption is what justifies examining <literal>CHECK</literal>
223+
constraints only when a value is first converted to be of a domain type,
224+
and not at other times. (This is essentially the same as the treatment
225+
of table <literal>CHECK</literal> constraints, as described in
226+
<xref linkend="ddl-constraints-check-constraints"/>.)
227+
</para>
228+
229+
<para>
230+
An example of a common way to break this assumption is to reference a
231+
user-defined function in a <literal>CHECK</literal> expression, and then
232+
change the behavior of that
233+
function. <productname>PostgreSQL</productname> does not disallow that,
234+
but it will not notice if there are stored values of the domain type that
235+
now violate the <literal>CHECK</literal> constraint. That would cause a
236+
subsequent database dump and reload to fail. The recommended way to
237+
handle such a change is to drop the constraint (using <command>ALTER
238+
DOMAIN</command>), adjust the function definition, and re-add the
239+
constraint, thereby rechecking it against stored data.
240+
</para>
217241
</refsect1>
218242

219243
<refsect1>

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

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -698,7 +698,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
698698
<para>
699699
Currently, <literal>CHECK</literal> expressions cannot contain
700700
subqueries nor refer to variables other than columns of the
701-
current row. The system column <literal>tableoid</literal>
701+
current row (see <xref linkend="ddl-constraints-check-constraints"/>).
702+
The system column <literal>tableoid</literal>
702703
may be referenced, but not any other system column.
703704
</para>
704705

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp