Re: Rule causes baffling error - Mailing list pgsql-sql

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
thatgets 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