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

From: jian he <jian(dot)universality(at)gmail(dot)com>
To: Tender Wang <tndrwang(at)gmail(dot)com>
Cc: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Can't find not null constraint, but \d+ shows that
Date: 2024-04-11 08:49:35
Message-ID: CACJufxFfcqScDSXTfM8BKMGbNdy1v0fcrEZHRR5BejtOTceG+A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Thu, Apr 11, 2024 at 3:19 PM Tender Wang <tndrwang(at)gmail(dot)com> wrote:
>
>> +DROP TABLE notnull_tbl1;
>> +-- make sure attnotnull is reset correctly when a PK is dropped indirectly
>> +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 | | not null | | plain | |
>> +
>>
>> this is not what we expected?
>> "not null" for "c0" now should be false?
>> am I missing something?
>
> Yeah, now this is expected behavior.
> Users can drop manually not-null of "c0" if they want, and no error reporte.
>

sorry for the noise.
these two past patches confused me:
0001-Correctly-reset-attnotnull-when-constraints-dropped-.patch
v4-0001-Fix-pg_attribute-attnotnull-not-reset-when-droppe.patch

I thought dropping a column of primary key (ALTER TABLE notnull_tbl2 DROP c1)
will make the others key columns to not have "not null" property.

now I figured out that
dropping a column of primary key columns will not change other key
columns' "not null" property.
dropping the primary key associated constraint will make all key
columns "not null" property disappear.

v2-0001-Handle-ALTER-.-DROP-NOT-NULL-when-no-pg_constrain.patch
behavior looks fine to me now.

inline drop_orphaned_notnull in ATExecDropNotNull looks fine to me.

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Dmitry Koval 2024-04-11 08:59:10 Re: Add SPLIT PARTITION/MERGE PARTITIONS commands
Previous Message Masahiko Sawada 2024-04-11 08:20:33 Re: Improve eviction algorithm in ReorderBuffer