David,
> You should be careful about linking records to PostgreSQL user
> accounts. If the PostgreSQL accounts are removed, there could be
> problems with the foreign key references, if you don't implement it
> carefully.
>
> That said, you could also use the value in pg_user.usesysid as a
> unique ID, rather than the account name (if you want to distinguish
> between different users with the same login name, over a period of
> time where users were removed and re-added).
Perhaps I should just not use a foreign key, and accept that users may
be removed, and do a LEFT JOIN to pg_user and SELECT
COALESCE(pg_use.username, 'user removed'). Since I imagine that
removing users will rarely or never occur in practice, this may be the
thing to do. Then again, perhaps I'm "prematurely pessimising" by not
just doing the simple and obvious thing and using a text column as
described in my original mail. I'd like to do whatever is considered
canonical, but I'm not sure what that is in this case.
Thanks,
Peter Geoghegan