PostgreSQL – Behavior at End of Sequence

postgresqlsequence

As per subject when a sequence hits its max val what happens?

What is the behavior? I have a big table with a sequence as a primary key and would like to know.

Thanks.

Best Answer

If you try it, you'll get:

ERROR: nextval: reached maximum value of sequence "testseq" (9223372036854775807): select nextval('testseq')

This is the case when the sequence is created automatically for a serial or bigserial column. You can create a sequence with specifying CYCLE, in which case it will restart from the MINVALUE specified (or left at the default 1).

Related Solutions

Postgresql – When postgresql sequences get out of sync

There are a few possible cases where things can get out of sync.

Very old versions (unsupported) used to sometimes fail to set sequences on backup restore. If you have manual backup and restore routines, this is somewhere to look.
setval('sequence_name', 1) will set to to 1.

Those are your only two possibilities unless you have a short cycle, and are cycling.

PostgreSQL 9.3 – Permission for Sequence in Another Schema

This can be done.
The column default for your serial primary key is typically defined as:

ALTER TABLE schema_a.tbl ALTER COLUMN tbl_id
SET DEFAULT nextval('schema_a.tbl_tbl_id_seq'::regclass);

Two options:

1. Move sequence (my preference)

to the public schema - or any schema with sufficient privileges:

GRANT USAGE ON SCHEMA public TO public; -- or: my_group

Moving the sequence to another schema is easy:

ALTER SEQUENCE schema_a.tbl_tbl_id_seq SET SCHEMA public;

Now you can grant USAGE:

GRANT USAGE ON SEQUENCE public.tbl_tbl_id_seq TO public; -- or: my_group

That preserves all references (incl. column defaults).

2. Function wrapper with `SECURITY DEFINER`

If you cannot move the sequence for some reason (can't think of one), you can alternatively wrap access to it in functions of your own with SECURITY DEFINER. Again, create those functions in the public schema (or any schema with sufficient privileges for your users):

CREATE OR REPLACE FUNCTION public.next_tbl_tbl_id_seq()
  RETURNS bigint AS
$func$
SELECT nextval('schema_a.tbl_tbl_id_seq'::regclass)
$func$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = schema_a, pg_temp;
ALTER FUNCTION shop.f_deswap_name(text) OWNER TO owning_role;

Where owning_role is the owner of the sequence or any role with sufficient privileges. Similar function for currval() ...

Be sure to read the chapter "Writing SECURITY DEFINER Functions Safely" in the manual.