Re: Enforcing serial uniqueness?

Csaba Nagy wrote:
>> That way they really can't touch the sequence; otherwise they still
>> could call nextval multiple times erroneously (people do that...). It
>> doesn't matter much to the sequence, of course... It just leaves the
>> ugly gaps out :P
>
> The sequence facility was NOT designed with no-gap sequencing in mind,
> but with good parallel performance in mind.

Gaps are fine. All I want is safe uniqueness. What is an issue for me
is a user having INSERT permission being able to shut down all INSERTs
from everyone else until someone manually figures out what happened and
fixes it, ditto for UPDATE permission on a sequence (which they need in
order to use nextval so they know what id the row they inserted will
have, right?), which seems extremely dangerous to me.

For example, forcing a value above the sequence position:

CREATE TABLE foo(id SERIAL PRIMARY KEY);
-- Forcing a value above the sequence position,
INSERT INTO foo(id) VALUES(1);
-- Causes future INSERT failures for everyone:
INSERT INTO foo DEFAULT VALUES;

If cache=1, possibly using a trigger on id to check that the next value
of the sequence will be greater than it would solve this if there's not
some reason that's unsafe/unworkable - e.g., is the sequence's position
guaranteed to have been updated before a BEFORE trigger (needed if
nextval is the default as in serial columns), and will the default taken
be available to a BEFORE?

And the other example:

CREATE TABLE foo(id SERIAL PRIMARY KEY);
INSERT INTO foo DEFAULT VALUES;
-- User with UPDATE for foo_id_seq can call setval as well as nextval,
SELECT setval('foo_id_seq', 1, false);
-- Causing future INSERT failures for everyone:
INSERT INTO foo DEFAULT VALUES;

I'm not sure how to solve this given UPDATE permission on sequences is
for both nextval and setval. If I could block/restrict setval somehow
that would fix this.