Re: Rule causes baffling error

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