Re: pg_stat_database shows userid as OID

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Tom Lane wrote:
>> I'd recommend not making any piecemeal changes, especially not when
>> there's not yet a consensus which way to converge.

> Well, seems we should make it consistent at least.

I think the original argument stemmed from the idea that we ought to use
pg_shadow's OID column as the user identifier (eliminating usesysid per
se). This seems like a good idea at first but I think it has a couple
of fatal problems:
* disappearance of pg_shadow.usesysid column will doubtless break some
applications
* if we use OID then it's much more difficult to support explicit
assignment of userid

> I think some wanted it to be an int so they could use the
> same unix uid for pg_shadow, but I think we aren't using that idea much
> anymore.

I don't think anyone worries about making usesysid match /etc/passwd
anymore, but nonetheless CREATE USER WITH SYSID is still an essential
capability. What if you drop a user accidentally while he still owns
objects? You *must* be able to recreate him with the same sysid as
before. pg_depend cannot save us from this kind of mistake, either,
since users span databases.

So it seems to me that we must keep pg_shadow.usesysid as a separate
column and not try to make it the OID of pg_shadow.

Given that decision, the argument for making it be type OID seems very
weak, so I'd lean to the "use int4" camp myself. But I'm not sure
everyone agrees. I think Peter was strongly in favor of OID when he
was revising the session-authorization code (that's why it all uses OID
for user IDs...)

As far as the actual C code goes, I'd lean to creating new typedefs
UserId and GroupId (or some such names) and making all the routine
and variable declarations use those, and not either OID or int4.
But I'm not excited about promoting these typedefs into actual SQL
types, as was done for TransactionId and CommandId; the payback seems
much less than the effort needed.

regards, tom lane