Horton

Re: cataloguing NOT NULL constraints

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Robert Haas <robertmhaas@gmail.com>, Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Peter Eisentraut <peter@eisentraut.org>, pgsql-hackers@lists.postgresql.org
Date: 2023-07-25T12:35:54Z
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 →

  1. Revert structural changes to not-null constraints

  2. Fix inconsistencies in error messages

  3. Disallow direct change of NO INHERIT of not-null constraints

  4. Disallow NO INHERIT not-null constraints on partitioned tables

  5. Better handle indirect constraint drops

  6. Don't try to assign smart names to constraints

  7. Fix restore of not-null constraints with inheritance

  8. ATTACH PARTITION: Don't match a PK with a UNIQUE constraint

  9. Fix propagating attnotnull in multiple inheritance

  10. Check stack depth in new recursive functions

  11. Move privilege check to the right place

  12. Update information_schema definition for not-null constraints

  13. Fix not-null constraint test

  14. Disallow changing NO INHERIT status of a not-null constraint

  15. Catalog not-null constraints

  16. parallel_schedule: add comment on event_trigger test dependency

  17. Revert "Catalog NOT NULL constraints" and fallout

  18. Adjust contrib/sepgsql regression test expected outputs.

  19. Fix table name clash in recently introduced test

  20. Catalog NOT NULL constraints

  21. Change the rules for inherited CHECK constraints to be essentially the same

Attachments

On 2023-Jul-24, Robert Haas wrote:

> I think I might want to retract my earlier -1 vote. I mean, I agree
> with former me that having the \d+ output get a whole lot longer is
> not super-appealing. But I also agree with Dean that having this
> information available somewhere is probably important, and I also
> agree with your point that inventing \d++ for this isn't necessarily a
> good idea. I fear that will just result in having to type an extra
> plus sign any time you want to see all of the table details, to make
> sure that psql knows that you really mean it. So, maybe showing it in
> the \d+ output as Dean proposes is the least of evils.

Okay then, I've made these show up in the footer of \d+.  This is in
patch 0003 here.  Please let me know what do you think of the regression
changes.

On 2023-Jul-24, Dean Rasheed wrote:

> To reproduce this error, try to create 2 constraints with the same
> name on different columns:
> 
> create table foo(a int, b int);
> alter table foo add constraint nn not null a;
> alter table foo add constraint nn not null b;

Ah, of course.  Fixed.

> I found another, separate issue:
> 
> create table p1(a int not null);
> create table p2(a int);
> create table foo () inherits (p1,p2);
> alter table p2 add not null a;
> 
> ERROR:  column "a" of table "foo" is already NOT NULL
> 
> whereas doing "alter table p2 alter column a set not null" works OK,
> merging the constraints as expected.

True.  I made it a non-error.  I initially changed the message to INFO,
as suggested by Vik nearby; but after noticing that SET NOT NULL just
does the same thing with no message, I removed this message altogether,
for consistence.  Now that I did it, though, I wonder: if the user
specified a constraint name, and that name does not match the existing
constraint, maybe we should have an INFO or NOTICE or WARNING message
that the requested constraint name was not satisfied.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/