From: Andreas Joseph Krogh <andreak@officenet.no>
Organization: OfficeNet AS
To: pgsql-sql@postgresql.org
Subject: Re: Problem with query on history table
Date: Thu, 2 Mar 2006 00:47:53 +0100
User-Agent: KMail/1.8.2
References: <200602270819.30702.andreak@officenet.no>
	<20060301221943.GX82012@pervasive.com>
In-Reply-To: <20060301221943.GX82012@pervasive.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200603020047.53925.andreak@officenet.no>

On Wednesday 01 March 2006 23:19, Jim C. Nasby wrote:
> Probably the easiest way is to switch to using table partitioning and
> switch to using start_timestamp and end_timestamp, so that when you
> modify a row you update the old one setting end_timestamp to now() and
> insert the new row (all within one transaction).
>
> There are other ways to do it, but they'll probably be much slower. I
> don't think they require a lot of CASE statements though.
>
> Show us what you were planning on doing and maybe I'll have more ideas.
>
> On Mon, Feb 27, 2006 at 08:19:30AM +0100, Andreas Joseph Krogh wrote:
> > Hi all!
> >
> > I don't know if there's a standard solution to the kind of problem I'm
> > trying to solve, but I will appreciate your thougts(and maybe solution:)
> > on this problem of mine:
> >
> > I have 2 tables: hist and curr which hold numbers for "history-data" and
> > "current-data" respectivly. Here is a simplified version of the schema:
> >
> > CREATE TABLE curr (
> >     id integer NOT NULL,
> >     etc integer NOT NULL,
> >     created timestamp without time zone NOT NULL,
> >     modified timestamp without time zone
> > );
> >
> > CREATE TABLE hist (
> >     id serial NOT NULL,
> >     curr_id integer NOT NULL REFERENCES curr(id),
> >     etc integer NOT NULL,
> >     modified timestamp without time zone NOT NULL
> > );
> >
> > andreak=# SELECT * from curr;
> >  id | etc |       created       |      modified
> > ----+-----+---------------------+---------------------
> >   1 |   5 | 2006-02-01 00:00:00 |
> >   2 |  10 | 2006-01-15 00:00:00 | 2006-01-26 00:00:00
> >   3 |  10 | 2006-01-08 00:00:00 | 2006-01-25 00:00:00
> > (3 rows)
> >
> > andreak=# SELECT * from hist;
>

The scenario I'm having is this:
I have some data in table "curr", and whenever that data changes, a copy of 
the "old data" for that entry is copied over to "hist" so that "hist" always 
holds all entries but the newest one with their values "one before" "curr".

I solved my problem with the following query:

SELECT curr.id,
COALESCE(
(SELECT h.etc FROM history h WHERE h.history_modified =
(SELECT MIN(h2.history_modified)
 FROM history h2 WHERE h2.history_modified >= ?
 AND h2.curr_id = curr.id)
 AND curr.created <= ? AND curr.id = h.curr_id),
 CASE WHEN curr.created > ? THEN NULL ELSE curr.etc END) AS etc
FROM curr;

I don't know how well it performes on larger data, but it work for me for the 
moment. I very much welcome some feedback on my "solution", and some 
enlightenment on what the impact of subqueries like this have on performance 
on larger data-sets.

--
AJK