Thread: Accidentally dropped constraints: bug?
If we drop a column we cascade that drop to all indexes and all
constraints that mention that column, even if they include other
columns also. We might expect that indexes should be dropped
automatically, but the latter behavior for constraints seems like a
bug, since it can silently remove constraints that might still be
valid without the dropped column. (Example below). This is even more
surprising if the user specifies RESTRICT explicitly. I note that this
is acting as documented, it's just the docs don't explain the full
implications, so I'm guessing we didn't think about this before.
The effect of accidentally removing a constraint can be fairly
dramatic, for example, tables suddenly start refusing updates/deletes
if they are part of a publication, or partitioning schemes that depend
upon check constraints can suddenly stop working. As well as the more
obvious loss of protection from bad input data.
ISTM that we should refuse to drop constraints, if the constraint is
also dependent upon other columns that will remain in the table,
unless the user requests CASCADE.
- - -
create table abc (a int, b int, c int, primary key (a,b,c), check (a >
5 and b is not null and c > 10));
create index bc on abc (b, c);
\d abc
Table "public.abc"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | not null |
b | integer | | not null |
c | integer | | not null |
Indexes:
"abc_pkey" PRIMARY KEY, btree (a, b, c)
"bc" btree (b, c)
Check constraints:
"abc_c_check" CHECK (c > 9)
"abc_check" CHECK (a > 5 AND b IS NOT NULL AND c > 10)
alter table abc drop column c restrict;
\d abc
Table "public.abc"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | not null |
b | integer | | not null |
Noting that all constraints have been removed, not just the ones
wholly dependent on dropped columns.
--
Simon Riggs http://www.EnterpriseDB.com/
On Thu, Aug 5, 2021 at 08:35:46AM +0100, Simon Riggs wrote: > If we drop a column we cascade that drop to all indexes and all > constraints that mention that column, even if they include other > columns also. We might expect that indexes should be dropped > automatically, but the latter behavior for constraints seems like a > bug, since it can silently remove constraints that might still be > valid without the dropped column. (Example below). This is even more > surprising if the user specifies RESTRICT explicitly. I note that this > is acting as documented, it's just the docs don't explain the full > implications, so I'm guessing we didn't think about this before. Wow, I certainly never thought of it or heard anyone complain about it. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
> On Aug 5, 2021, at 12:35 AM, Simon Riggs <simon.riggs@enterprisedb.com> wrote: > > Noting that all constraints have been removed, not just the ones > wholly dependent on dropped columns. I don't find this all that surprising. If CHECK (a > 5 AND b IS NOT NULL AND c > 10) is really meant as three independentchecks, it should be written that way. However, for any row where c is null, the entire expression will eitherbe null or false, and the check will fail precisely when (a > 5 AND b IS NOT NULL) is false. So if you imagine thedropped column as a column of phantom null values, you'd expect the check to still reject rows where a <= 5 or b is null. Is it reasonable to imagine the dropped column as implicitly full of nulls? That's how an added column works, but do wethink about dropped columns that way? In any event, the documentation is pretty clear about this: > DROP COLUMN [ IF EXISTS ] > This form drops a column from a table. Indexes and table constraints involving the column will be automatically droppedas well. — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company