Обсуждение: (2^63 - 1)::bigint => out of range? (because of the double precision)
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. ;-)
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
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
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
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
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