Re: Generate user/group sysids from a sequence?

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073