From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: jian he <jian.universality@gmail.com>
Cc: Tender Wang <tndrwang@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Can't find not null constraint, but \d+ shows that
Date: Fri, 12 Apr 2024 09:52:05 +0200
Message-ID: <202404120752.6ebv4q5zwnfw@alvherre.pgsql> (raw)
In-Reply-To: <CACJufxHNKqhFZBC_m0SVUg_AAq7HQcpyk8L+H8woH45Smj5SRg@mail.gmail.com>
On 2024-Apr-12, jian he wrote:
> Now I am more confused...
> +CREATE TABLE notnull_tbl1 (c0 int, c1 int, PRIMARY KEY (c0, c1));
> +ALTER TABLE notnull_tbl1 DROP c1;
> same query, mysql make let "c0" be not null
Yes, that was Postgres' old model. But the way we think of it now, is
that a column is marked attnotnull when a pg_constraint entry exists to
support that flag, which can be a not-null constraint, or a primary key
constraint. In the old Postgres model, you're right that we would
continue to have c0 as not-null, just like mysql. In the new model,
that flag no longer has no reason to be there, because the backing
primary key constraint has been removed, which is why we reset it.
So what I was saying in the cases with replica identity and generated
columns, is that there's an attnotnull flag we cannot remove, because of
either of those things, but we don't have any backing constraint for it,
which is an inconsistency with the view of the world that I described
above. I would like to manufacture one not-null constraint at that
point, or just abort the drop of the PK ... but I don't see how to do
either of those things.
If you want the c0 column to be still not-null after dropping the
primary key, you need to SET NOT NULL:
CREATE TABLE notnull_tbl1 (c0 int, c1 int, PRIMARY KEY (c0, c1));
ALTER TABLE notnull_tbl1 ALTER c0 SET NOT NULL;
ALTER TABLE notnull_tbl1 DROP c1;
\d+ notnull_tbl1
Table "public.notnull_tbl1"
Column │ Type │ Collation │ Nullable │ Default │ Storage │ Compression │ Stats target │ Description
────────┼─────────┼───────────┼──────────┼─────────┼─────────┼─────────────┼──────────────┼─────────────
c0 │ integer │ │ not null │ │ plain │ │ │
Not-null constraints:
"notnull_tbl1_c0_not_null" NOT NULL "c0"
Access method: heap
One thing that's not quite ideal, is that the "Nullable" column doesn't
make it obvious that the flag is going to be removed if you drop the PK;
you have to infer that that's going to happen by noticing that there's
no explicit not-null constraint listed for that column -- maybe too
subtle, especially if you have a lot of columns (luckily, PKs normally
don't have too many columns). This is why I suggested to change the
contents of that column if the flag is sustained by the PK. Something
like this, perhaps:
=# CREATE TABLE notnull_tbl1 (c0 int not null, c1 int, PRIMARY KEY (c0, c1));
=# \d+ notnull_tbl1
Table "public.notnull_tbl1"
Column │ Type │ Collation │ Nullable │ Default │ Storage │ Compression │ Stats target │ Description
────────┼─────────┼───────────┼─────────────┼─────────┼─────────┼─────────────┼──────────────┼─────────────
c0 │ integer │ │ not null │ │ plain │ │ │
c1 │ integer │ │ primary key │ │ plain │ │ │
Indexes:
"notnull_tbl1_pkey" PRIMARY KEY, btree (c0, c1)
Not-null constraints:
"notnull_tbl1_c0_not_null" NOT NULL "c0"
Access method: heap
which should make it obvious.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Right now the sectors on the hard disk run clockwise, but I heard a rumor that
you can squeeze 0.2% more throughput by running them counterclockwise.
It's worth the effort. Recommended." (Gerry Pourwelle)
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: pgsql-hackers@postgresql.org
Cc: alvherre@alvh.no-ip.org, jian.universality@gmail.com, tndrwang@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: Can't find not null constraint, but \d+ shows that
In-Reply-To: <202404120752.6ebv4q5zwnfw@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox