Re: Changes in serial / sequence introduced in Postgresql 10

On Sat, May 19, 2018 at 12:05:26PM +0100, Pantelis Theodosiou wrote:
> 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?

I don't think we realize there was a behavioral change here. I think we
were just trying to fix the case where the sequence maximum didn't match
the serial maximum. I am not sure if it is worth documenting it at this
point though.

---------------------------------------------------------------------------

>
> 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
>
>

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +