Re: cataloguing NOT NULL constraints

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(at)alvh(dot)no-ip(dot)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/
>
>
>
>
>