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'.
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 |