PostgreSQL – Get Last Usage of Sequence

The warning

WARNING:  no privileges were granted for "serial"

indicates that the granting role doesn't have the necessary privilege to grant privileges on the object in question.

• Either execute the GRANT command as the role owning the sequence.
• Or do the same as superuser (default superuser is postgres).
• Or GRANT the necessary privilege to the role dev so that this role can in turn grant it to others:

GRANT ALL ON SEQUENCE serial TO dev WITH GRANT OPTION;

The name serial for a sequence isn't particularly wise, but since serial is not a name of a base type and not a reserved word, it wouldn't cause any immediate trouble.

Aside:
Unfortunately the manual uses the name serial in the CREATE SEQUENCE and ALTER SEQUENCE examples. I feel like a suggestion to pgsql-docs@postgresql.org might be in order ...

First, if you don't want duplicates in this column, state that explicitly.

ALTER TABLE dbo.whatever ADD CONSTRAINT uq_that_column UNIQUE (that_column);

(Or you may want to make that the primary key, or change the clustered index, or what have you...)

In any case, raising an error when you generate a duplicate is far better than just blindly inserting a duplicate that you'll just have to deal with later.

Next, consider that a SEQUENCE is just a number generator, and by default it has a cache of 50 values. Depending on how your transactions are set up, and what other critical events happen on a server, it is possible that SQL Server can "forget" that it generated certain values for you. Sorry but I do not know exactly what criteria factor into reproducing this bug. The way to get around this (until the bug is solved/explained) is to change the sequence to use NO CYCLE and NO CACHE, e.g.:

ALTER SEQUENCE dbo.mysequence NO CYCLE NO CACHE; 

Note that NO CACHE can affect performance and concurrency, but will help eliminate gaps, lost blocks and, who knows, maybe your problem too.

You might also want to verify that you're on the most recent service pack and CU. At this point I recommend SP1 and CU10 with 3437; SP2 is out but there is still a critical issue there with online rebuilds that may affect you.