After a very long development period, we finally completed the project to rework NOT NULL constraints in PostgreSQL. This has long been a desire of the Postgres development community, and we finally pulled it off for version 18, which has made me very happy.
What changed?
The most visible user change is that NOT NULL constraints now have names. These names are visible in psql under the \d+ command. For instance, looking at the aircraft table in the postgres_air database, we can see something like this:
=# \d+ aircraft
Table "postgres_air.aircraft"
Column │ Type │ Collation │ Nullable │ Default │ Storage │
──────────┼─────────┼───────────┼──────────┼─────────┼──────────┼─
model │ text │ │ │ │ extended │
range │ numeric │ │ not null │ │ main │
class │ integer │ │ not null │ │ plain │
velocity │ numeric │ │ not null │ │ main │
code │ text │ │ not null │ │ extended │
Indexes:
"aircraft_pkey" PRIMARY KEY, btree (code)
Referenced by:
TABLE "flight" CONSTRAINT "aircraft_code_fk" FOREIGN KEY (airc...
Not-null constraints:
"aircraft_range_not_null" NOT NULL "range"
"aircraft_class_not_null" NOT NULL "class"
"aircraft_velocity_not_null" NOT NULL "velocity"
"aircraft_code_not_null" NOT NULL "code"
Access method: heapNote the new not-null constraints section at the bottom.
“So what?,” I hear you say. “I can easily use ALTER TABLE aircraft ALTER COLUMN range DROP NOT NULL if I want to drop that constraint, right? And then I don't need the constraint name at all.” And you would be correct. However, what if you wanted to do other things to that constraint? For instance, foreign keys have the ability to be created as NOT VALID, and validated later; this is incredibly useful if your database is in continuous operation, because such a constraint addition can be made with very little disruption to your production load, because no lengthy scan of the data needs to be made. You couldn't previously do that with not-null constraints. (Actually, you can create an invalid CHECK constraint, validate it, then add a not-null constraint (which doesn't require a slow scan because of the CHECK constraint), then drop the CHECK constraint. But who wants to go to all that trouble!?)
But what's the connection between the constraint name and the NOT VALID indicator of the constraint? The reason to mention those things together is that for all constraints we store that validity flag in the same place where we store other properties of constraints, including their names: a row in the pg_constraint system catalog. For implementation expediency reasons, Postgres historically only stored the NOT NULL bit of a column in the pg_attribute.attnotnull column, in the catalog where columns (attributes) are stored. There's no room for any extra information there. For more elaborate constraint properties, what we did for Postgres 18 was to add a pg_constraint row for each not-null constraint, which is what we use for other types of constraints, and migrate the ultimate source of truth there.
This sounds easy, but it was actually not. There are two sources of complications: one is making sure pg_upgrade did the right thing for existing databases, and there are tons of possible variations used in the wild, especially when tables are connected to other tables, as is the case with partitioning and with old-style inheritance. In the former case, the partitioning code introduced in release 10 had made sure that all partitions must have the not-null constraints that their parent table has, so that's easy to handle because all cases are essentially identical. Inheritance is more complicated, and we had to add a check mode to pg_upgrade that would throw an error if you had a parent table with a not-null constraint in one column and then a child table was missing that constraint. This is actually a case that had been largely considered a bug, but could not be fixed precisely until this feature was done.
The other implementation difficulty is making sure that during execution we don't make things slower than before, while not causing confusion for third-party software that might be confused by the attnotnull column disappearing. In-core code can be easily modified to no longer look at attnotnull anymore ... but would users love us if we made (say) an older pgAdmin break when connecting to Postgres 18? By golly they would not! And there's a ton of software that examines pg_attribute, so we needed to keep it working one way or another.
NOT VALID
Now it's a good time to introduce one of the other features we added in this area: the ability to add a NOT NULL NOT VALID constraint, as mentioned earlier. This part of the code was contributed by Rushabh Lathia, a long-time maintainer of EDB's EPAS fork of Postgres. For third-party applications, the usage of pg_attribute.attnotnull seems to be mostly about displaying whether a NULL value can be inserted into the column or not. So if the user adds a NOT VALID constraint, we must still mark that column as not-nullable for them -- even though it is possible that there are null values in the existing data! This means that the optimizer has a different opinion on the nullability of the column. This is critically important, because failing to properly account for this could lead to query plans that give wrong results.
Here's an example of NOT VALID constraints.
=# CREATE TABLE person (id bigserial PRIMARY KEY, lastname text, firstname text);
CREATE TABLE
-- This is the easiest way to add a not-valid constraint.
=# ALTER TABLE person ADD NOT NULL lastname NOT VALID;
ALTER TABLE
-- As written above, you can give these names of your choice.
=# ALTER TABLE person ADD CONSTRAINT "AnotherOne" NOT NULL firstname NOT VALID;
ALTER TABLE
-- And use those names to validate each constraint. This scans the table, but
-- it doesn't block other DML (I/U/D) on the table.
=# ALTER TABLE person VALIDATE CONSTRAINT "AnotherOne";
ALTER TABLE
-- Finally the table looks more or less like this.
=# \d+ person
Table "postgres_air.person"
Column │ Type │ Collation │ Nullable │ Default │
───────────┼────────┼───────────┼──────────┼────────────────────────────────────┼─
id │ bigint │ │ not null │ nextval('person_id_seq'::regclass) │
lastname │ text │ │ not null │ │
firstname │ text │ │ not null │ │
Indexes:
"person_pkey" PRIMARY KEY, btree (id)
Not-null constraints:
"person_id_not_null" NOT NULL "id"
"person_lastname_not_null" NOT NULL "lastname" NOT VALID
"AnotherOne" NOT NULL "firstname"
Access method: heapLuckily, we could rely on David Rowley's commit "Introduce CompactAttribute array in TupleDesc, take 2", which changed the way the optimizer deals with the so-called tuple descriptors. Previous to that work, a tuple descriptor was simply a copy of all the pg_attribute rows; he introduced use of the CompactAttribute struct, which made it faster for the executor to extract facts about stored tuples. It was an extremely happy coincidence that we could use CompactAttribute to distinguish those two cases (no constraint or an invalid one) from the case with no constraint at all while still allowing third-party apps depend on attnotnull simply to know whether a constraint exists or not. In addition, this made it easy for us to efficiently scan pg_constraint just once and collect the correct nullability info.
Note that if you say NOT VALID to a constraint during CREATE TABLE, the constraint spawns as a validated one. The reason is that because we know the table to be empty, there cannot be any rows that violate the constraint, therefore it makes no sense to mark the constraint as not valid. The only way to have a NOT VALID constraint is via ALTER TABLE.
INHERIT and NO INHERIT
A completely different aspect is whether a constraint must be propagated to a child table or not. Most constraints do, because by default they are all inheritable constraints. When the user decides to give the NO INHERIT flag to one particular constraint, they are saying that children tables may or may not have that constraint. This fact is stored in pg_constraint.connoinherit. This works as far as that goes, but what if the user changes their mind later? This is particularly important if you later decide to add a primary key to the table: if you had added a NO INHERIT constraint, then adding a primary key will fail because it wants an inheritable constraint instead. And the only way to change that would be to drop the constraint and add another one! Luckily, my colleague Suraj Karage spent some time adding the command ALTER TABLE ... ALTER CONSTRAINT ... INHERIT, which takes a NO INHERIT constraint and changes it to inheritable, and if there are children tables, it adds constraints there so that everything is consistent. (We added it as SET INHERIT first, and removed the SET keyword from there later, thanks to unrelated grammar hacking facilitated by Amul Sul.)
Here's an example of NO INHERIT constraints.
-- In our vegetables table, we add a not-null constraint that doesn't apply
-- to child tables.
=# create table vegetables (veggie text,
kcals_per_kg int constraint shortnm not null no inherit);
CREATE TABLE
-- ... so this table doesn't have it.
=# create table nightshades () inherits (vegetables);
CREATE TABLE
-- But this one does, because we declare it separately. Note we don't
-- declare the column itself, just the constraint on it (and we can choose
-- a different name if we so desire.)
CREATE TABLE tubers (type text,
CONSTRAINT yep NOT NULL kcals_per_kg) inherits (vegetables);
CREATE TABLE
-- Now we change our minds and set the constraint as inheritable in the
-- parent table:
=# ALTER TABLE vegetables ALTER CONSTRAINT shortnm INHERIT;
ALTER TABLE
-- Let's look at the child tables definitions. Here the constraint has the
-- name we gave it, and it's marked as locally defined as well as coming from
-- inheritance.
=# \d+ tubers
Table "postgres_air.tubers"
Column │ Type │ Collation │ Nullable │ Default │
──────────────┼─────────┼───────────┼──────────┼─────────┼─
veggie │ text │ │ │ │
kcals_per_kg │ integer │ │ not null │ │
type │ text │ │ │ │
Not-null constraints:
"yep" NOT NULL "kcals_per_kg" (local, inherited)
Inherits: vegetables
Access method: heap
-- In the other table it's only inherited.
=# \d+ nightshades
Table "postgres_air.nightshades"
Column │ Type │ Collation │ Nullable │ Default │
──────────────┼─────────┼───────────┼──────────┼─────────┼
veggie │ text │ │ │ │
kcals_per_kg │ integer │ │ not null │ │
Not-null constraints:
"shortnm" NOT NULL "kcals_per_kg" (inherited)
Inherits: vegetables
Access method: heapI must also praise the help given by our recently hired engineer Jian He, whose work was extremely valuable to find further corner cases that were not behaving as designed. I'm somewhat proud to say that he started just testing it, but eventually turned to writing some of the necessary C code. Jian makes a fine addition to the PostgreSQL Global Development Team, and I'm happy that he's helping us with the many tasks at EDB!
Team work, conclusions
To conclude, I am quite happy with the way this hacking project turned out: we ended up producing something that some users will appreciate, while at the same time giving a couple of developers at EDB the opportunity to experience the development workflow of the wider Postgres community. I know the process has its ups and downs, but I hope they will stick at it. We have room for more work: recently, the SQL standard added NOT ENFORCED constraints as a feature, and Jian He has already proposed patches to implement this for not-null constraints in the next Postgres version. And, who knows, maybe somebody will be motivated to implement DEFERRED not-null constraints also! I have already heard from folks that are looking forward to that, odd though it may sound.
Your help in testing this area is always appreciated. Just verifying that your databases can be cleanly upgraded by pg_upgrade, and that the resulting upgraded database can be cleanly dumped by pg_dump — that's already good to know. But also trying out whether these new constraint features I mentioned work as you would expect is valuable. And of course, proof-reading the documentation changes! We already got past beta, so the number of bugs is not expected to be very high — but maybe with unusual schemas something could still turn up. These aren't headline features, but they are finicky to get right, and we will rush to fix anything that isn't working as designed. Myself, I'm satisfied that I was able to carry this to completion.
And now, on to the next one!