Обсуждение: 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.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. 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.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. > > 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.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. > > 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.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. >> 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? > > http://www.postgresql.org/docs/faq -- David Gardner