Re: serial and partitioned table

On Mon, Nov 13, 2023 at 3:39 PM Peter Eisentraut <peter(at)eisentraut(dot)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