On Thu, Apr 11, 2024 at 10:48 PM Alvaro Herrera <alvherre@alvh.no-ip.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.
I'm not sure that SQL standard specifies what database must do for this case.
If the standard does not specify, then it depends on each database vendor's decision.
Some people like not-null retained, other people may like not-null removed.
I think it will be ok if people can drop not-null or add not-null back again after dropping pk.
In Master, not-null will reset when we drop PK directly. I hope dropping pk indirectly
is consistent with dropping PK directly.
--