pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedChanges in serial / sequence introduced in Postgresql 10
3+ messages / 3 participants
[nested] [flat]
* Changes in serial / sequence introduced in Postgresql 10
@ 2017-10-06 13:08 Pantelis Theodosiou <ypercube@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Pantelis Theodosiou @ 2017-10-06 13:08 UTC (permalink / raw)
To: pgsql-docs
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Changes in serial / sequence introduced in Postgresql 10
@ 2018-06-19 18:49 Bruce Momjian <bruce@momjian.us>
parent: Pantelis Theodosiou <ypercube@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Bruce Momjian @ 2018-06-19 18:49 UTC (permalink / raw)
To: Pantelis Theodosiou <ypercube@gmail.com>; +Cc: pgsql-docs
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 +
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Changes in serial / sequence introduced in Postgresql 10
@ 2018-06-20 02:05 Michael Paquier <michael@paquier.xyz>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 3+ messages in thread
From: Michael Paquier @ 2018-06-20 02:05 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Pantelis Theodosiou <ypercube@gmail.com>; pgsql-docs
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
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2018-06-20 02:05 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-10-06 13:08 Changes in serial / sequence introduced in Postgresql 10 Pantelis Theodosiou <ypercube@gmail.com>
2018-06-19 18:49 ` Bruce Momjian <bruce@momjian.us>
2018-06-20 02:05 ` Michael Paquier <michael@paquier.xyz>
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