Re: history table

Robin Helgelin wrote:
> On 8/21/07, Guy Rouillier <guyr-ml1(at)burntmail(dot)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