Thread: [DOCS] 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@gmail.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@momjian.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 +

On Tue, Jun 19, 2018 at 02:49:08PM -0400, Bruce Momjian wrote:
> 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.

Yeah, I agree that it is not worth documenting it.  I don't recall
reviewing the full patch related to identity columns, but I surely
looked at patches which fixed post-commit bugs, and the new behavior is
as a whole more consistent as sequences created with serial map to the
real bound values associated with the underlying column type, and
bigserial does the same:
=# create table test (id bigserial primary key) ;
CREATE TABLE
=# select sequencename, max_value from pg_sequences;
 sequencename |      max_value
--------------+---------------------
 test_id_seq  | 9223372036854775807
(1 row)
--
Michael