Re: Generate user/group sysids from a sequence?

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > OK.  Do we have many people left upgrading from pg_dump's that COPY into
> > pg_shadow?
> 
> Hm, good point.  I had forgotten we ever did that ;-)
> 
> It looks like 7.0.* was the last release where pg_dumpall did that.
> Is that far enough back?
> 
> [ looks further... ]  Actually, such a dump is broken now anyway,
> because the column layout of pg_shadow has changed since 7.0.
> So I think it's a moot point.

Good.

> We could perhaps arrange the code so that if nextval'ing the sequence
> produces a duplicate sysid, we just loop back and nextval again until
> we get a nonconflicting id.  I had hoped to remove the seqscan of
> pg_shadow from CREATE USER; but we could replace it with syscache probes
> for duplicate usename and id, and just repeat the syscache probe if
> we have to do another nextval.  This way, we don't need to bother with
> touching the sequence at all during a CREATE USER with explicit sysid.

Well, the problem is that this could still cause the reuse of a deleted
user, no?  Wasn't that the problem we were originally trying to solve?

The reason I was suggesting the trigger/setval earlier is that it would
eliminate the sequential scan and prevent reuse, I think.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073