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

On Thu, Apr 11, 2024 at 10:48 PM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>
>
> I'm still not ready with this -- still not convinced about the new AT
> pass. Also, I want to add a test for the pg_dump behavior, and there's
> an XXX comment.
>
Now I am more confused...

+-- make sure attnotnull is reset correctly when a PK is dropped indirectly,
+-- or kept if there's a reason for that
+CREATE TABLE notnull_tbl1 (c0 int, c1 int, PRIMARY KEY (c0, c1));
+ALTER TABLE notnull_tbl1 DROP c1;
+\d+ notnull_tbl1
+ Table "public.notnull_tbl1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats
target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ c0 | integer | | | | plain | |
+
+DROP TABLE notnull_tbl1;

same query, mysql make let "c0" be not null
mysql https://dbfiddle.uk/_ltoU7PO

for postgre
https://dbfiddle.uk/ZHJXEqL1
from 9.3 to 16 (click the link (https://dbfiddle.uk/ZHJXEqL1) then
click "9.3" choose which version you like)
all will make the remaining column "co" be not null.

latest
0001-Better-handle-indirect-constraint-drops.patch make c0 attnotnull be false.

previous patches:
v2-0001-Handle-ALTER-.-DROP-NOT-NULL-when-no-pg_constrain.patch make
c0 attnotnull be true.
0001-Correctly-reset-attnotnull-when-constraints-dropped-.patch make
c0 attnotnull be false.