Enforcing serial uniqueness?

From: Steven Brown <swbrown(at)ucsd(dot)edu>
To: pgsql-general(at)postgresql(dot)org
Subject: Enforcing serial uniqueness?
Date: 2006-03-22 07:41:11
Message-ID: dvqv2b$3e0$1@sea.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I want to allow access to a table's rows without allowing that table to
be damaged. A problem I have is with my serial primary key 'id' field.
Although I can block its UPDATE, if users INSERT with an explicit 'id'
higher than the sequence, future INSERTs will fail due to the values
colliding. Ditto if users modify the sequence (setval) to be lower than
existing 'id' fields. This is rather bad, as it prevents
anyone/anything from INSERTing until fixed by manual intervention.

This is probably a common question - how do I fix the above problems?

I'd be happy with them not being able to explicitly set 'id' at all as
I'd rather they also not be able to cause the reuse of a deleted id, but
the 'select nextval, insert, use id' paradigm seems to require it.
Also, if I could block 'setval', that would seem to prevent the latter
problem, but it doesn't seem possible to block 'setval' while still
allowing 'nextval'.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrus 2006-03-22 07:56:57 Re: How to release locks
Previous Message Qingqing Zhou 2006-03-22 07:25:15 Re: invalid page header