Re: cataloguing NOT NULL constraints
| От | Andrew Bille |
|---|---|
| Тема | Re: cataloguing NOT NULL constraints |
| Дата | |
| Msg-id | CAJnzarwkfRu76_yi3dqVF_WL-MpvT54zMwAxFwJceXdHB76bOA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: cataloguing NOT NULL constraints (Alvaro Herrera <alvherre@alvh.no-ip.org>) |
| Ответы |
Re: cataloguing NOT NULL constraints
(Alvaro Herrera <alvherre@alvh.no-ip.org>)
|
| Список | pgsql-hackers |
Hi Alvaro,
25.08.2023 14:38, Alvaro Herrera wrote:
> I have now pushed this again. Hopefully it'll stick this time.
> 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);
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
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
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;
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/
В списке pgsql-hackers по дате отправления:
Следующее
От: Bertrand DrouvotДата:
Сообщение: Re: Split index and table statistics into different types of stats