proposed FAQ entry
Earlier today I was tempted to start storing usesysid's as foreign keys
in one of my tables by doing:
SELECT usesysid FROM pg_user WHERE pg_user.usename= user;
My proposed FAQ entry is based on this mailing list post which convinced
me not to:
http://archives.postgresql.org/pgsql-novice/2005-04/msg00328.php
So the Question would be:
Q) How do I keep track of edits to a table? OR Can I use store usesysid
in my table to identify users?
A) No because usesysid values are not guaranteed to remain the same
after a restore, or upgrade. Instead store the user name as text, if
needed create your own user table and associate the user names with
integer keys.
David Gardner wrote:
Earlier today I was tempted to start storing usesysid's as foreign keys
in one of my tables by doing:
SELECT usesysid FROM pg_user WHERE pg_user.usename= user;My proposed FAQ entry is based on this mailing list post which convinced
me not to:
http://archives.postgresql.org/pgsql-novice/2005-04/msg00328.phpSo the Question would be:
Q) How do I keep track of edits to a table? OR Can I use store usesysid
in my table to identify users?A) No because usesysid values are not guaranteed to remain the same
after a restore, or upgrade. Instead store the user name as text, if
needed create your own user table and associate the user names with
integer keys.
Uh, sorry, this is not a _frequent_ question/issue.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote:
David Gardner wrote:
Earlier today I was tempted to start storing usesysid's as foreign keys
in one of my tables by doing:
SELECT usesysid FROM pg_user WHERE pg_user.usename= user;My proposed FAQ entry is based on this mailing list post which convinced
me not to:
http://archives.postgresql.org/pgsql-novice/2005-04/msg00328.phpSo the Question would be:
Q) How do I keep track of edits to a table? OR Can I use store usesysid
in my table to identify users?A) No because usesysid values are not guaranteed to remain the same
after a restore, or upgrade. Instead store the user name as text, if
needed create your own user table and associate the user names with
integer keys.Uh, sorry, this is not a _frequent_ question/issue.
Hmm, I do see it from time to time in pgsql-es-ayuda. Maybe not as
frequently as some other questions that are also missing from the FAQ
(like a thorough explanation of encoding issues), but I digress.
--
Alvaro Herrera http://www.PlanetPostgreSQL.org/
"Ni aun el genio muy grande llegar�a muy lejos
si tuviera que sacarlo todo de su propio interior" (Goethe)
On Thu, Jun 28, 2007 at 09:06:20PM -0400, Bruce Momjian wrote:
David Gardner wrote:
Earlier today I was tempted to start storing usesysid's as foreign keys
in one of my tables by doing:
SELECT usesysid FROM pg_user WHERE pg_user.usename= user;My proposed FAQ entry is based on this mailing list post which convinced
me not to:
http://archives.postgresql.org/pgsql-novice/2005-04/msg00328.phpSo the Question would be:
Q) How do I keep track of edits to a table? OR Can I use store usesysid
in my table to identify users?A) No because usesysid values are not guaranteed to remain the same
after a restore, or upgrade. Instead store the user name as text, if
needed create your own user table and associate the user names with
integer keys.Uh, sorry, this is not a _frequent_ question/issue.
If it's not frequent, perhaps it should at least go in the documentatino
somewhere?
(I confess I haven't checked if it's actually there already)
//Magnus
Well the motivation for the question, is to want to track edits to the
database. To know who changed what/when, and possibly run a report
against that information. I looked through the docs to find the user and
session_user variables at:
http://www.postgresql.org/docs/8.1/interactive/functions-info.html
It just seemed natural to think there must be some kind of integer key
associated with the user. Maybe this doesn't qualify as frequent.
Magnus Hagander wrote:
On Thu, Jun 28, 2007 at 09:06:20PM -0400, Bruce Momjian wrote:
David Gardner wrote:
Earlier today I was tempted to start storing usesysid's as foreign keys
in one of my tables by doing:
SELECT usesysid FROM pg_user WHERE pg_user.usename= user;My proposed FAQ entry is based on this mailing list post which convinced
me not to:
http://archives.postgresql.org/pgsql-novice/2005-04/msg00328.phpSo the Question would be:
Q) How do I keep track of edits to a table? OR Can I use store usesysid
in my table to identify users?A) No because usesysid values are not guaranteed to remain the same
after a restore, or upgrade. Instead store the user name as text, if
needed create your own user table and associate the user names with
integer keys.Uh, sorry, this is not a _frequent_ question/issue.
If it's not frequent, perhaps it should at least go in the documentatino
somewhere?(I confess I haven't checked if it's actually there already)
//Magnus
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
--
David Gardner