Dear Tom,
>> I was thinking that any integer attribute of any tuple in a shared
>> relation would be ok to store a sequence value.
>
> Certainly not --- when there are multiple versions of the tuple because
> of MVCC rules, which do you use?
I understand you concern, but I think the troubles depends on how the
value is actually used. I agree it would not be a real sequence with
nextval() and so, but the purpose is just to find an non already used
sysid.
The locking mecanism on the update of this special account would insure
that the there is no possible conflict.
<begin>
SELECT usesysid AS old_sysid_value
FROM pg_shadow WHERE usename='next_sysid' FOR UPDATE;
// iterate to find some new sysid starting from previous value
UPDATE usesysid = new_found_sysid WHERE usename='next_sysid';
INSERT new user with old_sysid_value;
<end>
So the concern I would see is more on the fact that there is a lock that
would block concurrent "create user", especially if done in a long
transaction, so IMHO this is performance/contention issue, but there is no
real semantical issue. What is lost is the no-lock nature of the sequence
update with nextval.
I'm not sure the performance would be a big trouble, because create user
are not issued that often, and I would not expect them to appear within a
large transaction.
Anyway I'm planing to hace a look at the real thing first (shared seq).
So no worry, and thanks for your question.
--
Fabien Coelho - coelho@cri.ensmp.fr