Ken Winter wrote:
Richard ~
Let me zoom out for a moment, for the bigger picture.
As you have inferred, what I'm trying to do is develop a history-preserving
table ("my_data" in the example that started this thread). *Most* user
programs would see and manipulate this table as if it contained only the
current rows (marked by effective_date_and_time <= 'now' and
expiration_date_and_time = 'infinity').
When these programs do an INSERT, I need automatic actions that set the
expiration and date timestamps to 'now' and 'infinity'; when they do an
UPDATE, I need automatic actions that save the old data in a history record
and expire it as of 'now' and the new data in a record that's effective
'now' and expires at 'infinity'; when they do a DELETE, I need an automatic
action to expire the target record as of 'now' rather than actually deleting
it.
Oh - while I think of it, be VERY VERY careful that your system clock
doesn't get put back. I've done this sort of thing and been bitten by it.
However, I also need certain maintenance programs, designed to enable
certain users to correct inaccurately entered data. These need to be able
to "rewrite history" by doing actions against "my_data" without these
automatic actions occurring. It may prove advisable to provide some
automatic actions for these programs too, but they definitely won't be the
actions described above. If the above actions were implemented as triggers,
all the ways I could think of to conditionally disable them (and possibly
replace them with other actions) seemed architecturally very klunky. That's
when I decided I needed the "my_data_now" view, and from that I inferred
(apparently correctly) that the actions would have to be implemented as
rewrite rules.
The "standard" approach in so far as there is one would be to have a
first line IF CURRENT_USER = 'MAINTENANCE' THEN RETURN ... or perhaps a
boolean stored in a system-settings table to turn them on or off in
en-masse. In your case the user-test seems better.
The cascading problem was solkable. But the solution was a bit hard to
reach because the user-invoked UPDATE action triggered both an INSERT and an
UPDATE on the same table (and user DELETE triggered an UPDATE), and so one
had to take into account that all of these triggered actions would cause
their triggers to fire again. Not a deal-killer, but the solution felt
brittle.
Yes, I did consider having a "live" table and a separate "history" table.
The killer of that idea was my inability to find a way to implement foreign
keys that could refer to both tables and that could follow a record when it
was moved from "live" to "history". Much of the history I'm trying to
preserve is not in the "my_data" table; it's in related tables that refer to
it. I presumably could do this by not declaring the FKs to PostgreSQL, and
implementing the necessary referential integrity with triggers, but - well,
in a word, yuck.
If you're going to do this with multiple tables you actually need (at
least) three. For example, if you had different versions of e.g.
documents being stored you would want:
document - invariants: the id, perhaps document-type.
FKeys link to this.
A row is only deleted from here if all live+history
is also deleted.
document_live - the one that gets edited.
1:1 relationship with document if still live
document_hist - with timestamps. N:1 with document
Have a google for Temporal Databases too - there's a lot of thinking
been done about this.
As it happens, I have found a rewrite of my UPDATE rule that works, so my
immediate need is past. FYI, the old update rule was:
[snip]
The relevant change is that I'm now expiring the record with the old data
and inserting the one with the new data, rather than vice versa. I still
don't know why the old rule didn't work and this one does, but hey,
whatever. Another advantage of the new one is that I don't have to re-point
foreign keys that were already pointed to the record containing the old
data, because that record stays in place.
(The other change, adding the lines
AND effective_date_and_time <= CURRENT_TIMESTAMP
AND expiration_date_and_time >= CURRENT_TIMESTAMP;
to the UPDATE, was necessary to keep updates to the "my_data_now" from
updating the expired rows as well.)
Make sure you test it with inserts/updates of multiple rows too.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
