Re: Changes in serial / sequence introduced in Postgresql 10

I guess no one noticed this (for almost a year!) but I keep wondering
whether it would be worth adding a note in the docs about the different
behaviour or perhaps it's a bug that should be addressed.

Should I post it to the bugs list?

Best regards,

Pantelis Theodosiou

On Fri, Oct 6, 2017 at 2:08 PM, Pantelis Theodosiou <ypercube(at)gmail(dot)com>
wrote:

> I noticed that for a column is defined as serial, there are differences in
> the created sequence (type and maximum value) in Postgres 10.
>
> In 9.6, the sequence create would have a maximum value of 2**64-1. In 10,
> it's created with 2**32-1 and I couldn't find this change in the release
> notes or in the docs.
>
>
> -- Postgres 9.6 --
>
> x=# select version() ;
>
> version
> ------------------------------------------------------------
> -----------------------------------------------------
> PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
> 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
> (1 row)
>
> x=# create table test (id serial primary key) ;
> CREATE TABLE
> x=# \d test_id_seq
> Sequence "public.test_id_seq"
> Type | Start | Minimum | Maximum | Increment | Cycles? |
> Cache
> --------+-------+---------+---------------------+-----------
> +---------+-------
> bigint | 1 | 1 | 9223372036854775807 | 1 | no
> | 1
> Owned by: public.test.id
>
>
> -- Postgres 10 --
>
> x=# select version() ;
>
> version
> ------------------------------------------------------------
> ----------------------------------------------------
> PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
> 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
> (1 row)
>
> x=# create table test (id serial primary key) ;
> CREATE TABLE
> x=# \d test_id_seq
> Sequence "public.test_id_seq"
> Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
> ---------+-------+---------+------------+-----------+---------+-------
> integer | 1 | 1 | 2147483647 | 1 | no | 1
> Owned by: public.test.id
>
>
> I suppose it's not a very common use case but I noticed because I had some
> tables that were created with serial columns, then later converted to
> bigint with:
>
> alter table test alter column id type bigint using id::bigint ;
>
> without need to modify the sequence.
>
> In 10, the same operation would modify only the column that later cause an
> error when the maximum value is reached.
>
> The change in behaviour is I guess due to the identity columns feature and
> I think it would be good to be somewhere in the documentation or the
> release notes - assuming that it was intentional.
>
> Pantelis Theodosiou
>