Re: Can't find not null constraint, but \d+ shows that

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> 于2024年4月10日周三 21:58写道：

> 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)
>

[1]
https://www.postgresql.org/message-id/CAHewXNn_So7LUCxxxyDNfdvCQp1TnD3gTVECBZX2bT_nbPgraQ%40mail.gmail.com
--
Tender Wang
OpenPie: https://en.openpie.com/