pg_upgrade does not support a table 2 in the original database to inherit from table 1 (field F_Test1 is not empty), and then table 2 modifies F by itself_ Test1 is nullable

From: 小杨 <17097859508(at)qq(dot)com>
To: pgsql-bugs <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: pg_upgrade does not support a table 2 in the original database to inherit from table 1 (field F_Test1 is not empty), and then table 2 modifies F by itself_ Test1 is nullable
Date: 2023-02-24 03:22:35
Message-ID: tencent_DD4C878DE5258D129A12D9AD9993054F4408@qq.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

pg_upgrade does not support a table 2 in the original database to inherit from table 1 (field F_Test1 is not empty), and then table 2 modifies F by itself_ Test1 is nullable

标题:pg_upgrade 不支持原始数据库里的某个表2从表1继承(字段F_Test1非空),表2然后自己修改F_Test1为可空
---------------------
[Emphasis] [Test version PostgreSQL 15.2] pg_ Upgrade internal call pg_ The dump command line uses the -- binary upgrade parameter
Problem description:
If a table 2 in the original database inherits from table 1 (field F_Test1 is not empty), table 2 then modifies F_ Test1 is nullable, use pg_ Dump -- binary-upgrade After backup.
pg_ When restoring, after creating Table 1 and Table 2 respectively, execute something like ALTER TABLE "T1" INHERIT "T2"; Error will be reported.
1) [Test succeeded] After the test, do not use pg_ The -- binary-upgrade parameter of dump uses the conventional parameter, after backup;
Use pg_ Whether restore can successfully restore the database using the - disable-triggers parameter or not.
2)pg_ The dump -- binary upgrade parameter is the same as the general SQL support. Create an inheritance relationship between two tables with the following restrictions:
DROP TABLE "T1";
CREATE TABLE IF NOT EXISTS "T1"(
"F_Test" text NOT NULL, -- inherited field
"F_Other" text -- other fields
);
DROP TABLE "T2";
CREATE TABLE IF NOT EXISTS "T2"(
"F_Test" text -- Inherit field. [Null field]
);
ALTER TABLE "T1" INHERIT "T2"; -- Set inheritance relationship. [Implementation succeeded]. When T1. F_ When Test is not empty, T2. F_ Whether the test uses NOT NULL is successful.
ALTER TABLE "T1" NO INHERIT "T2"; -- Cancel inheritance
ALTER TABLE "T2" ALTER COLUMN "F_Test" SET NOT NULL; -- T2.F_ Test field is set to non-empty
ALTER TABLE "T1" ALTER COLUMN "F_Test" DROP NOT NULL; -- T1.F_ The Test field is set to nullable
ALTER TABLE "T1" INHERIT "T2"; -- [Execution failed]. When T2. F_ When Test is not empty, T1. F_ Test must be NOT NULL or the execution fails.
1&gt; When creating an inheritance relationship between two tables, the parent table is a non-empty field, and the corresponding field of the child table must be a non-empty field, otherwise the inheritance relationship cannot be established.
If the field of the child table is non-empty, the inheritance relationship can be established whether the corresponding field of the parent table is non-empty.
---------------------------------------------------------------
【重点】【测试版本PostgreSQL 15.2】pg_upgrade内部调用pg_dump命令行使用--binary-upgrade参数

问题描述:
如果原始数据库里的某个表2从表1继承(字段F_Test1非空),表2然后自己修改F_Test1为可空,使用pg_dump --binary-upgrade备份后。
pg_restore还原时,在分别创建表1、表2后,执行类似ALTER TABLE "T1" INHERIT "T2";会报错。

&nbsp; 1)【测试成功】经过测试,此情况,不要使用pg_dump的--binary-upgrade参数,而是使用常规的参数,备份后;
&nbsp; &nbsp; &nbsp; 使用pg_restore是否使用-disable-triggers参数都能成功还原数据库。

&nbsp; 2)pg_dump--binary-upgrade参数与常规支持SQL一样。新建两个表之间的继承关系,有如下条件限制:
DROP TABLE "T1";
CREATE TABLE IF NOT EXISTS "T1"(
&nbsp; "F_Test" text NOT NULL, &nbsp;--继承字段
&nbsp; "F_Other" text --其他字段
);
DROP TABLE "T2";
CREATE TABLE IF NOT EXISTS "T2"(
&nbsp; "F_Test" text --继承字段。【可空字段】
);
ALTER TABLE "T1" INHERIT "T2"; --设置继承关系。【执行成功】。当T1.F_Test非空时,T2.F_Test是否使用NOT NULL都是执行成功。
ALTER TABLE "T1" NO INHERIT "T2"; --取消继承
ALTER TABLE "T2" ALTER COLUMN "F_Test" SET NOT NULL; --T2.F_Test字段设置为非空
ALTER TABLE "T1" ALTER COLUMN "F_Test" DROP NOT NULL; --T1.F_Test字段设置为可空
ALTER TABLE "T1" INHERIT "T2"; --【执行失败】。当T2.F_Test非空时,T1.F_Test必须为NOT NULL否则执行失败。
&nbsp; &nbsp; 1&gt; 新建两个表之间的继承关系时,父表为非空字段,子表则对应字段必须为非空字段,否则无法建立继承关系。
&nbsp; &nbsp; 如果子表的字段为非空,父表的对应字段是否为非空都可以建立继承关系。 &nbsp; &nbsp;

Browse pgsql-bugs by date

  From Date Subject
Next Message Kyotaro Horiguchi 2023-02-24 07:08:12 Re: BUG #17804: Assertion failed in pg_stat after fetching from pg_stat_database and swithing cache->snapshot
Previous Message Andres Freund 2023-02-24 01:54:17 Re: BUG #17800: ON CONFLICT DO UPDATE fails to detect incompatible fields that leads to a server crash