Hi Alvaro,
Problem 1
========
#create table tpart (a serial primary key, src varchar) partition by range(a);
CREATE TABLE
#create table t_p4 (a int primary key, src varchar);
CREATE TABLE
#\d tpart
Partitioned table "public.tpart"
Column | Type | Collation | Nullable |
Default
--------+-------------------+-----------+----------+----------------------------------
a | integer | | not null |
nextval('tpart_a_seq'::regclass)
src | character varying | | |
Partition key: RANGE (a)
Indexes:
"tpart_pkey" PRIMARY KEY, btree (a)
Number of partitions: 0
#\d t_p4;
Table "public.t_p4"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
a | integer | | not null |
src | character varying | | |
Indexes:
"t_p4_pkey" PRIMARY KEY, btree (a)
Notice that both tpart and t_p4 have their column 'a' marked NOT NULL resp.
#select conname, contype, conrelid::regclass from pg_constraint where
conrelid in ('tpart'::regclass, 't_p4'::regclass);
conname | contype | conrelid
------------------+---------+----------
tpart_a_not_null | n | tpart
tpart_pkey | p | tpart
t_p4_pkey | p | t_p4
(3 rows)
But tparts NOT NULL constraint is recorded in pg_constraint but not
t_p4's. Is this expected?
Both of them have there column a marked not null in pg_attribute
#select attrelid::regclass, attname, attnotnull from pg_attribute
where attrelid in ('tpart'::regclass, 't_p4'::regclass) and attname =
'a';
attrelid | attname | attnotnull
----------+---------+------------
tpart | a | t
t_p4 | a | t
(2 rows)
>From the next set of commands it can be inferred that the NOT NULL
constraint of tpart came because of serial column whereas t_p4's
column a was marked NOT NULL because of primary key. I didn't
investigate the source code.
#create table t_serial(a serial, src varchar);
CREATE TABLE
#select conname, contype, conrelid::regclass from pg_constraint where
conrelid in ('t_serial'::regclass);
conname | contype | conrelid
---------------------+---------+----------
t_serial_a_not_null | n | t_serial
(1 row)
#select attrelid::regclass, attname, attnotnull from pg_attribute
where attrelid in ('t_serial'::regclass) and attname = 'a';
attrelid | attname | attnotnull
----------+---------+------------
t_serial | a | t
(1 row)
Here's what I was trying to do actually.
#alter table tpart attach partition t_p4 for values from (7) to (9);
ERROR: column "a" in child table must be marked NOT NULL
This is a surprise since t_p4.a is marked as NOT NULL. That happens
because MergeConstraintsIntoExisting() only looks at pg_constraint and
not pg_attribute. Should this function look at pg_attribute as well?
This behaviour is different from PG 14. I chanced to have a PG 14
build and hence tried that. I haven't tried PG 15 though.
#select version();
version
-------------------------------------------------------------------------------------------------------
PostgreSQL 14.8 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
(1 row)
#create table tpart (a serial primary key, src varchar) partition by range(a);
CREATE TABLE
#create table t_p4 (a int primary key, src varchar);
CREATE TABLE
#\d tpart
Partitioned table "public.tpart"
Column | Type | Collation | Nullable |
Default
--------+-------------------+-----------+----------+----------------------------------
a | integer | | not null |
nextval('tpart_a_seq'::regclass)
src | character varying | | |
Partition key: RANGE (a)
Indexes:
"tpart_pkey" PRIMARY KEY, btree (a)
Number of partitions: 0
#\d t_p4
Table "public.t_p4"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
a | integer | | not null |
src | character varying | | |
Indexes:
"t_p4_pkey" PRIMARY KEY, btree (a)
#select conname, contype, conrelid::regclass from pg_constraint where
conrelid in ('tpart'::regclass, 't_p4'::regclass);
conname | contype | conrelid
------------+---------+----------
tpart_pkey | p | tpart
t_p4_pkey | p | t_p4
(2 rows)
^
#select attrelid::regclass, attname, attnotnull from pg_attribute
where attrelid in ('tpart'::regclass, 't_p4'::regclass) and attname =
'a';
attrelid | attname | attnotnull
----------+---------+------------
tpart | a | t
t_p4 | a | t
(2 rows)
#alter table tpart attach partition t_p4 for values from (7) to (9);
ALTER TABLE
postgres@1073836=#\d tpart
Partitioned table "public.tpart"
Column | Type | Collation | Nullable |
Default
--------+-------------------+-----------+----------+----------------------------------
a | integer | | not null |
nextval('tpart_a_seq'::regclass)
src | character varying | | |
Partition key: RANGE (a)
Indexes:
"tpart_pkey" PRIMARY KEY, btree (a)
Number of partitions: 1 (Use \d+ to list them.)
#\d t_p4
Table "public.t_p4"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
a | integer | | not null |
src | character varying | | |
Partition of: tpart FOR VALUES FROM (7) TO (9)
Indexes:
"t_p4_pkey" PRIMARY KEY, btree (a)
Notice that ALTER TABLE succeeded and t_p4 was attached to tpart as a partition.
Is this backward compatibility break intentional? I haven't followed
NOT NULL constraint thread closely. I might have missed some
discussion.
--
Best Wishes,
Ashutosh Bapat
