Subject: Re: cataloguing NOT NULL constraints

From: Peter Eisentraut <[email-masked]>
Date: Tue, 5 Sep 2023 13:29:47 +0200

On 31.08.23 12:02, Alvaro Herrera wrote:
> In constraint_column_usage, you're adding a relkind to the catalog scan
> that goes through pg_depend for CHECK constraints.  Here we can rely on
> a simple conkey[1] check and a separate UNION ALL arm[q5]; this is also
> faster when there are many tables.
> 
> The third view definition looks ok.  It's certainly very nice to be able
> to delete XXX comments there.

The following information schema views are affected by the not-null 
constraint catalog entries:

1. CHECK_CONSTRAINTS
2. CONSTRAINT_COLUMN_USAGE
3. DOMAIN_CONSTRAINTS
4. TABLE_CONSTRAINTS

Note that 1 and 3 also contain domain constraints.  So as long as the 
domain not-null constraints are not similarly catalogued, we can't 
delete the separate not-null union branch.  (3 never had one, so 
arguably a bit buggy.)

I think we can fix up 4 by just deleting the not-null union branch.

For 2, the simple fix is also easy, but there are some other options, as 
you discuss above.

How do you want to proceed?

---

From: Alvaro Herrera <[email-masked]>
Date: Tue, 5 Sep 2023 17:29:56 +0200

On 2023-Sep-05, Peter Eisentraut wrote:

> Note that 1 and 3 also contain domain constraints.  So as long as the domain
> not-null constraints are not similarly catalogued, we can't delete the
> separate not-null union branch.  (3 never had one, so arguably a bit buggy.)
> 
> For 2, the simple fix is also easy, but there are some other options, as you
> discuss above.
> 

I posted as a patch in a separate thread[1].  Let me fix up the
definitions for views 1 and 3 for domains per your comments, and I'll
post in that thread again.

[1] https://postgr.es/m/[email-masked]

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"Ninguna manada de bestias tiene una voz tan horrible como la humana" (Orual)