pgsql: Disallow NOT NULL NO INHERIT constraints on partitioned tables

Disallow NOT NULL NO INHERIT constraints on partitioned tables

This was blocked during normal CREATE TABLE, but cases such as CREATE
TABLE (LIKE) would copy the source table's NOT NULL NO INHERIT constraints
and apply them to the partitioned table being created. This could mean
that partitions didn't correctly receive the NOT NULL constraint when they
were created, thus could contain NULL values.

cacef1722 added a test to ensure CREATE TABLE (LIKE) PARTITION BY fails
when the source table has any CHECK NO INHERIT constraints. Here we do
the same for NOT NULL constraints.

It's probably unlikely that people have commonly done this, but the
release notes may wish to note that people should check if this has
happened to them. The following query can used each database to identify
problematic constraints:

SELECT conrelid::regclass, conname, connoinherit
FROM pg_constraint
WHERE contype = 'n'
AND connoinherit = true
AND conrelid IN (SELECT oid FROM pg_class WHERE relkind = 'p');

If no results are shown, the queried database isn't affected.

To resolve these issues, unwanted constraints can simply be dropped from
the partitioned table with:

ALTER TABLE ... DROP CONSTRAINT ...;

To remove the NO INHERIT flag, use:

ALTER TABLE ... ALTER CONSTRAINT ... INHERIT;

This will fail if any partition contains a NULL value in the given
column. Finding the cause of failures is made more difficult by the
planner trusting that "WHERE col IS NULL" cannot be true because of the
presence of a NOT NULL constraint. Affected users may need to resort to
tricks like WHERE col+0 IS NULL to find these. If it was thought to be a
common problem, then we could adjust get_relation_notnullatts(), and the
equivalent functions on older branches to ignore constraints marked as NO
INHERIT on partitioned tables, but currently that would require an
expensive lookup to find the pg_constraint row.

Backpatch to v18 where NOT NULL NO INHERIT constraints were added by
14e87ffa5.

Bug: #19770
Reported-by: Ann C <anncalla(at)163(dot)com>
Author: David Rowley <dgrowleyml(at)gmail(dot)com>
Author: Nitin Motiani <nitinmotiani(at)google(dot)com>
Reviewed-by: Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com>
Reviewed-by: Nitin Motiani <nitinmotiani(at)google(dot)com>
Discussion: https://postgr.es/m/19770-7c6e75fc30df7e06@postgresql.org
Discussion: https://postgr.es/m/CAH5HC96YCzOy+9JZLQ4n0sMchVnharTOXrr4sbYuh=_d+ffjAA@mail.gmail.com
Backpatch-through: 18

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/700b422af7e26cf310caf3fb76d78f4c8ca39c1f

Modified Files
--------------
src/backend/catalog/heap.c | 10 ++++++++++
src/test/regress/expected/create_table_like.out | 21 +++++++++++++++++----
src/test/regress/sql/create_table_like.sql | 23 +++++++++++++++++++----
3 files changed, 46 insertions(+), 8 deletions(-)