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

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