> "Rod Taylor" <rod.taylor@inquent.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?
Added to TODO:
* Add unique indexes to pg_shadow.usename and pg_shadow.usesysid
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026