Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPjB7-00000000rgR-20jc for pgsql-bugs@arkaria.postgresql.org; Sat, 07 Nov 2026 16:22:06 +0000 Received: from localhost ([127.0.0.1]:57128 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xPirG-000000049ly-0FW6 for pgsql-bugs@arkaria.postgresql.org; Sat, 07 Nov 2026 16:01:34 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:33208) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPi6N-00000003iwZ-0004 for pgsql-bugs@lists.postgresql.org; Sat, 07 Nov 2026 15:13:07 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPi6J-00000000j3E-4C8E for pgsql-bugs@lists.postgresql.org; Sat, 07 Nov 2026 15:13:05 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=HFZnrsGMG6Ega9FzDRbn4At+5TwZOggvIvsVwpINv8I=; b=n3dUu2QSpyNfJK1berExdOWGQi kZYqtImGvjeffOBshHH///GwzgyJeq5UmHJwHCZpLygjJ/I6W5qlsfyaoT7kNabBd0TyUh0VU25Go rWtDUeOpropxtXJuKl3+Z3kVjwPXrEHbONNVcgqGy71jaAwtWc/KoACoB755oyKjzBzPlSyZfgMtC krnJc32hgrl9uUa1flidTcXCQ5Zwreas2oMFaO6RyiQG5GMJ0QYA0lJRkX2aU8Gqb+i0TyPZadPv8 KWB/BkKh3zWeJxjGPowtCX0h7oiqb9A76uGDTIYshXY/GgOH2adyhZ82SuEQ1xqkr9Gidk/V24jyy nTf+60lQ==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPi6L-000000021fu-086y for pgsql-bugs@lists.postgresql.org; Sat, 07 Nov 2026 15:13:05 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xPi6J-000000056YG-0S6c for pgsql-bugs@lists.postgresql.org; Sat, 07 Nov 2026 15:13:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19847: pg_dump loses ENFORCED on inherited CHECK under NOT ENFORCED parent To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: 125atishprak@gmail.com Reply-To: 125atishprak@gmail.com, pgsql-bugs@lists.postgresql.org Date: Sat, 07 Nov 2026 15:12:50 +0000 Message-ID: <19847-f91f92380ce652c9@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk The following bug has been logged on the website: Bug reference: 19847 Logged by: Atish Prakash Email address: 125atishprak@gmail.com PostgreSQL version: 19rc1 Operating system: Linux x86_64 Description: =20 If a child's inherited CHECK constraint is made ENFORCED while the parent's stays NOT ENFORCED, a dump and restore silently makes the child's constraint NOT ENFORCED. The restored table then accepts rows the original rejects, with no warning from pg_dump or from the restore. CREATE TABLE ckp(a int, CONSTRAINT c CHECK (a > 0) NOT ENFORCED); CREATE TABLE ckc() INHERITS (ckp); ALTER TABLE ckc ALTER CONSTRAINT c ENFORCED; INSERT INTO ckc VALUES (-1); -- ERROR: new row for relation "ckc" violates check constraint "c" Then run pg_dump and restore into an empty database: INSERT INTO ckc VALUES (-1); -- INSERT 0 1 pg_constraint for ckc.c: original: conenforced =3D t, convalidated =3D t, conislocal =3D f, coninhcount =3D 1 restored: conenforced =3D f, convalidated =3D f, conislocal =3D f, coninhcount =3D 1 Expected: the restored child constraint stays ENFORCED. The dump contains only the parent's constraint, which the child inherits when it is created, so nothing records the child's different state. A child that has its own local constraint is not affected: an ENFORCED partition attached under a NOT ENFORCED parent restored correctly in my test. pg_upgrade from 19rc1 to master kept ENFORCED in my test, so this affects plain dumps. Probably the same mechanism: a child's constraint that was validated while the parent's stays NOT VALID comes back NOT VALID after a restore: CREATE TABLE c8p(a int); CREATE TABLE c8c(a int) INHERITS (c8p); ALTER TABLE c8p ADD CONSTRAINT k CHECK (a > 0) NOT VALID; ALTER TABLE c8c VALIDATE CONSTRAINT k; I have not written a patch. Found with dump and restore differential tests. I reproduced and checked the result myself.