Thread: serial and partitioned table
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
On 17.10.23 09:25, Ashutosh Bapat wrote: > #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) Partitions can have default values different from the parent table. So it would not be correct for the attach operation to just overwrite the defaults on the table being attached. One might think, it should only adjust the default if no default was explicitly specified. But we don't have a way to tell apart "no default" from "null default was actually intended". So, while I agree that there is some potential for confusion here, I think this might be intentional behavior. Or at least there is no better possible behavior. > > 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 is also correct if you consider the definition of serial as a macro that creates a sequence. Of course, the behavior is silly, which is why we are plotting to get rid of the current definition of serial.
On Mon, Nov 13, 2023 at 3:39 PM Peter Eisentraut <peter@eisentraut.org> wrote: > > On 17.10.23 09:25, Ashutosh Bapat wrote: > > #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) > > Partitions can have default values different from the parent table. So > it would not be correct for the attach operation to just overwrite the > defaults on the table being attached. One might think, it should only > adjust the default if no default was explicitly specified. But we don't > have a way to tell apart "no default" from "null default was actually > intended". > > So, while I agree that there is some potential for confusion here, I > think this might be intentional behavior. Or at least there is no > better possible behavior. Ok. > > > > > 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 is also correct if you consider the definition of serial as > a macro that creates a sequence. Of course, the behavior is silly, > which is why we are plotting to get rid of the current definition of serial. Ok. If we implement the identity behaviour as per the discussion in partitioning and identity thread [1], behaviour of serial column will be different from the identity column. The behaviour of the identity column would be saner, of course. When and if we redirect the serial to identity there will be some surprises because of these differences. I think, this is moving things in a better direction. We just need to acknowledge and agree on this. [1] https://www.postgresql.org/message-id/flat/8801cade-20d2-4c9c-a583-b3754beb9be3%40eisentraut.org#9ce279be53b86dd9ab5fce027c94687d -- Best Wishes, Ashutosh Bapat