history table

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