Hi All,
#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
To appease the gods of surprises I need to add a NOT NULL constraint. See [1].
#alter table t_p4 alter column a set not null;
ALTER TABLE
#alter table tpart attach partition t_p4 for values from (7) to (9);
ALTER TABLE
#\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)
The partition was attached but the gods of surprises forgot to set the
default value for a, which gets set when we create a partition
directly.
#create table t_p3 partition of tpart for values from (5) to (7);
CREATE TABLE
#\d t_p3
Table "public.t_p3"
Column | Type | Collation | Nullable |
Default
--------+-------------------+-----------+----------+----------------------------------
a | integer | | not null |
nextval('tpart_a_seq'::regclass)
src | character varying | | |
Partition of: tpart FOR VALUES FROM (5) TO (7)
Indexes:
"t_p3_pkey" PRIMARY KEY, btree (a)
Gods of surprises have another similar gift.
#create table t_p2(a serial primary key, src varchar);
CREATE TABLE
#alter table tpart attach partition t_p2 for values from (3) to (5);
ALTER TABLE
#\d t_p2
Table "public.t_p2"
Column | Type | Collation | Nullable |
Default
--------+-------------------+-----------+----------+---------------------------------
a | integer | | not null |
nextval('t_p2_a_seq'::regclass)
src | character varying | | |
Partition of: tpart FOR VALUES FROM (3) TO (5)
Indexes:
"t_p2_pkey" PRIMARY KEY, btree (a)
Observe that t_p2 uses a different sequence, not the sequence used by
the parttiioned table tpart.
I think this behaviour is an unexpected result of using inheritance
for partitioning. Also partitions not getting default values from the
partitioned table may be fine except in the case of serial columns.
Unlike inheritance hierarchy, a partitioned table is expected to be a
single table. Thus a serial column is expected to have monotonically
increasing values across the partitions. So partitions should use the
same sequence as the parent table. If the new partition being attached
uses a different sequence than the partitioned table, we should
prohibit it from being attached.
This raises the question of what should be the behaviour on detach
partitions. I haven't studied the behaviour of inherited properties.
But it looks like the partition being detached should let go of the
inherited properties and keep the non-inherited one (even those which
were retained after merging).
I found this behaviour when experimenting with serial columns when
reading [2]. The result of this discussion will have some impact on
how we deal with IDENTITY columns in partitioned tables.
[1]
https://www.postgresql.org/message-id/CAExHW5uRUtDfU0R8zXofQxCV3S1B%2BPa%2BX%2BNrpMwzKraLc25%3DEg%40mail.gmail.com
[2]
https://www.postgresql.org/message-id/flat/70be435b-05db-06f2-7c01-9bb8ee2fccce%40enterprisedb.com
--
Best Wishes,
Ashutosh Bapat
