Horton

Re: cataloguing NOT NULL constraints

Andrew Bille <andrewbille@gmail.com>

View thread
From: Andrew Bille <andrewbille@gmail.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Dean Rasheed <dean.a.rasheed@gmail.com>, Peter Eisentraut <peter@eisentraut.org>, Robert Haas <robertmhaas@gmail.com>, pgsql-hackers@lists.postgresql.org
Date: 2024-01-25T08:21:35Z
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

Hi Alvaro,
25.08.2023 14:38, Alvaro Herrera wrote:
> I have now pushed this again. Hopefully it'll stick this time.

Starting from b0e96f31, pg_upgrade fails with inherited NOT NULL constraint:
For example upgrade from 9c13b6814a (or REL_12_STABLE .. REL_16_STABLE) to
b0e96f31 (or master) with following two tables (excerpt from
src/test/regress/sql/rules.sql)

create table test_0 (id serial primary key);
create table test_1 (id integer primary key) inherits (test_0);

I get the failure:

Setting frozenxid and minmxid counters in new cluster         ok
Restoring global objects in the new cluster                   ok
Restoring database schemas in the new cluster
 test
*failure*

Consult the last few lines of
"new/pg_upgrade_output.d/20240125T151231.112/log/pg_upgrade_dump_16384.log"
for
the probable cause of the failure.
Failure, exiting

In log:

pg_restore: connecting to database for restore
pg_restore: creating DATABASE "test"
pg_restore: connecting to new database "test"
pg_restore: creating DATABASE PROPERTIES "test"
pg_restore: connecting to new database "test"
pg_restore: creating pg_largeobject "pg_largeobject"
pg_restore: creating COMMENT "SCHEMA "public""
pg_restore: creating TABLE "public.test_0"
pg_restore: creating SEQUENCE "public.test_0_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.test_0_id_seq"
pg_restore: creating TABLE "public.test_1"
pg_restore: creating DEFAULT "public.test_0 id"
pg_restore: executing SEQUENCE SET test_0_id_seq
pg_restore: creating CONSTRAINT "public.test_0 test_0_pkey"
pg_restore: creating CONSTRAINT "public.test_1 test_1_pkey"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3200; 2606 16397 CONSTRAINT test_1 test_1_pkey
andrew
pg_restore: error: could not execute query: ERROR:  cannot drop inherited
constraint "pgdump_throwaway_notnull_0" of relation "test_1"
Command was:
-- For binary upgrade, must preserve pg_class oids and relfilenodes
SELECT
pg_catalog.binary_upgrade_set_next_index_pg_class_oid('16396'::pg_catalog.oid);

SELECT
pg_catalog.binary_upgrade_set_next_index_relfilenode('16396'::pg_catalog.oid);


ALTER TABLE ONLY "public"."test_1"
   ADD CONSTRAINT "test_1_pkey" PRIMARY KEY ("id");

ALTER TABLE ONLY "public"."test_1" DROP CONSTRAINT
pgdump_throwaway_notnull_0;

Thanks!




On Thu, Jan 25, 2024 at 3:06 PM Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:

> I have now pushed this again.  Hopefully it'll stick this time.
>
> We may want to make some further tweaks to the behavior in some cases --
> for example, don't disallow ALTER TABLE DROP NOT NULL when the
> constraint is both inherited and has a local definition; the other
> option is to mark the constraint as no longer having a local definition.
> I left it the other way because that's what CHECK does; maybe we would
> like to change both at once.
>
> I ran it through CI, and the pg_upgrade test with a dump from 14's
> regression test database and everything worked well, but it's been a
> while since I tested the sepgsql part of it, so that might the first
> thing to explode.
>
> --
> Álvaro Herrera               48°01'N 7°57'E  —
> https://www.EnterpriseDB.com/
>
>
>
>
>