Thread: history table
Hi,
I want to save history for a few tables using triggers on update and
creation. What's the best approach to do this in a webapp environment
where I want to save which webapp user that is doing the change, not
the postgresql user?
--
regards,
Robin
am Tue, dem 21.08.2007, um 20:20:38 +0200 mailte Robin Helgelin folgendes: > Hi, > > I want to save history for a few tables using triggers on update and > creation. What's the best approach to do this in a webapp environment > where I want to save which webapp user that is doing the change, not > the postgresql user? Maybe tablelog. 20:49 < akretschmer> ??tablelog 20:49 < rtfm_please> For information about tablelog 20:49 < rtfm_please> see http://ads.wars-nicht.de/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html 20:49 < rtfm_please> or http://pgfoundry.org/projects/tablelog/ Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Robin Helgelin wrote: > Hi, > > I want to save history for a few tables using triggers on update and > creation. What's the best approach to do this in a webapp environment > where I want to save which webapp user that is doing the change, not > the postgresql user? > Well, you haven't told us much about your webapp. Are you using connection pooling? If so, then you'll need to provide the webapp userid as an additional parameter to your database updates. If you are not using connection pooling, such that your webapp userids are connecting as themselves, then the problem becomes much easier; you've got the correct userid to log by just looking at the connection details. -- Guy Rouillier
On 8/21/07, Guy Rouillier <guyr-ml1@burntmail.com> wrote:
> Well, you haven't told us much about your webapp. Are you using
> connection pooling? If so, then you'll need to provide the webapp
> userid as an additional parameter to your database updates. If you are
> not using connection pooling, such that your webapp userids are
> connecting as themselves, then the problem becomes much easier; you've
> got the correct userid to log by just looking at the connection details.
Yes, this is where I'm too new to postgresql, how do I tell the
database which user is logged in to the webapp? A session parameter?
There will be connection pooling, but if I know how to solve the
previous question I don't think it's hard to get it working with the
pool.
--
regards,
Robin
On Tuesday 21 August 2007 1:22 pm, Robin Helgelin wrote: > > Yes, this is where I'm too new to postgresql, how do I tell > the database which user is logged in to the webapp? A session > parameter? There will be connection pooling, but if I know how > to solve the previous question I don't think it's hard to get > it working with the pool. Tablelog looks pretty cool. One way to handle your user ID issue would be to initiate a user session by storing a session record (for example: id, username, starttime), then have your app pass that session ID to your updates for history. Then you could store the user ID in an update_session_id column and tablelog would help track of the history. Ed
On Tuesday 21 August 2007 1:42 pm, Ed L. wrote: > Then you could > store the user ID in an update_session_id column and tablelog > would help track of the history. s/user ID/session ID/g; Ed
Robin Helgelin wrote: > On 8/21/07, Guy Rouillier <guyr-ml1@burntmail.com> wrote: >> Well, you haven't told us much about your webapp. Are you using >> connection pooling? If so, then you'll need to provide the webapp >> userid as an additional parameter to your database updates. If you are >> not using connection pooling, such that your webapp userids are >> connecting as themselves, then the problem becomes much easier; you've >> got the correct userid to log by just looking at the connection details. > > Yes, this is where I'm too new to postgresql, how do I tell the > database which user is logged in to the webapp? A session parameter? > There will be connection pooling, but if I know how to solve the > previous question I don't think it's hard to get it working with the > pool. Well, I can't find a way to set a variable associated with a connection, so probably the easiest thing to do is to add an "updated_by" column to your regular table (i.e., the non-history version.) Then just include the userid from your webapp as the value for that column. Your history table can then be updated by just copying the entire row from the base table whenever an insert or update occurs. If you don't like the idea of adding an "updated_by" column to your base table, then you can wrap the insert inside of a stored proc and pass the userid value to the stored proc. The proc can update the base table without the userid, then update the history table with it. -- Guy Rouillier
On 8/21/07, Guy Rouillier <guyr-ml1@burntmail.com> wrote:
> Well, I can't find a way to set a variable associated with a connection,
> so probably the easiest thing to do is to add an "updated_by" column to
> your regular table (i.e., the non-history version.) Then just include
> the userid from your webapp as the value for that column. Your history
> table can then be updated by just copying the entire row from the base
> table whenever an insert or update occurs.
Yes, I think this will be the easiest way, thanks!
--
regards,
Robin