Re: information_schema and not-null constraints

Vik Fearing <vik@postgresfriends.org>

From: Vik Fearing <vik@postgresfriends.org>

To: Alvaro Herrera <alvherre@alvh.no-ip.org>, Pg Hackers <pgsql-hackers@lists.postgresql.org>, Tom Lane <tgl@sss.pgh.pa.us>, Peter Eisentraut <peter@eisentraut.org>

Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>

Date: 2023-09-05T21:50:04Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Revert structural changes to not-null constraints
- 6f8bb7c1e961 17.0 landed
Fix inconsistencies in error messages
- 21ac38f498b3 17.0 landed
Disallow direct change of NO INHERIT of not-null constraints
- d45597f72fe5 17.0 landed
Disallow NO INHERIT not-null constraints on partitioned tables
- 13daa33fa5a6 17.0 landed
Better handle indirect constraint drops
- 0cd711271d42 17.0 cited
Don't try to assign smart names to constraints
- d72d32f52d26 17.0 cited
Fix restore of not-null constraints with inheritance
- d9f686a72ee9 17.0 landed
ATTACH PARTITION: Don't match a PK with a UNIQUE constraint
- cee8db3f680b 17.0 landed
Fix propagating attnotnull in multiple inheritance
- c3709100be73 17.0 landed
Check stack depth in new recursive functions
- b0f7dd915bca 17.0 landed
Move privilege check to the right place
- ac22a9545ca9 17.0 cited
Update information_schema definition for not-null constraints
- 3af721794272 17.0 landed
Fix not-null constraint test
- d0ec2ddbe088 17.0 landed
Disallow changing NO INHERIT status of a not-null constraint
- 9b581c534186 17.0 cited
Catalog not-null constraints
- b0e96f311985 17.0 cited
parallel_schedule: add comment on event_trigger test dependency
- c8e43c22be27 17.0 landed
Revert "Catalog NOT NULL constraints" and fallout
- 9ce04b50e120 16.0 landed
Adjust contrib/sepgsql regression test expected outputs.
- 76c111a7f166 16.0 landed
Fix table name clash in recently introduced test
- 728015a47016 16.0 landed
Catalog NOT NULL constraints
- e056c557aef4 16.0 landed
Change the rules for inherited CHECK constraints to be essentially the same
- cd902b331dc4 8.4.0 cited

On 9/5/23 19:15, Alvaro Herrera wrote:
> On 2023-Sep-05, Alvaro Herrera wrote:
> 
> Looking now at what to do for CHECK_CONSTRAINTS with domain constraints,
> I admit I'm completely confused about what this view is supposed to
> show.  Currently, we show the constraint name and a definition like
> "CHECK (column IS NOT NULL)".  But since the table name is not given, it
> is not possible to know to what table the column name refers to.  For
> domains, we could show "CHECK (VALUE IS NOT NULL)" but again with no
> indication of what domain it applies to, or anything at all that would
> make this useful in any way whatsoever.

Constraint names are supposed to be unique per schema[1] so the view 
contains the minimum required information to identify the constraint.

> So this whole thing seems pretty futile and I'm disinclined to waste
> much time on it.

Until PostgreSQL either
   A) obeys the spec on this uniqueness, or
   B) decides to deviate from the information_schema spec;
this view will be completely useless for actually getting any useful 
information.

I would like to see us do A because it is the right thing to do.  Our 
autogenerated names obey this rule, but who knows how many duplicate 
names per schema are out there in the wild from people specifying their 
own names.

I don't know what the project would think about doing B.

[1] SQL:2023-2 11.4 <table constraint definition> Syntax Rule 4
-- 
Vik Fearing