Re: Foreign Key issue - pg_shadow

"Rod Taylor" <rod(dot)taylor(at)inquent(dot)com> writes:
> I suppose the below reference isn't intended to be done. But, I'd
> like to extend the pg_shadow table to store information about the
> database users and some of their actions (a history of changes).
> Is it safe to add a UNIQUE constraint to the usesysid or usename
> columns?

You can't do either of those things, at least not without modifying
code in the backend. The code that manipulates pg_shadow entries knows
exactly what indexes exist on the table, so you can't just go and create
more. Also, that code will not fire triggers, so even if the correct
unique index existed, you would not get correct referential integrity
behavior.

However, it does seem like an oversight that we don't use unique indexes
to enforce uniqueness of usename and usesysid. Too late to fix it for
7.1 (unless we want to force another initdb), but it ought to get fixed.
Bruce, a TODO item please?

regards, tom lane