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.
