Postgres Pro
Downloads
Home   >   mailing lists

Thread: Foreign Key issue - pg_shadow

Foreign Key issue - pg_shadow

From
"Rod Taylor"
Date:
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.

Re: Foreign Key issue - pg_shadow

From
Tom Lane
Date:
"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

Re: Foreign Key issue - pg_shadow

From
Bruce Momjian
Date:
> "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