Re: Denormalization question, history+ current

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: eric soroos <eric-psql(at)soroos(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Denormalization question, history+ current
Date: 2002-04-30 00:06:56
Message-ID: 14235.1020125216@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

eric soroos <eric-psql(at)soroos(dot)net> writes:
> What I currently do from an outside app:

> insert into history_foo (column1, ...) values (bar, ...);
> update current_foo set column1=bar, ... where pkey=a;

> What I'd like to do is insert, then have a trigger/rule assemble an
> update statement for the current_foo table, only updating the columns
> that are non-null and in the current_foo table.

Is it critical (or even desirable?) for your app to control the values
being inserted into the extra columns of the history table? Or are
those columns well-defined values like timestamps?

The way I'd be inclined to do this is to have the app manipulate
current_foo directly, not touching the history table, and then let
a rule or trigger insert into the history table.

A simple rule would be along the lines of

ON INSERT TO current DO
INSERT INTO history SELECT new.*, current_timestamp, current_user;

You might get better performance with a trigger, however.

regards, tom lane

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Josh Berkus 2002-04-30 03:02:23 Re: Denormalization question, history+ current
Previous Message Sharon Cowling 2002-04-29 23:09:00 Re: Problem with Dates