Zsolt Parragi <[email protected]> writes:
> On Sun, 08 Nov 2026, Tom Lane <[email protected]> wrote:
>> The test query alone would have been fine, but without a
>> straightforward repair recipe, I'm not sure how useful it is. I don't
>> want to expend a large amount of note space on a problem that likely
>> affects very few people. Maybe we could stop at offering the ALTER
>> TABLE commands, and leave people to cope with finding any NULLs on
>> their own?
> Yes, I think even the test query could be enough, and then simply
> mentioning that the constraint has to be fixed or dropped.
OK, this is what I've got now:
<listitem>
<!--
Author: David Rowley <[email protected]>
Branch: master [700b422af] 2026-10-27 19:46:12 +1300
Branch: REL_19_STABLE [a01d1a17e] 2026-10-27 19:46:42 +1300
Branch: REL_18_STABLE [35f60e5ce] 2026-10-27 19:47:09 +1300
-->
<para>
Disallow <literal>NOT NULL NO INHERIT</literal> constraints on
partitioned tables (David Rowley, Nitin Motiani)
<ulink url="&commit_baseurl;35f60e5ce">§</ulink>
</para>
<para>
This was already rejected by plain <command>CREATE TABLE</command>,
but cases such as <command>CREATE TABLE LIKE</command> missed
checking copied constraints, potentially allowing a partition to
contain null values when it should not.
</para>
<para>
If you suspect this might have happened in your database, issue this
command to find broken constraints:
<programlisting>
SELECT conrelid::regclass, conname
FROM pg_constraint
WHERE contype = 'n' AND connoinherit
AND conrelid IN (SELECT oid FROM pg_class WHERE relkind = 'p');
</programlisting>
Unwanted constraints can be removed with <literal>ALTER TABLE
... DROP CONSTRAINT ...</literal>, while ones that are wanted should
be repaired with <literal>ALTER TABLE ... ALTER CONSTRAINT
... INHERIT</literal>. The latter command will fail if any null
values are present in the table's partitions.
</para>
</listitem>
regards, tom lane
