Re: serial and partitioned table - Mailing list pgsql-hackers

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.