Re: [HACKERS] sequence data type - Mailing list pgsql-hackers

From Daniel Verite
Subject Re: [HACKERS] sequence data type
Date
Msg-id aa1d20e8-afcb-4d19-a69e-8268c72ea2ea@manitou-mail.org
Whole thread Raw
In response to sequence data type  (Peter Eisentraut <peter.eisentraut@2ndquadrant.com>)
List pgsql-hackers
    Peter Eisentraut wrote:

> So in order to correctly answer the question, is the sequence about to
> run out, you need to look not only at
> the sequence but also any columns it is associated with.  check_postgres
> figures this out, but it's complicated and slow, and not easy to do
> manually.

It strikes me that this is a compelling argument for setting a sensible
MAXVALUE when creating a sequence for a SERIAL, rather than binding
the sequence to a datatype.

In existing releases the SERIAL code sets the maxvalue to 2^63 even
though it knows that the column is limited to 2^31.
It looks like setting it to 2^31 would be enough for the sake of
monitoring.

More generally, what is of interest for the monitoring is how close
the sequence's last_value is from its max_value, independently of an
underlying type.

2^{15,31,63} are specific cases of particular interest, but there's
no reason to only check for these limits when you can do it
for every possible limit.

For instance if a user has a business need to limit an ID to 1 billion,
they should alter the sequence to a MAXVALUE of 1 billion, and be
interested in how close they are from that, not from 2^31.


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite



pgsql-hackers by date:

Previous
From: Dilip Kumar
Date:
Subject: Re: [HACKERS] New SQL counter statistics view (pg_stat_sql)
Next
From: Alvaro Herrera
Date:
Subject: Re: [HACKERS] Adding type info etc for inheritance errmsg: "childtable is missing column ..."