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 <[email protected]> Author: David Rowley <[email protected]> Author: Nitin Motiani <[email protected]> Reviewed-by: Chao Li <[email protected]> Reviewed-by: Nitin Motiani <[email protected]> Discussion: https://postgr.es/m/[email protected] Discussion: https://postgr.es/m/CAH5HC96YCzOy+9JZLQ4n0sMchVnharTOXrr4sbYuh=_d+ff...@mail.gmail.com Backpatch-through: 18 Branch ------ REL_19_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/a01d1a17e9a38187421957dd2455de3d8d8d893f 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(-)
