Обсуждение: 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