Thread: (2^63 - 1)::bigint => out of range? (because of the double precision)

(2^63 - 1)::bigint => out of range? (because of the double precision)

From
Alexey Dokuchaev
Date:
Hi there,

I've decided to run some tests to see how my tables' ids would survive
when their yielding sequences would start hitting their MAXVALUE's, by
doing some "SELECT setval('foo_id_seq', ~maxbigint)".  As I don't like
to hardcode numbers (esp. huge numbers, because sequences are always[*]
bigint's), I've tried to use (2^63 - 1)::bigint as "maxbigint", to no
avail, in contrast to (2^31 - 1)::int (-> below is short mnemonic for
"returns"):

  select (2^31 - 1)::int        -> 2147483647 (correct)

  select (2^63 - 1)::bigint        -> bigint out of range (???)
  select (9223372036854775807)::bigint    -> 9223372036854775807 (correct)

Apparently, this is because the type of 2^63 is double precision, which
is inexact; if I explicitly cast any of 2 or 63 to ::numeric, it behaves
as expected:

  select (2::numeric^63 - 1)::bigint    -> 9223372036854775807 (ok)
  select (2^63::numeric - 1)::bigint    -> 9223372036854775807 (ditto)

What is the rationale for (int ^ int) to return double precision rather
than numeric?  I am missing something obvious here?

./danfe

P.S.  On a tangentally related note, why is "NO CYCLE" is the default
for sequences?

[*] Per documentation, "The [SQL] standard's AS <data type> expression
is not supported."  Another "why is it so?" question, btw. ;-)


Re: (2^63 - 1)::bigint => out of range? (because of the doubleprecision)

From
Adrian Klaver
Date:
On 06/08/2018 10:23 AM, Alexey Dokuchaev wrote:
> Hi there,
> 
> I've decided to run some tests to see how my tables' ids would survive
> when their yielding sequences would start hitting their MAXVALUE's, by
> doing some "SELECT setval('foo_id_seq', ~maxbigint)".  As I don't like
> to hardcode numbers (esp. huge numbers, because sequences are always[*]
> bigint's), I've tried to use (2^63 - 1)::bigint as "maxbigint", to no

Not always, bigints are just the default. All those cases where folks 
use the serial 'type' are getting an int sequence:

create table serial_test2(id serial);

\d serial_test2
                             Table "public.serial_test2" 
 

  Column |  Type   | Collation | Nullable |                 Default 
 

--------+---------+-----------+----------+------------------------------------------
  id     | integer |           | not null | 
nextval('serial_test2_id_seq'::regclass)

\d+ serial_test2_id_seq
                 Sequence "public.serial_test2_id_seq"
   Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
  integer |     1 |       1 | 2147483647 |         1 | no      |     1
Owned by: public.serial_test2.id


> avail, in contrast to (2^31 - 1)::int (-> below is short mnemonic for
> "returns"):
> 
>    select (2^31 - 1)::int        -> 2147483647 (correct)
> 
>    select (2^63 - 1)::bigint        -> bigint out of range (???)
>    select (9223372036854775807)::bigint    -> 9223372036854775807 (correct)
> 
> Apparently, this is because the type of 2^63 is double precision, which
> is inexact; if I explicitly cast any of 2 or 63 to ::numeric, it behaves
> as expected:
> 
>    select (2::numeric^63 - 1)::bigint    -> 9223372036854775807 (ok)
>    select (2^63::numeric - 1)::bigint    -> 9223372036854775807 (ditto)
> 
> What is the rationale for (int ^ int) to return double precision rather
> than numeric?  I am missing something obvious here?

Not sure, someone else will have to explain.
> 
> ./danfe
> 
> P.S.  On a tangentally related note, why is "NO CYCLE" is the default
> for sequences?

My guess is because sequences are often used to provide numbers for a 
PRIMARY KEY and NO CYCLE is a heads up for key duplication before the PK 
code kicks in.

> 
> [*] Per documentation, "The [SQL] standard's AS <data type> expression
> is not supported."  Another "why is it so?" question, btw. ;-)
> 

Where in the docs are you seeing this?


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: (2^63 - 1)::bigint => out of range? (because of the doubleprecision)

From
Adrian Klaver
Date:
On 06/08/2018 10:23 AM, Alexey Dokuchaev wrote:
> Hi there,
> 

> P.S.  On a tangentally related note, why is "NO CYCLE" is the default
> for sequences?
> 
> [*] Per documentation, "The [SQL] standard's AS <data type> expression
> is not supported."  Another "why is it so?" question, btw. ;-)
> 

I found it. Its in the docs for 9.6-. That is not the case anymore in 10+:

https://www.postgresql.org/docs/10/static/sql-createsequence.html
"data_type

     The optional clause AS data_type specifies the data type of the 
sequence. Valid types are smallint, integer, and bigint. bigint is the 
default. The data type determines the default minimum and maximum values 
of the sequence.
"


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Alexey Dokuchaev <danfe@nsu.ru> writes:
> What is the rationale for (int ^ int) to return double precision rather
> than numeric?  I am missing something obvious here?

There are two ^ operators, one taking float8 and one taking numeric.
Since float8 is the preferred datatype in the numeric category
(i.e. the top of the implicit-casting hierarchy), the float8 operator
will be chosen unless one of the earlier disambiguation rules applies:

https://www.postgresql.org/docs/current/static/typeconv-oper.html

In this case, you need at least one input to be numeric, so that
rule 3c fires before rule 3d can.

You might argue that numeric should be the preferred type, but that
falls foul of the SQL standard, which is quite clear that only
numeric -> float8 can be an implicit cast, not the other direction.
(They word it in terms of casts between exact and approximate
numeric types, but that's the outcome.)  The type resolution
heuristics break down pretty badly if the preferred type in a
category doesn't have implicit casts from every other type in the
category.

BTW, you could also just write 2.0^63 to get numeric.

> P.S.  On a tangentally related note, why is "NO CYCLE" is the default
> for sequences?

(a) the SQL standard says so, (b) most people tend to expect serial
columns to not repeat values.

> [*] Per documentation, "The [SQL] standard's AS <data type> expression
> is not supported."  Another "why is it so?" question, btw. ;-)

We didn't get around to implementing that till v10.

            regards, tom lane


Re: (2^63 - 1)::bigint => out of range? (because of the double precision)

From
Alexey Dokuchaev
Date:
On Fri, Jun 08, 2018 at 10:30:45AM -0700, Adrian Klaver wrote:
> On 06/08/2018 10:23 AM, Alexey Dokuchaev wrote:
> >I've decided to run some tests to see how my tables' ids would survive
> >when their yielding sequences would start hitting their MAXVALUE's, by
> >doing some "SELECT setval('foo_id_seq', ~maxbigint)".  As I don't like
> >to hardcode numbers (esp. huge numbers, because sequences are always[*]
> >bigint's)
> 
> Not always, bigints are just the default. All those cases where folks
> use the serial 'type' are getting an int sequence:
> 
> \d+ serial_test2_id_seq
>                 Sequence "public.serial_test2_id_seq"
>   Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache
> ---------+-------+---------+------------+-----------+---------+-------
>  integer |     1 |       1 | 2147483647 |         1 | no      |     1

Apparently this is only in 10+, while I'm kind of confined to 9.x ATM.

> >What is the rationale for (int ^ int) to return double precision rather
> >than numeric?  I am missing something obvious here?
> 
> Not sure, someone else will have to explain.

Tom already did, thanks Tom!

> >On a tangentally related note, why is "NO CYCLE" is the default
> >for sequences?
> 
> My guess is because sequences are often used to provide numbers for a
> PRIMARY KEY and NO CYCLE is a heads up for key duplication before the
> PK code kicks in.

OK, but what about highly volatile tables for come-and-go type of things?
Think of a session pool, or task queue.  I want to use NO CYCLE for this
kind of tables as it would allow me to never worry about hitting "nextval:
reached maximum value of sequence" error, recycle ids (because they come
and go), and still be safe because PK constraint protects me.  Any flaws
in this vision of mine?

./danfe


Re: (2^63 - 1)::bigint => out of range? (because of the doubleprecision)

From
Adrian Klaver
Date:
On 06/09/2018 05:24 AM, Alexey Dokuchaev wrote:
> On Fri, Jun 08, 2018 at 10:30:45AM -0700, Adrian Klaver wrote:

>>
>> My guess is because sequences are often used to provide numbers for a
>> PRIMARY KEY and NO CYCLE is a heads up for key duplication before the
>> PK code kicks in.
> 
> OK, but what about highly volatile tables for come-and-go type of things?
> Think of a session pool, or task queue.  I want to use NO CYCLE for this
> kind of tables as it would allow me to never worry about hitting "nextval:
> reached maximum value of sequence" error, recycle ids (because they come
> and go), and still be safe because PK constraint protects me.  Any flaws
> in this vision of mine?

Assuming you meant CYCLE not NO CYCLE, I see no issue. If you do use a 
sequence with NO CYCLE you can use ALTER SEQUENCE some_seq RESTART to 
reset it:

https://www.postgresql.org/docs/10/static/sql-altersequence.html

> 
> ./danfe
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: (2^63 - 1)::bigint => out of range? (because of the double precision)

From
Alexey Dokuchaev
Date:
On Sat, Jun 09, 2018 at 07:20:17AM -0700, Adrian Klaver wrote:
> On 06/09/2018 05:24 AM, Alexey Dokuchaev wrote:
> >OK, but what about highly volatile tables for come-and-go type of things?
> >Think of a session pool, or task queue.  I want to use NO CYCLE for this
> >kind of tables as it would allow me to never worry about hitting "nextval:
> >reached maximum value of sequence" error, recycle ids (because they come
> >and go), and still be safe because PK constraint protects me.  Any flaws
> >in this vision of mine?
> 
> Assuming you meant CYCLE not NO CYCLE, I see no issue.

Oh, mea culpa, I meant CYCLE of course (in the quoted paragraph above).

> If you do use a sequence with NO CYCLE you can use ALTER SEQUENCE some_seq
> RESTART to reset it:
> https://www.postgresql.org/docs/10/static/sql-altersequence.html

I understand that I can reset it; the idea was to minimize the table and
sequence maintenance while allowing it to work, well, forever.  Hence the
CYCLE idea.  Anyway, I've heard you, thanks Adrian.

./danfe