It turns out that trying to close all holes that lead to columns marked
not-null without a pg_constraint row is not possible within the ALTER
TABLE framework, because it can happen outside it also. Consider this
CREATE DOMAIN dom1 AS integer;
CREATE TABLE notnull_tbl (a dom1, b int, PRIMARY KEY (a, b));
DROP DOMAIN dom1 CASCADE;
In this case you'll end up with b having attnotnull=true and no
constraint; and no amount of messing with tablecmds.c will fix it.
I try above case on my v4 patch[1], and it seems no result as what you said.
But, anyway, I now don't like updating other catalog in RemoveConstraintById().
Because it will not be friendly for others who call RemoveConstraintById() want only
to remove pg_constraint tuple, but actually it do more works stealthily.
So I propose to instead allow those constraints, and treat them as
second-class citizens. We allow dropping them with ALTER TABLE DROP NOT
NULL, and we allow to create a backing full-fledged constraint with SET
NOT NULL or ADD CONSTRAINT. So here's a partial crude initial patch to
do that.
Hmm, the patch looks like the patch in my first email in this thread. But my v1 patch seem
a poc at most.
One thing missing here is pg_dump support. If you just dump this table,
it'll end up with no constraint at all. That's obviously bad, so I
propose we have pg_dump add a regular NOT NULL constraint for those, to
avoid perpetuating the weird situation further.
Another thing I wonder if whether I should use the existing
set_attnotnull() instead of adding drop_orphaned_notnull(). Or we could
just inline the code in ATExecDropNotNull, since it's small and
self-contained.
I like just inline the code in ATExecDropNotNull, as you said, it's small and self-contained.
in ATExecDropNotNull(), we had open the pg_attributed table and hold RowExclusiveLock,
the tuple we also get.
What we do is set attnotnull = false, and call CatalogTupleUpdate.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"Postgres is bloatware by design: it was built to house
PhD theses." (Joey Hellerstein, SIGMOD annual conference 2002)