From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Better handle indirect constraint drops
Date: Fri, 19 Apr 2024 10:38:15 +0000
Message-ID: <E1rxldH-002pB7-7m@gemulon.postgresql.org> (raw)
Better handle indirect constraint drops
It is possible for certain cases to remove not-null constraints without
maintaining the attnotnull in its correct state; for example if you drop
a column that's part of the primary key, and the other columns of the PK don't
have not-null constraints, then we should reset the attnotnull flags for
those other columns; up to this commit, we didn't. Handle those cases
better by doing the attnotnull reset in RemoveConstraintById() instead
of in dropconstraint_internal().
However, there are some cases where we must not do so. For example if
those other columns are in replica identity indexes or are generated
identity columns, we must keep attnotnull set, even though it results in
the catalog inconsistency that no not-null constraint supports that.
Because the attnotnull reset now happens in more places than before, for
instance when a column of the primary key changes type, we need an
additional trick to reinstate it as necessary. Introduce a new
alter-table pass that does this, which needs simply reschedule some
AT_SetAttNotNull subcommands that were already being generated and
ignored.
Because of the exceptions in which attnotnull is not reset noted above,
we also include a pg_dump hack to include a not-null constraint when the
attnotnull flag is set even if no pg_constraint row exists. This part
is undesirable but necessary, because failing to handle the case can
result in unrestorable dumps.
Reported-by: Tender Wang <tndrwang@gmail.com>
Co-authored-by: Tender Wang <tndrwang@gmail.com>
Reviewed-by: jian he <jian.universality@gmail.com>
Discussion: https://postgr.es/m/CAHewXN=hMbNa3d43NOR=OCgdgpTt18S-1fmueCoEGesyeK4bqw@mail.gmail.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/0cd711271d42b0888d36f8eda50e1092c2fed4b3
Modified Files
--------------
src/backend/catalog/pg_constraint.c | 117 +++++++++++++++++++++++++-
src/backend/commands/tablecmds.c | 135 +++++++++++++++---------------
src/bin/pg_dump/pg_dump.c | 30 +++++--
src/test/regress/expected/constraints.out | 78 +++++++++++++++++
src/test/regress/sql/constraints.sql | 39 +++++++++
5 files changed, 323 insertions(+), 76 deletions(-)
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-committers@postgresql.org
Cc: alvherre@alvh.no-ip.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Better handle indirect constraint drops
In-Reply-To: <E1rxldH-002pB7-7m@gemulon.postgresql.org>
* 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