public inbox for [email protected]  

help / color / mirror / Atom feed

From: Alvaro Herrera <[email protected]>
To: Pg Hackers <[email protected]>
Subject: cataloguing NOT NULL constraints
Date: Wed, 17 Aug 2022 20:12:49 +0200
Message-ID: <[email protected]> (raw)

I've been working on having NOT NULL constraints have pg_constraint
rows.

Everything is working now.  Some things are a bit weird, and I would
like opinions on them:

1. In my implementation, you can have more than one NOT NULL
   pg_constraint row for a column.  What should happen if the user does
   ALTER TABLE .. ALTER COLUMN .. DROP NOT NULL;
   ?  Currently it throws an error about the ambiguity (ie. which
   constraint to drop).
   Using ALTER TABLE DROP CONSTRAINT works fine, and the 'attnotnull'
   bit is lost when the last one such constraint goes away.

2. If a table has a primary key, and a table is created that inherits
   from it, then the child has its column(s) marked attnotnull but there
   is no pg_constraint row for that.  This is not okay.  But what should
   happen?

   1. a CHECK(col IS NOT NULL) constraint is created for each column
   2. a PRIMARY KEY () constraint is created

Note that I've chosen not to create CHECK(foo IS NOT NULL) pg_constraint
rows for columns in the primary key, unless an explicit NOT NULL
declaration is also given.  Adding them would be a very easily solution
to problem 2 above, but ISTM that such constraints would be redundant
and not very nice.

After gathering input on these thing, I'll finish the patch and post it.
As far as I can tell, everything else is working (except the annoying
pg_dump tests, see below).

Thanks

Implementation notes:

In the current implementation I am using CHECK constraints, so these
constraints are contype='c', conkey={col} and the corresponding
expression.

pg_attribute.attnotnull is still there, and it is set true when at least
one "CHECK (col IS NOT NULL)" constraint (and it's been validated) or
PRIMARY KEY constraint exists for the column.

CHECK constraint names are no longer "tab_col_check" when the expression
is CHECK (foo IS NOT NULL).  The constraint is now going to be named
"tab_col_not_null"

If you say CREATE TABLE (a int NOT NULL), you'll get a CHECK constraint
printed by psql: (this is a bit more noisy that previously and it
changes a lot of regression tests output).

55489 16devel 1776237=# create table tab (a int not null);
CREATE TABLE
55489 16devel 1776237=# \d tab
                    Tabla «public.tab»
 Columna │  Tipo   │ Ordenamiento │ Nulable  │ Por omisión 
─────────┼─────────┼──────────────┼──────────┼─────────────
 a       │ integer │              │ not null │ 
Restricciones CHECK:
    "tab_a_not_null" CHECK (a IS NOT NULL)


pg_dump no longer prints NOT NULL in the table definition; rather, the
CHECK constraint is dumped as a separate table constraint (still within
the CREATE TABLE statement though).  This preserves any possible
constraint name, in case one was specified by the user at creation time.

In order to search for the correct constraint for each column for
various DDL actions, I just inspect each pg_constraint row for the table
and match conkey and the CHECK expression.  Some things would be easier
with a new pg_attribute column that carries a pg_constraint.oid of the
constraint for that column; however, that seems to be just catalog bloat
and is not normalized, so I decided not to do it.

Nice side-effect: if you add CHECK (foo IS NOT NULL) NOT VALID, and
later validate that constraint, the attnotnull bit becomes set.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/

view thread (78+ 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: cataloguing NOT NULL constraints
  In-Reply-To: <[email protected]>

* 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