Foreign Key issue - pg_shadow
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?
temp=# \d pg_shadow
Table "pg_shadow"
Attribute | Type | Modifier
-------------+---------+----------
usename | name |
usesysid | integer |
usecreatedb | boolean |
usetrace | boolean |
usesuper | boolean |
usecatupd | boolean |
passwd | text |
valuntil | abstime |
temp=#
temp=# CREATE TABLE users (
temp(# pg_username name NOT NULL
temp(# REFERENCES pg_shadow(usesysid)
temp(# ON UPDATE CASCADE
temp(# ON DELETE RESTRICT
temp(# );
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
ERROR: UNIQUE constraint matching given keys for referenced table
"pg_shadow" not found
temp=#
--
Rod Taylor
There are always four sides to every story: your side, their side, the
truth, and what really happened.
"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?
regards, tom lane
"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, Pennsylvania 19026