From: Bruce Momjian <bruce@momjian.us>
To: Pantelis Theodosiou <ypercube@gmail.com>
Cc: pgsql-docs@postgresql.org
Subject: Re: Changes in serial / sequence introduced in Postgresql 10
Date: Tue, 19 Jun 2018 14:49:08 -0400
Message-ID: <20180619184908.GF3637@momjian.us> (raw)
In-Reply-To: <CAE3TBxzpRJFF1QhKiEy6ryvgpQwituGbUDqSzi00w-WU7FtRfA@mail.gmail.com>
References: <CAE3TBxz4BkhcvYwCxtRpQyqu5NVjMAxgun5vhasM5C+kDcuCRA@mail.gmail.com>
<CAE3TBxzpRJFF1QhKiEy6ryvgpQwituGbUDqSzi00w-WU7FtRfA@mail.gmail.com>
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 +
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: bruce@momjian.us, ypercube@gmail.com
Subject: Re: Changes in serial / sequence introduced in Postgresql 10
In-Reply-To: <20180619184908.GF3637@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox