Hey all,

I try to explain to my best...
I have the situation where I log every change to a text field into a separate log table. From this I want to create an SQL query to show side by side the change from the last record to the new record, this is done with a trigger procedure. The example below more or less reflect my actual situation:

CREATE TABLE tabledata{
key TEXT,
data TEXT
PRIMARY KEY (key)
};

CREATE TAVLE tabledata_log {
changedate timestamp,
key TEXT,
data TEXT,
PRIMARY KEY (changedate, key)
}

The above data can be filled with data much like

'ries', 'Hello my name is ries'
'piet', 'Hello my name is piet'
'jan', 'hello my name is jan'

The data_log table would look like this:
2008-05-05 14:42:15.501928-05", 'ries', 'Hello my name is ries!'
2008-05-05 14:22:15.501928-05", 'ries', 'Hello my name is Ries'
2008-05-05 14:12:15.501928-05", 'ries', 'Hello my name is Ries.'

Well, you get the idea....
Now to show the changes from record to record sorted by time side by side I issue a query that looks like this:

SELECT
b.changedate AS previousdate, b.data AS previousdata, a.changedate, a.data
FROM
-- Find all records of changes including the current record
-- give me a list of total data records
(
SELECT
now() AS changedate, key, data
FROM datetable WHERE key='ries'
UNION
SELECT
changedate, key, data
FROM datetable_log WHERE key='ries'
) as a

-- Self join finding the single last changed record, join on primary key and find single last changetime
LEFT JOIN datetable_log b ON
(
a.key=b.key AND b.changedate=
-- Find the single last change time
(
SELECT c.changedate FROM datetable_log c WHERE a.key=c.key AND c.changedate < a.changedate ORDER BY changedate DESC limit 1
)
)

-- Find the appropriate record by primary key
WHERE a.key = 'ries'
ORDER BY a.changedate DESC


As you can see from the above, a lot of queries to do that 'sounds simple'.
Would you feel this can be optimized better by uzing less queries?

The above querry does work properly on my real tables, but just wondering if there is a best practise in such a situation.

Ries

When you log changes in your history table, instead of logging what was modified, just log a current copy of the record AFTER the modifications, along with a time stamp. You will log not only updates and deletes, but also inserts.
This way, your log table has a complete history of every each record, including its current values. Then, you can drop the clumsy UNION statement and do all of your historical snapshot reporting solely from the log table.
It will make the queries simpler, and faster.

Hey Blindman (I hope it's not an offense to call you like that...),

currently we don't have the requirement to log deletes and inserts to the log table, only changes.
However to also log the insert might be a good idea to get rid of the UNION.

However, is the general principle of the given SQL ok, it cannot be written smarter I assume?

Ries

Isn't your requirement to log the history?
And you should log deletes as well, but adding a "deleted" bitflag column to the log tables.

Honestly, I do think your current sql is clumsy, and I'm not convinced it is even giving you what you think you are getting. But I am reluctant to spend a lot of time looking at it when it is your data structure and requirements that are faulty. Until you fix these, you are always going to be running into complex and inefficient code.

I need to log history, but only changes , that is changes to a record, not inserts or deletes so I don't need the extra column there.

I think you are getting confused with something. I do need to compare the most recent historical change with current value and the historical values with each other culminative. Thus I must need the UNION as far as I can tell.

The idea was taken from http://www.postgresql.com.cn/docs/8....l-trigger.html (section 38-3.) but I simply don't record what operation was done (I know I record the old value in the same log table).

I will give it a second thought and see if I also want to insert the current (NEW) value in the same table. My design was based on recording the OLD value in the log table

Ries

Really? Why do you "need" to do this in the first place? Simply because a piece of paper told you to? There has to be a true business requirement behind this somewhere, which is probable something like "The system needs to be able retain historical data for auditing purposes", or "The system needs to be able to reproduce historical data as of any point in time." Either of these is better served using the archiving method I have outlined, rather than the structure you have set up. The proof is in the SQL, which is relatively simple under the schema I gave you, but is clearly raising concerns for you with your current design.

Hey,

I want to thank you for your extensive responses to by questions.
I see what you are saying and will change my data structure to follow your suggestions.

Ries

I always like to see things done right.
If you need any more help, jump back on the forum.
Good luck.