public inbox for [email protected]
help / color / mirror / Atom feedFrom: Rushabh Lathia <[email protected]>
To: PostgreSQL Hackers <[email protected]>
To: Alvaro Herrera <[email protected]>
Subject: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints
Date: Thu, 6 Feb 2025 18:08:46 +0530
Message-ID: <CAGPqQf0KitkNack4F5CFkFi-9Dqvp29Ro=EpcWt=4_hs-Rt+bQ@mail.gmail.com> (raw)
Hi,
Commit 14e87ffa5c543b5f30ead7413084c25f7735039f
<https://github.com/postgres/postgres/commit/14e87ffa5c543b5f30ead7413084c25f7735039f;
added
the support for named NOT NULL constraints. We can now support the NOT
VALID/VALID named NOT NULL constraints.
This patch supports the NOT VALID and VALIDATE CONSTRAINT for name NOT NULL
constraints. In order to achieve this patch,
1) Converted the pg_attribute.attnotnull to CHAR type, so that it can hold
the INVALID flag for the constraint.
2) Added the support for NOT VALID as well as VALIDATE CONSTRAINT support.
3) Support for pg_dump, where we now dumping the INVALID NOT NULL as
separate from table schemes, just like CHECK Constraints.
4) Added related testcases.
Attaching the patch here.
Thanks Alvaro for your offline help and support for this feature.
Thanks
Rushabh Lathia
www.EnterpriseDB.com
Attachments:
[application/octet-stream] 0003-Support-pg_dump-to-dump-NOT-VALID-named-NOT-NULL-con.patch (10.8K, ../CAGPqQf0KitkNack4F5CFkFi-9Dqvp29Ro=EpcWt=4_hs-Rt+bQ@mail.gmail.com/3-0003-Support-pg_dump-to-dump-NOT-VALID-named-NOT-NULL-con.patch)
download | inline diff:
From e40943c25f48eeb06971ed404002e4e284b0065b Mon Sep 17 00:00:00 2001
From: Rushabh Lathia <[email protected]>
Date: Wed, 5 Feb 2025 15:38:21 +0530
Subject: [PATCH 3/3] Support pg_dump to dump NOT VALID named NOT NULL
constraints.
Dump the INVALID NOT NULL constraint as separate from table schema,
just like CHECK constraints.
---
src/bin/pg_dump/pg_dump.c | 168 ++++++++++++++++++++++++++++++++++++++++++----
src/bin/pg_dump/pg_dump.h | 1 +
2 files changed, 156 insertions(+), 13 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 02e1fdf..319eeab 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -8745,6 +8745,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
PQExpBuffer q = createPQExpBuffer();
PQExpBuffer tbloids = createPQExpBuffer();
PQExpBuffer checkoids = createPQExpBuffer();
+ PQExpBuffer invalidnotnulloids = createPQExpBuffer();
PGresult *res;
int ntups;
int curtblindx;
@@ -8761,6 +8762,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
int i_attlen;
int i_attalign;
int i_attislocal;
+ int i_notnull_valid;
int i_notnull_name;
int i_notnull_noinherit;
int i_notnull_islocal;
@@ -8857,11 +8859,13 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
*/
if (fout->remoteVersion >= 180000)
appendPQExpBufferStr(q,
+ "CASE WHEN a.attnotnull = 'i' THEN 'f' ELSE 't' END AS notnull_valid,\n"
"co.conname AS notnull_name,\n"
"co.connoinherit AS notnull_noinherit,\n"
"co.conislocal AS notnull_islocal,\n");
else
appendPQExpBufferStr(q,
+ "'t' AS notnull_valid,\n"
"CASE WHEN a.attnotnull THEN '' ELSE NULL END AS notnull_name,\n"
"false AS notnull_noinherit,\n"
"a.attislocal AS notnull_islocal,\n");
@@ -8936,6 +8940,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
i_attlen = PQfnumber(res, "attlen");
i_attalign = PQfnumber(res, "attalign");
i_attislocal = PQfnumber(res, "attislocal");
+ i_notnull_valid = PQfnumber(res, "notnull_valid");
i_notnull_name = PQfnumber(res, "notnull_name");
i_notnull_noinherit = PQfnumber(res, "notnull_noinherit");
i_notnull_islocal = PQfnumber(res, "notnull_islocal");
@@ -8955,6 +8960,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
* r is handled by the inner loop.
*/
curtblindx = -1;
+ appendPQExpBufferChar(invalidnotnulloids, '{');
for (int r = 0; r < ntups;)
{
Oid attrelid = atooid(PQgetvalue(res, r, i_attrelid));
@@ -9003,6 +9009,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
tbinfo->attcompression = (char *) pg_malloc(numatts * sizeof(char));
tbinfo->attfdwoptions = (char **) pg_malloc(numatts * sizeof(char *));
tbinfo->attmissingval = (char **) pg_malloc(numatts * sizeof(char *));
+ tbinfo->notnull_valid = (bool *) pg_malloc(numatts * sizeof(bool));
tbinfo->notnull_constrs = (char **) pg_malloc(numatts * sizeof(char *));
tbinfo->notnull_noinh = (bool *) pg_malloc(numatts * sizeof(bool));
tbinfo->notnull_islocal = (bool *) pg_malloc(numatts * sizeof(bool));
@@ -9029,12 +9036,28 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
tbinfo->attlen[j] = atoi(PQgetvalue(res, r, i_attlen));
tbinfo->attalign[j] = *(PQgetvalue(res, r, i_attalign));
tbinfo->attislocal[j] = (PQgetvalue(res, r, i_attislocal)[0] == 't');
+ tbinfo->notnull_valid[j] = (PQgetvalue(res, r, i_notnull_valid)[0] == 't');
- /* Handle not-null constraint name and flags */
- determineNotNullFlags(fout, res, r,
- tbinfo, j,
- i_notnull_name, i_notnull_noinherit,
- i_notnull_islocal);
+ /*
+ * Dump the invalid NOT NULL constrint like the Check constraints
+ */
+ if (tbinfo->notnull_valid[j])
+ /* Handle not-null constraint name and flags */
+ determineNotNullFlags(fout, res, r,
+ tbinfo, j,
+ i_notnull_name, i_notnull_noinherit,
+ i_notnull_islocal);
+ else if(!PQgetisnull(res, r, i_notnull_name))
+ {
+ /*
+ * Add the entry into invalidnotnull list so NOT NULL constraint get
+ * dump as separate constrints.
+ */
+ if (invalidnotnulloids->len > 1) /* do we have more than the '{'? */
+ appendPQExpBufferChar(invalidnotnulloids, ',');
+ appendPQExpBuffer(invalidnotnulloids, "%u", tbinfo->dobj.catId.oid);
+
+ }
tbinfo->attoptions[j] = pg_strdup(PQgetvalue(res, r, i_attoptions));
tbinfo->attcollation[j] = atooid(PQgetvalue(res, r, i_attcollation));
@@ -9056,6 +9079,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
}
PQclear(res);
+ appendPQExpBufferChar(invalidnotnulloids, '}');
/*
* Now get info about column defaults. This is skipped for a data-only
@@ -9319,6 +9343,128 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
PQclear(res);
}
+ /*
+ * Get info about table INVALID NOT NULL constraints. This is skipped for a
+ * data-only dump, as it is only needed for table schemas.
+ */
+ if (dopt->dumpSchema && invalidnotnulloids->len > 2)
+ {
+ ConstraintInfo *constrs;
+ int numConstrs;
+ int i_tableoid;
+ int i_oid;
+ int i_conrelid;
+ int i_conname;
+ int i_consrc;
+ int i_conislocal;
+ int i_convalidated;
+
+ pg_log_info("finding table check constraints");
+
+ resetPQExpBuffer(q);
+ appendPQExpBuffer(q,
+ "SELECT c.tableoid, c.oid, conrelid, conname, "
+ "pg_catalog.pg_get_constraintdef(c.oid) AS consrc, "
+ "conislocal, convalidated "
+ "FROM unnest('%s'::pg_catalog.oid[]) AS src(tbloid)\n"
+ "JOIN pg_catalog.pg_constraint c ON (src.tbloid = c.conrelid)\n"
+ "WHERE contype = 'n' AND convalidated = 'f'"
+ "ORDER BY c.conrelid, c.conname",
+ invalidnotnulloids->data);
+
+ res = ExecuteSqlQuery(fout, q->data, PGRES_TUPLES_OK);
+
+ numConstrs = PQntuples(res);
+ constrs = (ConstraintInfo *) pg_malloc(numConstrs * sizeof(ConstraintInfo));
+
+ i_tableoid = PQfnumber(res, "tableoid");
+ i_oid = PQfnumber(res, "oid");
+ i_conrelid = PQfnumber(res, "conrelid");
+ i_conname = PQfnumber(res, "conname");
+ i_consrc = PQfnumber(res, "consrc");
+ i_conislocal = PQfnumber(res, "conislocal");
+ i_convalidated = PQfnumber(res, "convalidated");
+
+ /* As above, this loop iterates once per table, not once per row */
+ curtblindx = -1;
+ for (int j = 0; j < numConstrs;)
+ {
+ Oid conrelid = atooid(PQgetvalue(res, j, i_conrelid));
+ TableInfo *tbinfo = NULL;
+ int numcons;
+
+ /* Count rows for this table */
+ for (numcons = 1; numcons < numConstrs - j; numcons++)
+ if (atooid(PQgetvalue(res, j + numcons, i_conrelid)) != conrelid)
+ break;
+
+ /*
+ * Locate the associated TableInfo; we rely on tblinfo[] being in
+ * OID order.
+ */
+ while (++curtblindx < numTables)
+ {
+ tbinfo = &tblinfo[curtblindx];
+ if (tbinfo->dobj.catId.oid == conrelid)
+ break;
+ }
+ if (curtblindx >= numTables)
+ pg_fatal("unrecognized table OID %u", conrelid);
+
+
+ tbinfo->checkexprs = constrs + j;
+
+ for (int c = 0; c < numcons; c++, j++)
+ {
+ bool validated = PQgetvalue(res, j, i_convalidated)[0] == 't';
+
+ constrs[j].dobj.objType = DO_CONSTRAINT;
+ constrs[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_tableoid));
+ constrs[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_oid));
+ AssignDumpId(&constrs[j].dobj);
+ constrs[j].dobj.name = pg_strdup(PQgetvalue(res, j, i_conname));
+ constrs[j].dobj.namespace = tbinfo->dobj.namespace;
+ constrs[j].contable = tbinfo;
+ constrs[j].condomain = NULL;
+ constrs[j].contype = 'n';
+ constrs[j].condef = pg_strdup(PQgetvalue(res, j, i_consrc));
+ constrs[j].confrelid = InvalidOid;
+ constrs[j].conindex = 0;
+ constrs[j].condeferrable = false;
+ constrs[j].condeferred = false;
+ constrs[j].conislocal = (PQgetvalue(res, j, i_conislocal)[0] == 't');
+
+ /*
+ * An unvalidated constraint needs to be dumped separately, so
+ * that potentially-violating existing data is loaded before
+ * the constraint.
+ */
+ constrs[j].separate = !validated;
+
+ constrs[j].dobj.dump = tbinfo->dobj.dump;
+
+ /*
+ * Mark the constraint as needing to appear before the table
+ * --- this is so that any other dependencies of the
+ * constraint will be emitted before we try to create the
+ * table. If the constraint is to be dumped separately, it
+ * will be dumped after data is loaded anyway, so don't do it.
+ * (There's an automatic dependency in the opposite direction
+ * anyway, so don't need to add one manually here.)
+ */
+ if (!constrs[j].separate)
+ addObjectDependency(&tbinfo->dobj,
+ constrs[j].dobj.dumpId);
+
+ /*
+ * We will detect later whether the constraint must be split
+ * out from the table definition.
+ */
+ }
+ }
+ PQclear(res);
+ }
+
destroyPQExpBuffer(q);
destroyPQExpBuffer(tbloids);
destroyPQExpBuffer(checkoids);
@@ -16136,7 +16282,8 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
* defined, or if binary upgrade. (In the latter case, we
* reset conislocal below.)
*/
- print_notnull = (tbinfo->notnull_constrs[j] != NULL &&
+ print_notnull = (tbinfo->notnull_valid[j] &&
+ tbinfo->notnull_constrs[j] != NULL &&
(tbinfo->notnull_islocal[j] ||
dopt->binary_upgrade ||
tbinfo->ispartition));
@@ -16195,11 +16342,6 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
tbinfo->attrdefs[j]->adef_expr);
}
- print_notnull = (tbinfo->notnull_constrs[j] != NULL &&
- (tbinfo->notnull_islocal[j] ||
- dopt->binary_upgrade ||
- tbinfo->ispartition));
-
if (print_notnull)
{
if (tbinfo->notnull_constrs[j][0] == '\0')
@@ -17521,9 +17663,9 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo)
.createStmt = q->data,
.dropStmt = delq->data));
}
- else if (coninfo->contype == 'c' && tbinfo)
+ else if ((coninfo->contype == 'c' || coninfo->contype == 'n') && tbinfo)
{
- /* CHECK constraint on a table */
+ /* CHECK/INVALID_NOTNULL constraint on a table */
/* Ignore if not to be dumped separately, or if it was inherited */
if (coninfo->separate && coninfo->conislocal)
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 7139c88..c4e05bd 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -356,6 +356,7 @@ typedef struct _tableInfo
char *attcompression; /* per-attribute compression method */
char **attfdwoptions; /* per-attribute fdw options */
char **attmissingval; /* per attribute missing value */
+ bool *notnull_valid; /* NOT NULL status */
char **notnull_constrs; /* NOT NULL constraint names. If null,
* there isn't one on this column. If
* empty string, unnamed constraint
--
1.8.3.1
[application/octet-stream] 0002-Support-NOT-VALID-and-VALIDATE-CONSTRAINT-for-named-.patch (20.7K, ../CAGPqQf0KitkNack4F5CFkFi-9Dqvp29Ro=EpcWt=4_hs-Rt+bQ@mail.gmail.com/4-0002-Support-NOT-VALID-and-VALIDATE-CONSTRAINT-for-named-.patch)
download | inline diff:
From aa8d3fbf55326aab14a05660e429f908472e15c4 Mon Sep 17 00:00:00 2001
From: Rushabh Lathia <[email protected]>
Date: Wed, 5 Feb 2025 15:36:43 +0530
Subject: [PATCH 2/3] Support NOT VALID and VALIDATE CONSTRAINT for named NOT
NULL constraints.
---
src/backend/commands/tablecmds.c | 119 +++++++++++++++++++++---------
src/backend/executor/execMain.c | 3 +-
src/backend/parser/gram.y | 4 +-
src/bin/psql/describe.c | 10 ++-
src/include/catalog/pg_attribute.h | 1 +
src/test/regress/expected/constraints.out | 85 +++++++++++++++++++++
src/test/regress/sql/constraints.sql | 40 ++++++++++
7 files changed, 220 insertions(+), 42 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 933530d..2efbacc 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -405,9 +405,9 @@ static ObjectAddress ATExecValidateConstraint(List **wqueue,
bool recurse, bool recursing, LOCKMODE lockmode);
static void QueueFKConstraintValidation(List **wqueue, Relation conrel, Relation rel,
HeapTuple contuple, LOCKMODE lockmode);
-static void QueueCheckConstraintValidation(List **wqueue, Relation conrel, Relation rel,
- char *constrName, HeapTuple contuple,
- bool recurse, bool recursing, LOCKMODE lockmode);
+static void QueueConstraintValidation(List **wqueue, Relation conrel, Relation rel,
+ char *constrName, HeapTuple contuple,
+ bool recurse, bool recursing, LOCKMODE lockmode);
static int transformColumnNameList(Oid relId, List *colList,
int16 *attnums, Oid *atttypids, Oid *attcollids);
static int transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
@@ -471,7 +471,7 @@ static void add_column_collation_dependency(Oid relid, int32 attnum, Oid collid)
static ObjectAddress ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
LOCKMODE lockmode);
static void set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
- LOCKMODE lockmode);
+ bool skip_validation, LOCKMODE lockmode);
static ObjectAddress ATExecSetNotNull(List **wqueue, Relation rel,
char *constrname, char *colName,
bool recurse, bool recursing,
@@ -700,6 +700,7 @@ static List *GetParentedForeignKeyRefs(Relation partition);
static void ATDetachCheckNoForeignKeyRefs(Relation partition);
static char GetAttributeCompression(Oid atttypid, const char *compression);
static char GetAttributeStorage(Oid atttypid, const char *storagemode);
+static bool check_for_invalid_notnull(Oid relid, const char *attname);
/* ----------------------------------------------------------------
@@ -1317,7 +1318,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
nncols = AddRelationNotNullConstraints(rel, stmt->nnconstraints,
old_notnulls);
foreach_int(attrnum, nncols)
- set_attnotnull(NULL, rel, attrnum, NoLock);
+ set_attnotnull(NULL, rel, attrnum, false, NoLock);
ObjectAddressSet(address, RelationRelationId, relationId);
@@ -6171,7 +6172,8 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
{
Form_pg_attribute attr = TupleDescAttr(newTupDesc, i);
- if (attr->attnotnull == ATTRIBUTE_NOTNULL_TRUE &&
+ if ((attr->attnotnull == ATTRIBUTE_NOTNULL_TRUE ||
+ attr->attnotnull == ATTRIBUTE_NOTNULL_INVALID) &&
!attr->attisdropped)
notnull_attrs = lappend_int(notnull_attrs, i);
}
@@ -7692,7 +7694,7 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
*/
static void
set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
- LOCKMODE lockmode)
+ bool skip_validation, LOCKMODE lockmode)
{
Form_pg_attribute attr;
@@ -7720,14 +7722,19 @@ set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
attr = (Form_pg_attribute) GETSTRUCT(tuple);
Assert(attr->attnotnull == ATTRIBUTE_NOTNULL_FALSE);
- attr->attnotnull = ATTRIBUTE_NOTNULL_TRUE;
+
+ if (skip_validation)
+ attr->attnotnull = ATTRIBUTE_NOTNULL_INVALID;
+ else
+ attr->attnotnull = ATTRIBUTE_NOTNULL_TRUE;
CatalogTupleUpdate(attr_rel, &tuple->t_self, tuple);
/*
* If the nullness isn't already proven by validated constraints, have
* ALTER TABLE phase 3 test for it.
*/
- if (wqueue && !NotNullImpliedByRelConstraints(rel, attr))
+ if (!skip_validation &&
+ wqueue && !NotNullImpliedByRelConstraints(rel, attr))
{
AlteredTableInfo *tab;
@@ -7888,7 +7895,7 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
RelationGetRelid(rel), attnum);
/* Mark pg_attribute.attnotnull for the column */
- set_attnotnull(wqueue, rel, attnum, lockmode);
+ set_attnotnull(wqueue, rel, attnum, constraint->skip_validation, lockmode);
/*
* Recurse to propagate the constraint to children that don't have one.
@@ -9272,6 +9279,12 @@ ATPrepAddPrimaryKey(List **wqueue, Relation rel, AlterTableCmd *cmd,
{
AlterTableCmd *newcmd;
Constraint *nnconstr;
+ String *colname = lfirst(lc);
+
+ if (check_for_invalid_notnull(RelationGetRelid(rel), colname->sval))
+ ereport(ERROR,
+ errmsg("column \"%s\" of table \"%s\" is marked as NOT VALID NOT NULL constrint",
+ colname->sval, RelationGetRelationName(rel)));
nnconstr = makeNotNullConstraint(lfirst(lc));
@@ -9285,6 +9298,30 @@ ATPrepAddPrimaryKey(List **wqueue, Relation rel, AlterTableCmd *cmd,
}
/*
+ * This function checks for any invalid NOT NULL constraint on the given
+ * relation and attribute name. It returns true if found, false otherwise.
+ */
+static bool
+check_for_invalid_notnull(Oid relid, const char *attname)
+{
+ HeapTuple tuple;
+ bool retval = false;
+
+ tuple = SearchSysCache2(ATTNAME,
+ ObjectIdGetDatum(relid),
+ CStringGetDatum(attname));
+ if (!HeapTupleIsValid(tuple))
+ return false;
+ if (!((Form_pg_attribute) GETSTRUCT(tuple))->attisdropped &&
+ ((Form_pg_attribute) GETSTRUCT(tuple))->attnotnull == ATTRIBUTE_NOTNULL_INVALID)
+ retval = true;
+
+ ReleaseSysCache(tuple);
+
+ return retval;
+}
+
+/*
* ALTER TABLE ADD INDEX
*
* There is no such command in the grammar, but parse_utilcmd.c converts
@@ -9651,7 +9688,7 @@ ATAddCheckNNConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
* phase 3 to verify existing rows, if needed.
*/
if (constr->contype == CONSTR_NOTNULL)
- set_attnotnull(wqueue, rel, ccon->attnum, lockmode);
+ set_attnotnull(wqueue, rel, ccon->attnum, ccon->skip_validation, lockmode);
ObjectAddressSet(address, ConstraintRelationId, ccon->conoid);
}
@@ -12079,7 +12116,8 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName,
con = (Form_pg_constraint) GETSTRUCT(tuple);
if (con->contype != CONSTRAINT_FOREIGN &&
- con->contype != CONSTRAINT_CHECK)
+ con->contype != CONSTRAINT_CHECK &&
+ con->contype != CONSTRAINT_NOTNULL)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key or check constraint",
@@ -12096,10 +12134,11 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName,
{
QueueFKConstraintValidation(wqueue, conrel, rel, tuple, lockmode);
}
- else if (con->contype == CONSTRAINT_CHECK)
+ else if (con->contype == CONSTRAINT_CHECK ||
+ con->contype == CONSTRAINT_NOTNULL)
{
- QueueCheckConstraintValidation(wqueue, conrel, rel, constrName,
- tuple, recurse, recursing, lockmode);
+ QueueConstraintValidation(wqueue, conrel, rel, constrName,
+ tuple, recurse, recursing, lockmode);
}
ObjectAddressSet(address, ConstraintRelationId, con->oid);
@@ -12217,14 +12256,14 @@ QueueFKConstraintValidation(List **wqueue, Relation conrel, Relation rel,
/*
* QueueCheckConstraintValidation
*
- * Add an entry to the wqueue to validate the given check constraint in Phase 3
- * and update the convalidated field in the pg_constraint catalog for the
- * specified relation and all its inheriting children.
+ * Add an entry to the wqueue to validate the given check or notnull constraint
+ * in Phase 3 and update the convalidated field in the pg_constraint catalog
+ * for the specified relation and all its inheriting children.
*/
static void
-QueueCheckConstraintValidation(List **wqueue, Relation conrel, Relation rel,
- char *constrName, HeapTuple contuple,
- bool recurse, bool recursing, LOCKMODE lockmode)
+QueueConstraintValidation(List **wqueue, Relation conrel, Relation rel,
+ char *constrName, HeapTuple contuple,
+ bool recurse, bool recursing, LOCKMODE lockmode)
{
Form_pg_constraint con;
AlteredTableInfo *tab;
@@ -12238,7 +12277,7 @@ QueueCheckConstraintValidation(List **wqueue, Relation conrel, Relation rel,
char *conbin;
con = (Form_pg_constraint) GETSTRUCT(contuple);
- Assert(con->contype == CONSTRAINT_CHECK);
+ Assert(con->contype == CONSTRAINT_CHECK || con->contype == CONSTRAINT_NOTNULL);
/*
* If we're recursing, the parent has already done this, so skip it. Also,
@@ -12283,21 +12322,31 @@ QueueCheckConstraintValidation(List **wqueue, Relation conrel, Relation rel,
}
/* Queue validation for phase 3 */
- newcon = (NewConstraint *) palloc0(sizeof(NewConstraint));
- newcon->name = constrName;
- newcon->contype = CONSTR_CHECK;
- newcon->refrelid = InvalidOid;
- newcon->refindid = InvalidOid;
- newcon->conid = con->oid;
+ if (con->contype == CONSTRAINT_CHECK)
+ {
+ newcon = (NewConstraint *) palloc0(sizeof(NewConstraint));
+ newcon->name = constrName;
+ newcon->contype = CONSTR_CHECK;
+ newcon->refrelid = InvalidOid;
+ newcon->refindid = InvalidOid;
+ newcon->conid = con->oid;
- val = SysCacheGetAttrNotNull(CONSTROID, contuple,
- Anum_pg_constraint_conbin);
- conbin = TextDatumGetCString(val);
- newcon->qual = (Node *) stringToNode(conbin);
+ val = SysCacheGetAttrNotNull(CONSTROID, contuple,
+ Anum_pg_constraint_conbin);
+ conbin = TextDatumGetCString(val);
+ newcon->qual = (Node *) stringToNode(conbin);
- /* Find or create work queue entry for this table */
- tab = ATGetQueueEntry(wqueue, rel);
- tab->constraints = lappend(tab->constraints, newcon);
+ /* Find or create work queue entry for this table */
+ tab = ATGetQueueEntry(wqueue, rel);
+ tab->constraints = lappend(tab->constraints, newcon);
+ }
+ else
+ {
+ Assert(con->contype == CONSTRAINT_NOTNULL);
+
+ tab = ATGetQueueEntry(wqueue, rel);
+ tab->verify_new_notnull = true;
+ }
/*
* Invalidate relcache so that others see the new validated constraint.
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index d4e09a1..ebd759e 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1959,7 +1959,8 @@ ExecConstraints(ResultRelInfo *resultRelInfo,
{
Form_pg_attribute att = TupleDescAttr(tupdesc, attrChk - 1);
- if (att->attnotnull == ATTRIBUTE_NOTNULL_TRUE &&
+ if ((att->attnotnull == ATTRIBUTE_NOTNULL_TRUE ||
+ att->attnotnull == ATTRIBUTE_NOTNULL_INVALID) &&
slot_attisnull(slot, attrChk))
{
char *val_desc;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index d7f9c00..a02e8ac 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4189,9 +4189,9 @@ ConstraintElem:
n->keys = list_make1(makeString($3));
/* no NOT VALID support yet */
processCASbits($4, @4, "NOT NULL",
- NULL, NULL, NULL, NULL,
+ NULL, NULL, NULL, &n->skip_validation,
&n->is_no_inherit, yyscanner);
- n->initially_valid = true;
+ n->initially_valid = !n->skip_validation;
$$ = (Node *) n;
}
| UNIQUE opt_unique_null_treatment '(' columnList opt_without_overlaps ')' opt_c_include opt_definition OptConsTableSpace
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index aa4363b..c5f7fdc 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2107,7 +2107,7 @@ describeOneTableDetails(const char *schemaname,
printTableAddCell(&cont, PQgetvalue(res, i, attcoll_col), false, false);
printTableAddCell(&cont,
- strcmp(PQgetvalue(res, i, attnotnull_col), "t") == 0 ? "not null" : "",
+ strcmp(PQgetvalue(res, i, attnotnull_col), "f") == 0 ? "" : "not null",
false, false);
identity = PQgetvalue(res, i, attidentity_col);
@@ -3108,7 +3108,7 @@ describeOneTableDetails(const char *schemaname,
{
printfPQExpBuffer(&buf,
"SELECT c.conname, a.attname, c.connoinherit,\n"
- " c.conislocal, c.coninhcount <> 0\n"
+ " c.conislocal, c.coninhcount <> 0, c.convalidated \n"
"FROM pg_catalog.pg_constraint c JOIN\n"
" pg_catalog.pg_attribute a ON\n"
" (a.attrelid = c.conrelid AND a.attnum = c.conkey[1])\n"
@@ -3132,13 +3132,15 @@ describeOneTableDetails(const char *schemaname,
bool islocal = PQgetvalue(result, i, 3)[0] == 't';
bool inherited = PQgetvalue(result, i, 4)[0] == 't';
- printfPQExpBuffer(&buf, " \"%s\" NOT NULL \"%s\"%s",
+ printfPQExpBuffer(&buf, " \"%s\" NOT NULL \"%s\"%s%s",
PQgetvalue(result, i, 0),
PQgetvalue(result, i, 1),
PQgetvalue(result, i, 2)[0] == 't' ?
" NO INHERIT" :
islocal && inherited ? _(" (local, inherited)") :
- inherited ? _(" (inherited)") : "");
+ inherited ? _(" (inherited)") : "",
+ PQgetvalue(result, i, 5)[0] == 'f' ?
+ " NOT VALID " : "");
printTableAddFooter(&cont, buf.data);
}
diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_attribute.h
index bc59a76..5d543af 100644
--- a/src/include/catalog/pg_attribute.h
+++ b/src/include/catalog/pg_attribute.h
@@ -228,6 +228,7 @@ MAKE_SYSCACHE(ATTNUM, pg_attribute_relid_attnum_index, 128);
#define ATTRIBUTE_NOTNULL_TRUE 't'
#define ATTRIBUTE_NOTNULL_FALSE 'f'
+#define ATTRIBUTE_NOTNULL_INVALID 'i'
#endif /* EXPOSE_TO_CLIENT_CODE */
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 692a69f..073e940 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -897,6 +897,91 @@ Not-null constraints:
"foobar" NOT NULL "a"
DROP TABLE notnull_tbl1;
+-- verify NOT NULL VALID/NOT VALID
+CREATE TABLE notnull_tbl1 (a INTEGER, b INTEGER);
+INSERT INTO notnull_tbl1 VALUES (NULL, 1);
+INSERT INTO notnull_tbl1 VALUES (NULL, 2);
+INSERT INTO notnull_tbl1 VALUES (300, 3);
+-- Below statement should throw an error
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a;
+ERROR: column "a" of relation "notnull_tbl1" contains null values
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID;
+\d+ notnull_tbl1
+ Table "public.notnull_tbl1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | not null | | plain | |
+ b | integer | | | | plain | |
+Not-null constraints:
+ "nn" NOT NULL "a" NOT VALID
+
+-- Try to insert new record with NULL, should throw an error
+INSERT INTO notnull_tbl1 VALUES (NULL, 4);
+ERROR: null value in column "a" of relation "notnull_tbl1" violates not-null constraint
+DETAIL: Failing row contains (null, 4).
+-- SELECT NULL values for COLUMN a, should return 2 records.
+SELECT * FROM notnull_tbl1 WHERE a is NULL;
+ a | b
+---+---
+ | 1
+ | 2
+(2 rows)
+
+-- UPDATE the one of the NULL values
+UPDATE notnull_tbl1 SET a = 100 WHERE b = 1;
+-- DELETE the record (NULL, 2)
+DELETE FROM notnull_tbl1 WHERE b = 2;
+SELECT * FROM notnull_tbl1;
+ a | b
+-----+---
+ 300 | 3
+ 100 | 1
+(2 rows)
+
+-- Try to add primary key on table column marked as NOT VALID NOT NULL
+-- constraint. This should throw an error.
+ALTER TABLE notnull_tbl1 add primary key (a);
+ERROR: column "a" of table "notnull_tbl1" is marked as NOT VALID NOT NULL constrint
+-- INHERITS table having NOT VALID NOT NULL constraints.
+CREATE TABLE notnull_tbl1_child(a INTEGER, b INTEGER) INHERITS(notnull_tbl1);
+NOTICE: merging column "a" with inherited definition
+NOTICE: merging column "b" with inherited definition
+-- Child table NOT NULL constraints should be valid.
+SELECT conname, convalidated FROM pg_catalog.pg_constraint WHERE conrelid = 'notnull_tbl1_child'::regclass;
+ conname | convalidated
+---------+--------------
+ nn | t
+(1 row)
+
+DROP TABLE notnull_tbl1_child;
+ALTER TABLE notnull_tbl1 validate constraint nn;
+\d+ notnull_tbl1
+ Table "public.notnull_tbl1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | not null | | plain | |
+ b | integer | | | | plain | |
+Not-null constraints:
+ "nn" NOT NULL "a"
+
+DROP TABLE notnull_tbl1;
+-- Verify NOT NULL VALID/NOT VALID with partition table.
+CREATE TABLE notnull_tbl1 (a INTEGER, b INTEGER) PARTITION BY RANGE (a);
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT notnull_con NOT NULL a NOT VALID;
+CREATE TABLE notnull_tbl1_1 PARTITION OF notnull_tbl1 FOR VALUES FROM (0) TO (10);
+CREATE TABLE notnull_tbl1_2 PARTITION OF notnull_tbl1 FOR VALUES FROM (20) TO (30);
+-- Parent table NOT NULL constraints will be market as validated false, where
+-- for child table it will be true
+SELECT conrelid::regclass, conname, convalidated FROM pg_catalog.pg_constraint WHERE
+conrelid IN ('notnull_tbl1'::regclass, 'notnull_tbl1_1'::regclass, 'notnull_tbl1_2'::regclass);
+ conrelid | conname | convalidated
+----------------+-------------+--------------
+ notnull_tbl1 | notnull_con | f
+ notnull_tbl1_1 | notnull_con | t
+ notnull_tbl1_2 | notnull_con | t
+(3 rows)
+
+DROP TABLE notnull_tbl1, notnull_tbl1_1, notnull_tbl1_2;
-- Verify that constraint names and NO INHERIT are properly considered when
-- multiple constraint are specified, either explicitly or via SERIAL/PK/etc,
-- and that conflicting cases are rejected. Mind that table constraints
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index d6742f8..85cc13a 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -641,6 +641,46 @@ ALTER TABLE notnull_tbl1 ADD CONSTRAINT foobar NOT NULL a;
\d+ notnull_tbl1
DROP TABLE notnull_tbl1;
+-- verify NOT NULL VALID/NOT VALID
+CREATE TABLE notnull_tbl1 (a INTEGER, b INTEGER);
+INSERT INTO notnull_tbl1 VALUES (NULL, 1);
+INSERT INTO notnull_tbl1 VALUES (NULL, 2);
+INSERT INTO notnull_tbl1 VALUES (300, 3);
+-- Below statement should throw an error
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a;
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID;
+\d+ notnull_tbl1
+-- Try to insert new record with NULL, should throw an error
+INSERT INTO notnull_tbl1 VALUES (NULL, 4);
+-- SELECT NULL values for COLUMN a, should return 2 records.
+SELECT * FROM notnull_tbl1 WHERE a is NULL;
+-- UPDATE the one of the NULL values
+UPDATE notnull_tbl1 SET a = 100 WHERE b = 1;
+-- DELETE the record (NULL, 2)
+DELETE FROM notnull_tbl1 WHERE b = 2;
+SELECT * FROM notnull_tbl1;
+-- Try to add primary key on table column marked as NOT VALID NOT NULL
+-- constraint. This should throw an error.
+ALTER TABLE notnull_tbl1 add primary key (a);
+-- INHERITS table having NOT VALID NOT NULL constraints.
+CREATE TABLE notnull_tbl1_child(a INTEGER, b INTEGER) INHERITS(notnull_tbl1);
+-- Child table NOT NULL constraints should be valid.
+SELECT conname, convalidated FROM pg_catalog.pg_constraint WHERE conrelid = 'notnull_tbl1_child'::regclass;
+DROP TABLE notnull_tbl1_child;
+ALTER TABLE notnull_tbl1 validate constraint nn;
+\d+ notnull_tbl1
+DROP TABLE notnull_tbl1;
+-- Verify NOT NULL VALID/NOT VALID with partition table.
+CREATE TABLE notnull_tbl1 (a INTEGER, b INTEGER) PARTITION BY RANGE (a);
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT notnull_con NOT NULL a NOT VALID;
+CREATE TABLE notnull_tbl1_1 PARTITION OF notnull_tbl1 FOR VALUES FROM (0) TO (10);
+CREATE TABLE notnull_tbl1_2 PARTITION OF notnull_tbl1 FOR VALUES FROM (20) TO (30);
+-- Parent table NOT NULL constraints will be market as validated false, where
+-- for child table it will be true
+SELECT conrelid::regclass, conname, convalidated FROM pg_catalog.pg_constraint WHERE
+conrelid IN ('notnull_tbl1'::regclass, 'notnull_tbl1_1'::regclass, 'notnull_tbl1_2'::regclass);
+DROP TABLE notnull_tbl1, notnull_tbl1_1, notnull_tbl1_2;
+
-- Verify that constraint names and NO INHERIT are properly considered when
-- multiple constraint are specified, either explicitly or via SERIAL/PK/etc,
-- and that conflicting cases are rejected. Mind that table constraints
--
1.8.3.1
[application/octet-stream] 0001-Convert-pg_attribut.attnotnull-to-char-type.patch (19.5K, ../CAGPqQf0KitkNack4F5CFkFi-9Dqvp29Ro=EpcWt=4_hs-Rt+bQ@mail.gmail.com/5-0001-Convert-pg_attribut.attnotnull-to-char-type.patch)
download | inline diff:
From 5add88722ce42b2b4c1a983b9bc2be1b4729b76e Mon Sep 17 00:00:00 2001
From: Rushabh Lathia <[email protected]>
Date: Wed, 5 Feb 2025 15:35:03 +0530
Subject: [PATCH 1/3] Convert pg_attribut.attnotnull to char type.
This commit change the pg_attribut.attnotnull to char type. Now
attnotnull holds three values, where attnotnull can be either
TRUE, FALSE and INVALID. INVALID is when name not null constrint
is NOT VALIDATED.
---
src/backend/access/common/tupdesc.c | 12 +++++-----
src/backend/bootstrap/bootstrap.c | 13 ++++++-----
src/backend/catalog/heap.c | 16 +++++++-------
src/backend/catalog/index.c | 2 +-
src/backend/catalog/indexing.c | 3 ++-
src/backend/catalog/information_schema.sql | 4 ++--
src/backend/commands/tablecmds.c | 35 +++++++++++++++++-------------
src/backend/executor/execMain.c | 3 ++-
src/backend/jit/llvm/llvmjit_deform.c | 10 +++++----
src/backend/optimizer/util/plancat.c | 5 +++--
src/include/access/tupdesc.h | 2 +-
src/include/catalog/pg_attribute.h | 5 ++++-
12 files changed, 63 insertions(+), 47 deletions(-)
diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c
index fe19744..728c1cc 100644
--- a/src/backend/access/common/tupdesc.c
+++ b/src/backend/access/common/tupdesc.c
@@ -251,7 +251,7 @@ CreateTupleDescCopy(TupleDesc tupdesc)
{
Form_pg_attribute att = TupleDescAttr(desc, i);
- att->attnotnull = false;
+ att->attnotnull = ATTRIBUTE_NOTNULL_FALSE;
att->atthasdef = false;
att->atthasmissing = false;
att->attidentity = '\0';
@@ -297,7 +297,7 @@ CreateTupleDescTruncatedCopy(TupleDesc tupdesc, int natts)
{
Form_pg_attribute att = TupleDescAttr(desc, i);
- att->attnotnull = false;
+ att->attnotnull = ATTRIBUTE_NOTNULL_FALSE;
att->atthasdef = false;
att->atthasmissing = false;
att->attidentity = '\0';
@@ -416,7 +416,7 @@ TupleDescCopy(TupleDesc dst, TupleDesc src)
{
Form_pg_attribute att = TupleDescAttr(dst, i);
- att->attnotnull = false;
+ att->attnotnull = ATTRIBUTE_NOTNULL_FALSE;
att->atthasdef = false;
att->atthasmissing = false;
att->attidentity = '\0';
@@ -462,7 +462,7 @@ TupleDescCopyEntry(TupleDesc dst, AttrNumber dstAttno,
dstAtt->attnum = dstAttno;
/* since we're not copying constraints or defaults, clear these */
- dstAtt->attnotnull = false;
+ dstAtt->attnotnull = ATTRIBUTE_NOTNULL_FALSE;
dstAtt->atthasdef = false;
dstAtt->atthasmissing = false;
dstAtt->attidentity = '\0';
@@ -837,7 +837,7 @@ TupleDescInitEntry(TupleDesc desc,
att->attnum = attributeNumber;
att->attndims = attdim;
- att->attnotnull = false;
+ att->attnotnull = ATTRIBUTE_NOTNULL_FALSE;
att->atthasdef = false;
att->atthasmissing = false;
att->attidentity = '\0';
@@ -900,7 +900,7 @@ TupleDescInitBuiltinEntry(TupleDesc desc,
att->attnum = attributeNumber;
att->attndims = attdim;
- att->attnotnull = false;
+ att->attnotnull = ATTRIBUTE_NOTNULL_FALSE;
att->atthasdef = false;
att->atthasmissing = false;
att->attidentity = '\0';
diff --git a/src/backend/bootstrap/bootstrap.c b/src/backend/bootstrap/bootstrap.c
index 359f58a..8b952d8 100644
--- a/src/backend/bootstrap/bootstrap.c
+++ b/src/backend/bootstrap/bootstrap.c
@@ -582,14 +582,17 @@ DefineAttr(char *name, char *type, int attnum, int nullness)
attrtypes[attnum]->atttypmod = -1;
attrtypes[attnum]->attislocal = true;
+ /* set default to false */
+ attrtypes[attnum]->attnotnull = ATTRIBUTE_NOTNULL_FALSE;
+
if (nullness == BOOTCOL_NULL_FORCE_NOT_NULL)
{
- attrtypes[attnum]->attnotnull = true;
+ attrtypes[attnum]->attnotnull = ATTRIBUTE_NOTNULL_TRUE;
}
else if (nullness == BOOTCOL_NULL_FORCE_NULL)
{
- attrtypes[attnum]->attnotnull = false;
+ attrtypes[attnum]->attnotnull = ATTRIBUTE_NOTNULL_FALSE;
}
else
{
@@ -608,11 +611,11 @@ DefineAttr(char *name, char *type, int attnum, int nullness)
for (i = 0; i < attnum; i++)
{
if (attrtypes[i]->attlen <= 0 ||
- !attrtypes[i]->attnotnull)
+ attrtypes[i]->attnotnull == ATTRIBUTE_NOTNULL_FALSE)
break;
}
if (i == attnum)
- attrtypes[attnum]->attnotnull = true;
+ attrtypes[attnum]->attnotnull = ATTRIBUTE_NOTNULL_TRUE;
}
}
}
@@ -696,7 +699,7 @@ InsertOneNull(int i)
{
elog(DEBUG4, "inserting column %d NULL", i);
Assert(i >= 0 && i < MAXATTR);
- if (TupleDescAttr(boot_reldesc->rd_att, i)->attnotnull)
+ if (TupleDescAttr(boot_reldesc->rd_att, i)->attnotnull == ATTRIBUTE_NOTNULL_TRUE)
elog(ERROR,
"NULL value specified for not-null column \"%s\" of relation \"%s\"",
NameStr(TupleDescAttr(boot_reldesc->rd_att, i)->attname),
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 57ef466..e6093da 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -149,7 +149,7 @@ static const FormData_pg_attribute a1 = {
.attbyval = false,
.attalign = TYPALIGN_SHORT,
.attstorage = TYPSTORAGE_PLAIN,
- .attnotnull = true,
+ .attnotnull = ATTRIBUTE_NOTNULL_TRUE,
.attislocal = true,
};
@@ -162,7 +162,7 @@ static const FormData_pg_attribute a2 = {
.attbyval = true,
.attalign = TYPALIGN_INT,
.attstorage = TYPSTORAGE_PLAIN,
- .attnotnull = true,
+ .attnotnull = ATTRIBUTE_NOTNULL_TRUE,
.attislocal = true,
};
@@ -175,7 +175,7 @@ static const FormData_pg_attribute a3 = {
.attbyval = true,
.attalign = TYPALIGN_INT,
.attstorage = TYPSTORAGE_PLAIN,
- .attnotnull = true,
+ .attnotnull = ATTRIBUTE_NOTNULL_TRUE,
.attislocal = true,
};
@@ -188,7 +188,7 @@ static const FormData_pg_attribute a4 = {
.attbyval = true,
.attalign = TYPALIGN_INT,
.attstorage = TYPSTORAGE_PLAIN,
- .attnotnull = true,
+ .attnotnull = ATTRIBUTE_NOTNULL_TRUE,
.attislocal = true,
};
@@ -201,7 +201,7 @@ static const FormData_pg_attribute a5 = {
.attbyval = true,
.attalign = TYPALIGN_INT,
.attstorage = TYPSTORAGE_PLAIN,
- .attnotnull = true,
+ .attnotnull = ATTRIBUTE_NOTNULL_TRUE,
.attislocal = true,
};
@@ -220,7 +220,7 @@ static const FormData_pg_attribute a6 = {
.attbyval = true,
.attalign = TYPALIGN_INT,
.attstorage = TYPSTORAGE_PLAIN,
- .attnotnull = true,
+ .attnotnull = ATTRIBUTE_NOTNULL_TRUE,
.attislocal = true,
};
@@ -740,7 +740,7 @@ InsertPgAttributeTuples(Relation pg_attribute_rel,
slot[slotCount]->tts_values[Anum_pg_attribute_attalign - 1] = CharGetDatum(attrs->attalign);
slot[slotCount]->tts_values[Anum_pg_attribute_attstorage - 1] = CharGetDatum(attrs->attstorage);
slot[slotCount]->tts_values[Anum_pg_attribute_attcompression - 1] = CharGetDatum(attrs->attcompression);
- slot[slotCount]->tts_values[Anum_pg_attribute_attnotnull - 1] = BoolGetDatum(attrs->attnotnull);
+ slot[slotCount]->tts_values[Anum_pg_attribute_attnotnull - 1] = CharGetDatum(attrs->attnotnull);
slot[slotCount]->tts_values[Anum_pg_attribute_atthasdef - 1] = BoolGetDatum(attrs->atthasdef);
slot[slotCount]->tts_values[Anum_pg_attribute_atthasmissing - 1] = BoolGetDatum(attrs->atthasmissing);
slot[slotCount]->tts_values[Anum_pg_attribute_attidentity - 1] = CharGetDatum(attrs->attidentity);
@@ -1699,7 +1699,7 @@ RemoveAttributeById(Oid relid, AttrNumber attnum)
attStruct->atttypid = InvalidOid;
/* Remove any not-null constraint the column may have */
- attStruct->attnotnull = false;
+ attStruct->attnotnull = ATTRIBUTE_NOTNULL_FALSE;
/* Unset this so no one tries to look up the generation expression */
attStruct->attgenerated = '\0';
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 7377912..b202a61 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -261,7 +261,7 @@ index_check_primary_key(Relation heapRel,
attnum, RelationGetRelid(heapRel));
attform = (Form_pg_attribute) GETSTRUCT(atttuple);
- if (!attform->attnotnull)
+ if (attform->attnotnull == ATTRIBUTE_NOTNULL_FALSE)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
errmsg("primary key column \"%s\" is not marked NOT NULL",
diff --git a/src/backend/catalog/indexing.c b/src/backend/catalog/indexing.c
index 25c4b6b..a2211d7 100644
--- a/src/backend/catalog/indexing.c
+++ b/src/backend/catalog/indexing.c
@@ -207,7 +207,8 @@ CatalogTupleCheckConstraints(Relation heapRel, HeapTuple tup)
{
Form_pg_attribute thisatt = TupleDescAttr(tupdesc, attnum);
- Assert(!(thisatt->attnotnull && att_isnull(attnum, bp)));
+ Assert(!(thisatt->attnotnull == ATTRIBUTE_NOTNULL_TRUE &&
+ att_isnull(attnum, bp)));
}
}
}
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index a7bffca..4f59bc4 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -292,7 +292,7 @@ CREATE VIEW attributes AS
CAST(a.attname AS sql_identifier) AS attribute_name,
CAST(a.attnum AS cardinal_number) AS ordinal_position,
CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS attribute_default,
- CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
+ CAST(CASE WHEN a.attnotnull = 't' OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
AS yes_or_no)
AS is_nullable, -- This column was apparently removed between SQL:2003 and SQL:2008.
@@ -671,7 +671,7 @@ CREATE VIEW columns AS
CAST(a.attname AS sql_identifier) AS column_name,
CAST(a.attnum AS cardinal_number) AS ordinal_position,
CAST(CASE WHEN a.attgenerated = '' THEN pg_get_expr(ad.adbin, ad.adrelid) END AS character_data) AS column_default,
- CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
+ CAST(CASE WHEN a.attnotnull = 't' OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
AS yes_or_no)
AS is_nullable,
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 18f64db..933530d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -1401,7 +1401,10 @@ BuildDescForRelation(const List *columns)
TupleDescInitEntryCollation(desc, attnum, attcollation);
/* Fill in additional stuff not handled by TupleDescInitEntry */
- att->attnotnull = entry->is_not_null;
+ if (entry->is_not_null)
+ att->attnotnull = ATTRIBUTE_NOTNULL_TRUE;
+ else
+ att->attnotnull = ATTRIBUTE_NOTNULL_FALSE;
att->attislocal = entry->is_local;
att->attinhcount = entry->inhcount;
att->attidentity = entry->identity;
@@ -6168,7 +6171,8 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
{
Form_pg_attribute attr = TupleDescAttr(newTupDesc, i);
- if (attr->attnotnull && !attr->attisdropped)
+ if (attr->attnotnull == ATTRIBUTE_NOTNULL_TRUE &&
+ !attr->attisdropped)
notnull_attrs = lappend_int(notnull_attrs, i);
}
if (notnull_attrs)
@@ -7618,7 +7622,7 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
RelationGetRelid(rel), attnum);
/* If the column is already nullable there's nothing to do. */
- if (!attTup->attnotnull)
+ if (attTup->attnotnull == ATTRIBUTE_NOTNULL_FALSE)
{
table_close(attr_rel, RowExclusiveLock);
return InvalidObjectAddress;
@@ -7648,7 +7652,7 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
AttrNumber parent_attnum;
parent_attnum = get_attnum(parentId, colName);
- if (TupleDescAttr(tupDesc, parent_attnum - 1)->attnotnull)
+ if (TupleDescAttr(tupDesc, parent_attnum - 1)->attnotnull == ATTRIBUTE_NOTNULL_TRUE)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
errmsg("column \"%s\" is marked NOT NULL in parent table",
@@ -7702,7 +7706,7 @@ set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
if (attr->attisdropped)
return;
- if (!attr->attnotnull)
+ if (attr->attnotnull == ATTRIBUTE_NOTNULL_FALSE)
{
Relation attr_rel;
HeapTuple tuple;
@@ -7715,8 +7719,8 @@ set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
attnum, RelationGetRelid(rel));
attr = (Form_pg_attribute) GETSTRUCT(tuple);
- Assert(!attr->attnotnull);
- attr->attnotnull = true;
+ Assert(attr->attnotnull == ATTRIBUTE_NOTNULL_FALSE);
+ attr->attnotnull = ATTRIBUTE_NOTNULL_TRUE;
CatalogTupleUpdate(attr_rel, &tuple->t_self, tuple);
/*
@@ -8114,7 +8118,7 @@ ATExecAddIdentity(Relation rel, const char *colName,
* to an existing column that is not NOT NULL would create a state that
* cannot be reproduced without contortions.
*/
- if (!attTup->attnotnull)
+ if (attTup->attnotnull == ATTRIBUTE_NOTNULL_FALSE)
ereport(ERROR,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("column \"%s\" of relation \"%s\" must be declared NOT NULL before identity can be added",
@@ -9254,7 +9258,7 @@ ATPrepAddPrimaryKey(List **wqueue, Relation rel, AlterTableCmd *cmd,
elog(ERROR, "cache lookup failed for attribute %s of relation %u",
attname, childrelid);
attrForm = (Form_pg_attribute) GETSTRUCT(tup);
- if (!attrForm->attnotnull)
+ if (attrForm->attnotnull == ATTRIBUTE_NOTNULL_FALSE)
ereport(ERROR,
errmsg("column \"%s\" of table \"%s\" is not marked NOT NULL",
attname, get_rel_name(childrelid)));
@@ -13184,9 +13188,9 @@ dropconstraint_internal(Relation rel, HeapTuple constraintTup, DropBehavior beha
RelationGetRelationName(rel)));
/* All good -- reset attnotnull if needed */
- if (attForm->attnotnull)
+ if (attForm->attnotnull == ATTRIBUTE_NOTNULL_TRUE)
{
- attForm->attnotnull = false;
+ attForm->attnotnull = ATTRIBUTE_NOTNULL_FALSE;
CatalogTupleUpdate(attrel, &atttup->t_self, atttup);
}
@@ -16508,7 +16512,8 @@ MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel, bool ispart
*
* Other constraints are checked elsewhere.
*/
- if (parent_att->attnotnull && !child_att->attnotnull)
+ if (parent_att->attnotnull == ATTRIBUTE_NOTNULL_TRUE &&
+ child_att->attnotnull == ATTRIBUTE_NOTNULL_FALSE)
{
HeapTuple contup;
@@ -17543,7 +17548,7 @@ ATExecReplicaIdentity(Relation rel, ReplicaIdentityStmt *stmt, LOCKMODE lockmode
RelationGetRelationName(indexRel), attno)));
attr = TupleDescAttr(rel->rd_att, attno - 1);
- if (!attr->attnotnull)
+ if (attr->attnotnull == ATTRIBUTE_NOTNULL_FALSE)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("index \"%s\" cannot be used as replica identity because column \"%s\" is nullable",
@@ -19015,7 +19020,7 @@ PartConstraintImpliedByRelConstraint(Relation scanrel,
{
Form_pg_attribute att = TupleDescAttr(scanrel->rd_att, i - 1);
- if (att->attnotnull && !att->attisdropped)
+ if (att->attnotnull == ATTRIBUTE_NOTNULL_TRUE && !att->attisdropped)
{
NullTest *ntest = makeNode(NullTest);
@@ -20847,7 +20852,7 @@ verifyPartitionIndexNotNull(IndexInfo *iinfo, Relation partition)
Form_pg_attribute att = TupleDescAttr(RelationGetDescr(partition),
iinfo->ii_IndexAttrNumbers[i] - 1);
- if (!att->attnotnull)
+ if (att->attnotnull == ATTRIBUTE_NOTNULL_FALSE)
ereport(ERROR,
errcode(ERRCODE_INVALID_TABLE_DEFINITION),
errmsg("invalid primary key definition"),
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 604cb06..d4e09a1 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1959,7 +1959,8 @@ ExecConstraints(ResultRelInfo *resultRelInfo,
{
Form_pg_attribute att = TupleDescAttr(tupdesc, attrChk - 1);
- if (att->attnotnull && slot_attisnull(slot, attrChk))
+ if (att->attnotnull == ATTRIBUTE_NOTNULL_TRUE &&
+ slot_attisnull(slot, attrChk))
{
char *val_desc;
Relation orig_rel = rel;
diff --git a/src/backend/jit/llvm/llvmjit_deform.c b/src/backend/jit/llvm/llvmjit_deform.c
index 5d169c7..e6444bc 100644
--- a/src/backend/jit/llvm/llvmjit_deform.c
+++ b/src/backend/jit/llvm/llvmjit_deform.c
@@ -123,7 +123,7 @@ slot_compile_deform(LLVMJitContext *context, TupleDesc desc,
* combination of attisdropped && attnotnull combination shouldn't
* exist.
*/
- if (att->attnotnull &&
+ if (att->attnotnull == ATTRIBUTE_NOTNULL_TRUE &&
!att->atthasmissing &&
!att->attisdropped)
guaranteed_column_number = attnum;
@@ -438,7 +438,7 @@ slot_compile_deform(LLVMJitContext *context, TupleDesc desc,
* into account, because if they're present the heaptuple's natts
* would have indicated that a slot_getmissingattrs() is needed.
*/
- if (!att->attnotnull)
+ if (att->attnotnull == ATTRIBUTE_NOTNULL_FALSE)
{
LLVMBasicBlockRef b_ifnotnull;
LLVMBasicBlockRef b_ifnull;
@@ -604,7 +604,8 @@ slot_compile_deform(LLVMJitContext *context, TupleDesc desc,
known_alignment = -1;
attguaranteedalign = false;
}
- else if (att->attnotnull && attguaranteedalign && known_alignment >= 0)
+ else if (att->attnotnull == ATTRIBUTE_NOTNULL_TRUE &&
+ attguaranteedalign && known_alignment >= 0)
{
/*
* If the offset to the column was previously known, a NOT NULL &
@@ -614,7 +615,8 @@ slot_compile_deform(LLVMJitContext *context, TupleDesc desc,
Assert(att->attlen > 0);
known_alignment += att->attlen;
}
- else if (att->attnotnull && (att->attlen % alignto) == 0)
+ else if (att->attnotnull == ATTRIBUTE_NOTNULL_TRUE &&
+ (att->attlen % alignto) == 0)
{
/*
* After a NOT NULL fixed-width column with a length that is a
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 71abb01..e322098 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -177,7 +177,7 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
{
CompactAttribute *attr = TupleDescCompactAttr(relation->rd_att, i);
- if (attr->attnotnull)
+ if (attr->attnotnull == ATTRIBUTE_NOTNULL_TRUE)
{
rel->notnullattnums = bms_add_member(rel->notnullattnums,
i + 1);
@@ -1355,7 +1355,8 @@ get_relation_constraints(PlannerInfo *root,
{
Form_pg_attribute att = TupleDescAttr(relation->rd_att, i - 1);
- if (att->attnotnull && !att->attisdropped)
+ if (att->attnotnull == ATTRIBUTE_NOTNULL_TRUE &&
+ !att->attisdropped)
{
NullTest *ntest = makeNode(NullTest);
diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h
index ff27df9..a7238a1 100644
--- a/src/include/access/tupdesc.h
+++ b/src/include/access/tupdesc.h
@@ -75,7 +75,7 @@ typedef struct CompactAttribute
bool atthasmissing; /* as FormData_pg_attribute.atthasmissing */
bool attisdropped; /* as FormData_pg_attribute.attisdropped */
bool attgenerated; /* FormData_pg_attribute.attgenerated != '\0' */
- bool attnotnull; /* as FormData_pg_attribute.attnotnull */
+ char attnotnull; /* as FormData_pg_attribute.attnotnull */
uint8 attalignby; /* alignment requirement in bytes */
} CompactAttribute;
diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_attribute.h
index b33c315..bc59a76 100644
--- a/src/include/catalog/pg_attribute.h
+++ b/src/include/catalog/pg_attribute.h
@@ -118,7 +118,7 @@ CATALOG(pg_attribute,1249,AttributeRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(75,
char attcompression BKI_DEFAULT('\0');
/* This flag represents the "NOT NULL" constraint */
- bool attnotnull;
+ char attnotnull;
/* Has DEFAULT value or not */
bool atthasdef BKI_DEFAULT(f);
@@ -226,6 +226,9 @@ MAKE_SYSCACHE(ATTNUM, pg_attribute_relid_attnum_index, 128);
#define ATTRIBUTE_GENERATED_STORED 's'
+#define ATTRIBUTE_NOTNULL_TRUE 't'
+#define ATTRIBUTE_NOTNULL_FALSE 'f'
+
#endif /* EXPOSE_TO_CLIENT_CODE */
#endif /* PG_ATTRIBUTE_H */
--
1.8.3.1
view thread (8+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints
In-Reply-To: <CAGPqQf0KitkNack4F5CFkFi-9Dqvp29Ro=EpcWt=4_hs-Rt+bQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox