Forum OpenACS Development: Sequence overrun on openacs.org

Collapse
Posted by Gustaf Neumann on
Dear All,

Today we had an error-state on openacs.org from about 10:30 am till 2pm due to a sequence running into its limit. The symptom were entries in error.log like

ERROR:  nextval: reached maximum value of sequence "t_sec_id_seq" (2147483647)
The database of openacs.org was created in 2002 and was upgraded steadily over the last (nearly) 14 years. At that time the servers were mostly 32-bit servers, and as it seems, postgresql was carrying the limit for sequences over until now. The problem can be fixed easily with:
ALTER SEQUENCE t_sec_id_seq NO MAXVALUE;
I will prepare an update script for this.

The usage of sequences depends substantially by the applications and will vary from site to site. In case you have a similar old database, and you ran also into (or close to) an overrun, please let us know such i can include this in an update script. Check the usage of a sequence e.g. with:

SELECT sequence_name, last_value, max_value, 1.0*last_value/max_value as usage FROM t_sec_id_seq;
all the best
-g
Collapse
Posted by Gustaf Neumann on
One consequence of the overrun of the sequence is that the session_id does not fit into an SQL integer type anymore. This can result in cascading changes, when the session_id is used for attributed values stored with type integer in the DB.

One example is the bookmark packages (still at use at OpenACS.org), which uses (negative) session_ids to identify non-registered users. See [1] for a fix of this, which changes the type from integer to bigint.

-gn

PS: http://cvs.openacs.org/changelog/OpenACS?cs=MAIN%3Agustafn%3A20160126084356

Collapse
Posted by Cesareo Garci­a Rodicio on
I had no issue in my setup but to doc here an example of t_sec_id_seq :

SELECT sequence_name, last_value, max_value, 1.0*last_value/max_value as usage FROM t_sec_id_seq;
sequence_name | last_value | max_value | usage
---------------+------------+---------------------+--------------------------------
t_sec_id_seq | 35179901 | 9223372036854775807 | 0.0000000000038142125092024970