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

Commita379061

Browse files
committed
Allow NOT NULL constraints to be added as NOT VALID
This allows them to be added without scanning the table, and validatingthem afterwards without holding access exclusive lock on the table afterany violating rows have been deleted or fixed.Doing ALTER TABLE ... SET NOT NULL for a column that has an invalidnot-null constraint validates that constraint. ALTER TABLE .. VALIDATECONSTRAINT is also supported. There are various checks on whether aninvalid constraint is allowed in a child table when the parent table hasa valid constraint; this should match what we do for enforced/notenforced constraints.pg_attribute.attnotnull is now only an indicator for whether a not-nullconstraint exists for the column; whether it's valid or invalid must bequeried in pg_constraint. Applications can continue to querypg_attribute.attnotnull as before, but now it's possible that NULL rowsare present in the column even when that's set to true.For backend internal purposes, we cache the nullability status inCompactAttribute->attnullability that each tuple descriptor carries(replacing CompactAttribute.attnotnull, which was a mirror ofForm_pg_attribute.attnotnull). During the initial tuple descriptorcreation, based on the pg_attribute scan, we set this to UNRESTRICTED ifpg_attribute.attnotnull is false, or to UNKNOWN if it's true; then weupdate the latter to VALID or INVALID depending on the pg_constraintscan. This flag is also copied when tupledescs are copied.Comparing tuple descs for equality must also compare theCompactAttribute.attnullability flag and return false in case of amismatch.pg_dump deals with these constraints by storing the OIDs of invalidnot-null constraints in a separate array, and running a query to obtaintheir properties. The regular table creation SQL omits them entirely.They are then dealt with in the same way as "separate" CHECKconstraints, and dumped after the data has been loaded. Because noadditional pg_dump infrastructure was required, we don't bump itsversion number.I decided not to bump catversion either, because the old catalog stateworks perfectly in the new world. (Trying to run with new catalog stateand the old server version would likely run into issues, however.)System catalogs do not support invalid not-null constraints (becausecommit14e87ff didn't allow them to have pg_constraint rowsanyway.)Author: Rushabh Lathia <rushabh.lathia@gmail.com>Author: Jian He <jian.universality@gmail.com>Reviewed-by: Álvaro Herrera <alvherre@alvh.no-ip.org>Tested-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>Discussion:https://postgr.es/m/CAGPqQf0KitkNack4F5CFkFi-9Dqvp29Ro=EpcWt=4_hs-Rt+bQ@mail.gmail.com
1 parentb52a4a5 commita379061

File tree

21 files changed

+1109
-111
lines changed

21 files changed

+1109
-111
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1260,7 +1260,7 @@
12601260
<structfield>attnotnull</structfield> <type>bool</type>
12611261
</para>
12621262
<para>
1263-
This column has a not-null constraint.
1263+
This column has a(possibly invalid)not-null constraint.
12641264
</para></entry>
12651265
</row>
12661266

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

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -243,6 +243,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
243243
entire table; however, if a valid <literal>CHECK</literal> constraint is
244244
found which proves no <literal>NULL</literal> can exist, then the
245245
table scan is skipped.
246+
If a column has an invalid not-null constraint,
247+
<literal>SET NOT NULL</literal> validates it.
246248
</para>
247249

248250
<para>
@@ -458,8 +460,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
458460
<para>
459461
This form adds a new constraint to a table using the same constraint
460462
syntax as <link linkend="sql-createtable"><command>CREATE TABLE</command></link>, plus the option <literal>NOT
461-
VALID</literal>, which is currently only allowed for foreign key
462-
andCHECK constraints.
463+
VALID</literal>, which is currently only allowed for foreign key,
464+
<literal>CHECK</literal> constraintsandnot-null constraints.
463465
</para>
464466

465467
<para>
@@ -586,7 +588,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
586588
<term><literal>VALIDATE CONSTRAINT</literal></term>
587589
<listitem>
588590
<para>
589-
This form validates a foreign keyorcheck constraint that was
591+
This form validates a foreign key, check,ornot-null constraint that was
590592
previously created as <literal>NOT VALID</literal>, by scanning the
591593
table to ensure there are no rows for which the constraint is not
592594
satisfied. If the constraint is not enforced, an error is thrown.

‎src/backend/access/common/tupdesc.c

Lines changed: 36 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -22,6 +22,7 @@
2222
#include"access/htup_details.h"
2323
#include"access/toast_compression.h"
2424
#include"access/tupdesc_details.h"
25+
#include"catalog/catalog.h"
2526
#include"catalog/pg_collation.h"
2627
#include"catalog/pg_type.h"
2728
#include"common/hashfn.h"
@@ -74,7 +75,16 @@ populate_compact_attribute_internal(Form_pg_attribute src,
7475
dst->atthasmissing=src->atthasmissing;
7576
dst->attisdropped=src->attisdropped;
7677
dst->attgenerated= (src->attgenerated!='\0');
77-
dst->attnotnull=src->attnotnull;
78+
79+
/*
80+
* Assign nullability status for this column. Assuming that a constraint
81+
* exists, at this point we don't know if a not-null constraint is valid,
82+
* so we assign UNKNOWN unless the table is a catalog, in which case we
83+
* know it's valid.
84+
*/
85+
dst->attnullability= !src->attnotnull ?ATTNULLABLE_UNRESTRICTED :
86+
IsCatalogRelationOid(src->attrelid) ?ATTNULLABLE_VALID :
87+
ATTNULLABLE_UNKNOWN;
7888

7989
switch (src->attalign)
8090
{
@@ -144,9 +154,10 @@ verify_compact_attribute(TupleDesc tupdesc, int attnum)
144154

145155
/*
146156
* Make the attcacheoff match since it's been reset to -1 by
147-
* populate_compact_attribute_internal.
157+
* populate_compact_attribute_internal. Same with attnullability.
148158
*/
149159
tmp.attcacheoff=cattr->attcacheoff;
160+
tmp.attnullability=cattr->attnullability;
150161

151162
/* Check the freshly populated CompactAttribute matches the TupleDesc's */
152163
Assert(memcmp(&tmp,cattr,sizeof(CompactAttribute))==0);
@@ -333,8 +344,13 @@ CreateTupleDescCopyConstr(TupleDesc tupdesc)
333344
desc->natts*sizeof(FormData_pg_attribute));
334345

335346
for (i=0;i<desc->natts;i++)
347+
{
336348
populate_compact_attribute(desc,i);
337349

350+
TupleDescCompactAttr(desc,i)->attnullability=
351+
TupleDescCompactAttr(tupdesc,i)->attnullability;
352+
}
353+
338354
/* Copy the TupleConstr data structure, if any */
339355
if (constr)
340356
{
@@ -613,6 +629,24 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2)
613629
return false;
614630
if (attr1->attnotnull!=attr2->attnotnull)
615631
return false;
632+
633+
/*
634+
* When the column has a not-null constraint, we also need to consider
635+
* its validity aspect, which only manifests in CompactAttribute->
636+
* attnullability, so verify that.
637+
*/
638+
if (attr1->attnotnull)
639+
{
640+
CompactAttribute*cattr1=TupleDescCompactAttr(tupdesc1,i);
641+
CompactAttribute*cattr2=TupleDescCompactAttr(tupdesc2,i);
642+
643+
Assert(cattr1->attnullability!=ATTNULLABLE_UNKNOWN);
644+
Assert((cattr1->attnullability==ATTNULLABLE_UNKNOWN)==
645+
(cattr2->attnullability==ATTNULLABLE_UNKNOWN));
646+
647+
if (cattr1->attnullability!=cattr2->attnullability)
648+
return false;
649+
}
616650
if (attr1->atthasdef!=attr2->atthasdef)
617651
return false;
618652
if (attr1->attidentity!=attr2->attidentity)

‎src/backend/catalog/heap.c

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2616,12 +2616,17 @@ AddRelationNewConstraints(Relation rel,
26162616
errmsg("cannot add not-null constraint on system column \"%s\"",
26172617
strVal(linitial(cdef->keys))));
26182618

2619+
Assert(cdef->initially_valid!=cdef->skip_validation);
2620+
26192621
/*
26202622
* If the column already has a not-null constraint, we don't want
2621-
* to add another one; just adjust inheritance status as needed.
2623+
* to add another one; adjust inheritance status as needed. This
2624+
* also checks whether the existing constraint matches the
2625+
* requested validity.
26222626
*/
26232627
if (AdjustNotNullInheritance(RelationGetRelid(rel),colnum,
2624-
is_local,cdef->is_no_inherit))
2628+
is_local,cdef->is_no_inherit,
2629+
cdef->skip_validation))
26252630
continue;
26262631

26272632
/*

‎src/backend/catalog/pg_constraint.c

Lines changed: 30 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -576,8 +576,8 @@ ChooseConstraintName(const char *name1, const char *name2,
576576

577577
/*
578578
* Find and return a copy of the pg_constraint tuple that implements a
579-
*validated not-null constraint for the given column of the given relation.
580-
* If no such constraint exists, return NULL.
579+
*(possibly not valid) not-null constraint for the given column of the
580+
*given relation.If no such constraint exists, return NULL.
581581
*
582582
* XXX This would be easier if we had pg_attribute.notnullconstr with the OID
583583
* of the constraint that implements the not-null constraint for that column.
@@ -606,13 +606,11 @@ findNotNullConstraintAttnum(Oid relid, AttrNumber attnum)
606606
AttrNumberconkey;
607607

608608
/*
609-
* We're looking for a NOTNULL constraintthat's marked validated,
610-
*with the column we'relooking for as the sole element in conkey.
609+
* We're looking for a NOTNULL constraintwith the column we're
610+
* looking for as the sole element in conkey.
611611
*/
612612
if (con->contype!=CONSTRAINT_NOTNULL)
613613
continue;
614-
if (!con->convalidated)
615-
continue;
616614

617615
conkey=extractNotNullColumn(conTup);
618616
if (conkey!=attnum)
@@ -630,9 +628,10 @@ findNotNullConstraintAttnum(Oid relid, AttrNumber attnum)
630628
}
631629

632630
/*
633-
* Find and return the pg_constraint tuple that implements a validated
634-
* not-null constraint for the given column of the given relation. If
635-
* no such column or no such constraint exists, return NULL.
631+
* Find and return a copy of the pg_constraint tuple that implements a
632+
* (possibly not valid) not-null constraint for the given column of the
633+
* given relation.
634+
* If no such column or no such constraint exists, return NULL.
636635
*/
637636
HeapTuple
638637
findNotNullConstraint(Oidrelid,constchar*colname)
@@ -723,15 +722,19 @@ extractNotNullColumn(HeapTuple constrTup)
723722
*
724723
* If no not-null constraint is found for the column, return false.
725724
* Caller can create one.
725+
*
726726
* If a constraint exists but the connoinherit flag is not what the caller
727-
* wants, throw an error about the incompatibility. Otherwise, we adjust
728-
* conislocal/coninhcount and return true.
729-
* In the latter case, if is_local is true we flip conislocal true, or do
730-
* nothing if it's already true; otherwise we increment coninhcount by 1.
727+
* wants, throw an error about the incompatibility. If the desired
728+
* constraint is valid but the existing constraint is not valid, also
729+
* throw an error about that (the opposite case is acceptable).
730+
*
731+
* If everything checks out, we adjust conislocal/coninhcount and return
732+
* true. If is_local is true we flip conislocal true, or do nothing if
733+
* it's already true; otherwise we increment coninhcount by 1.
731734
*/
732735
bool
733736
AdjustNotNullInheritance(Oidrelid,AttrNumberattnum,
734-
boolis_local,boolis_no_inherit)
737+
boolis_local,boolis_no_inherit,boolis_notvalid)
735738
{
736739
HeapTupletup;
737740

@@ -755,6 +758,17 @@ AdjustNotNullInheritance(Oid relid, AttrNumber attnum,
755758
errmsg("cannot change NO INHERIT status of NOT NULL constraint \"%s\" on relation \"%s\"",
756759
NameStr(conform->conname),get_rel_name(relid)));
757760

761+
/*
762+
* Throw an error if the existing constraint is NOT VALID and caller
763+
* wants a valid one.
764+
*/
765+
if (!is_notvalid&& !conform->convalidated)
766+
ereport(ERROR,
767+
errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
768+
errmsg("incompatible NOT VALID constraint \"%s\" on relation \"%s\"",
769+
NameStr(conform->conname),get_rel_name(relid)),
770+
errhint("You will need to use ALTER TABLE ... VALIDATE CONSTRAINT to validate it."));
771+
758772
if (!is_local)
759773
{
760774
if (pg_add_s16_overflow(conform->coninhcount,1,
@@ -832,7 +846,7 @@ RelationGetNotNullConstraints(Oid relid, bool cooked, bool include_noinh)
832846
cooked->attnum=colnum;
833847
cooked->expr=NULL;
834848
cooked->is_enforced= true;
835-
cooked->skip_validation=false;
849+
cooked->skip_validation=!conForm->convalidated;
836850
cooked->is_local= true;
837851
cooked->inhcount=0;
838852
cooked->is_no_inherit=conForm->connoinherit;
@@ -852,7 +866,7 @@ RelationGetNotNullConstraints(Oid relid, bool cooked, bool include_noinh)
852866
constr->keys=list_make1(makeString(get_attname(relid,colnum,
853867
false)));
854868
constr->is_enforced= true;
855-
constr->skip_validation=false;
869+
constr->skip_validation=!conForm->convalidated;
856870
constr->initially_valid= true;
857871
constr->is_no_inherit=conForm->connoinherit;
858872
notnulls=lappend(notnulls,constr);

0 commit comments

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